Maîtriser INDEX et EQUIV sur Excel : Le duo gagnant pour vos recherches !

Comprendre INDEX et EQUIV : Les bases

Avant de plonger dans la combinaison de ces deux fonctions, il est essentiel de comprendre leur rôle individuel. INDEX renvoie une valeur à partir d'une plage de cellules, en fonction d'un numéro de ligne et/ou de colonne. EQUIV (MATCH), quant à elle, recherche une valeur spécifique dans une plage et renvoie la position relative de cette valeur.

La fonction INDEX : Récupérer une valeur par sa position

La fonction INDEX a deux syntaxes principales:

• INDEX(matrice, no_ligne, [no_colonne]) : Renvoie la valeur à l'intersection de la ligne et de la colonne spécifiées dans la matrice.
• INDEX(référence, no_ligne, [no_colonne], [no_zone]) : Renvoie une référence à une cellule ou une plage de cellules à partir d'une liste de références.

Dans le contexte de cet article, nous nous concentrerons sur la première syntaxe, la plus couramment utilisée.

Exemple :

Supposons que vous ayez une plage de cellules A1:C5 contenant des données. Pour récupérer la valeur située à la 3ème ligne et 2ème colonne, vous utiliserez la formule : =INDEX(A1:C5, 3, 2).

La fonction EQUIV (MATCH) : Trouver la position d'une valeur

La fonction EQUIV (MATCH) recherche une valeur spécifique dans une plage et renvoie sa position relative. Sa syntaxe est la suivante :

EQUIV(valeur_recherchée, plage_recherche, [type])

• valeur_recherchée : La valeur que vous souhaitez trouver.
• plage_recherche : La plage de cellules dans laquelle vous effectuez la recherche.
• [type] : Un argument optionnel qui spécifie le type de correspondance. Les valeurs possibles sont 1, 0 ou -1. Généralement, on utilise 0 pour une correspondance exacte.

Exemple :

Si vous avez une liste de noms en A1:A10 et que vous voulez trouver la position du nom "Marie", vous utiliserez la formule : =EQUIV("Marie", A1:A10, 0). Si "Marie" est le 5ème nom de la liste, la formule renverra 5.

Combiner INDEX et EQUIV : La puissance décuplée

La véritable puissance de ces fonctions réside dans leur combinaison. En utilisant EQUIV pour déterminer le numéro de ligne ou de colonne à utiliser dans la fonction INDEX, vous créez une recherche dynamique et flexible.

Remplacer RECHERCHEV avec INDEX et EQUIV

La fonction RECHERCHEV est souvent utilisée pour rechercher une valeur dans une colonne et renvoyer une valeur correspondante dans une autre colonne. Cependant, elle a des limitations : elle ne peut rechercher que dans la première colonne de la plage, et elle ne fonctionne pas si la colonne contenant la valeur à renvoyer se trouve à gauche de la colonne de recherche. INDEX et EQUIV surmontent ces limitations.

Exemple :

Imaginez un tableau avec les colonnes "Nom", "Prénom", et "Âge" en colonnes A, B et C respectivement. Vous voulez trouver l'âge d'une personne en connaissant son nom.

1. Utilisation de RECHERCHEV (limitée) : Si "Nom" est en colonne A et "Âge" est en colonne C, RECHERCHEV pourrait fonctionner. Mais si "Âge" était en colonne A et "Nom" en colonne C, ce serait impossible.
2. Utilisation d'INDEX et EQUIV (flexible) :
   • =EQUIV(valeur_a_rechercher, plage_des_noms, 0) : Cette partie trouve la position du nom recherché dans la plage de noms.
   • =INDEX(plage_des_ages, resultat_de_EQUIV) : Cette partie utilise la position trouvée par EQUIV pour renvoyer l'âge correspondant.

La formule complète serait : =INDEX(C1:C10, EQUIV("NomRecherché", A1:A10, 0)). Ici, C1:C10 est la plage contenant les âges, et A1:A10 est la plage contenant les noms. "NomRecherché" est le nom dont vous voulez connaître l'âge.

Avantages d'utiliser INDEX et EQUIV :

• Flexibilité : Vous pouvez rechercher dans n'importe quelle colonne et renvoyer une valeur de n'importe quelle autre colonne, quelle que soit leur position relative.
• Moins de sensibilité aux insertions/suppressions de colonnes : Si vous insérez ou supprimez une colonne, la formule RECHERCHEV peut se casser. INDEX et EQUIV sont moins sensibles à ces changements, car ils se basent sur des plages spécifiques.

Recherche bidirectionnelle avec INDEX et EQUIV

INDEX et EQUIV peuvent également être utilisés pour effectuer une recherche bidirectionnelle, c'est-à-dire trouver une valeur à l'intersection d'une ligne et d'une colonne spécifiées.

Exemple :

Imaginez un tableau où les lignes représentent des produits et les colonnes représentent des mois. Chaque cellule contient le chiffre d'affaires pour un produit donné pendant un mois donné. Vous voulez trouver le chiffre d'affaires pour un produit spécifique pendant un mois spécifique.

La formule serait : =INDEX(plage_de_données, EQUIV(produit_recherché, plage_des_produits, 0), EQUIV(mois_recherché, plage_des_mois, 0)).

• plage_de_données : La plage de cellules contenant les chiffres d'affaires.
• produit_recherché : Le nom du produit dont vous voulez connaître le chiffre d'affaires.
• plage_des_produits : La plage de cellules contenant les noms des produits (en ligne).
• mois_recherché : Le nom du mois dont vous voulez connaître le chiffre d'affaires.
• plage_des_mois : La plage de cellules contenant les noms des mois (en colonne).

Astuces et bonnes pratiques

• Utiliser des noms de plages : Au lieu d'utiliser des références de cellules directement dans vos formules, définissez des noms pour vos plages de données. Cela rend vos formules plus lisibles et plus faciles à maintenir. Par exemple, vous pouvez nommer la plage A1:A10 "Noms" et la plage B1:B10 "Ages". Votre formule deviendrait alors : =INDEX(Ages, EQUIV("NomRecherché", Noms, 0)).
• Gérer les erreurs : Si la valeur recherchée n'est pas trouvée, la fonction EQUIV renverra une erreur #N/A. Vous pouvez utiliser la fonction SIERREUR pour gérer ces erreurs et afficher un message plus convivial. Par exemple : =SIERREUR(INDEX(C1:C10, EQUIV("NomRecherché", A1:A10, 0)), "Nom non trouvé").
• Optimiser les performances : Pour les grandes feuilles de calcul, l'utilisation excessive de fonctions EQUIV peut ralentir le calcul. Dans ce cas, envisagez d'utiliser des tableaux croisés dynamiques ou des Power Query pour effectuer vos analyses.
• Comprendre les différents types de correspondance dans EQUIV : Bien que le type 0 (correspondance exacte) soit le plus couramment utilisé, les types 1 (inférieur ou égal à) et -1 (supérieur ou égal à) peuvent être utiles dans certaines situations. Assurez-vous de bien comprendre leur fonctionnement avant de les utiliser.

Alternatives à INDEX et EQUIV

Bien qu'INDEX et EQUIV soient un duo puissant, il existe d'autres fonctions Excel qui peuvent être utilisées pour effectuer des recherches.

• RECHERCHEV : Comme mentionné précédemment, RECHERCHEV est une alternative plus simple pour les recherches verticales, mais elle est moins flexible.
• RECHERCHEH : RECHERCHEH est similaire à RECHERCHEV, mais elle effectue des recherches horizontales (dans la première ligne d'une plage).
• XLOOKUP (RECHERCHEX): Disponible dans les versions récentes d'Excel, XLOOKUP est une fonction de recherche plus moderne et plus flexible que RECHERCHEV et RECHERCHEH. Elle combine les fonctionnalités d'INDEX et EQUIV et offre des options de correspondance plus avancées. C'est souvent l'option la plus simple et la plus performante.

Conclusion

La combinaison des fonctions INDEX et EQUIV (MATCH) est un outil puissant et polyvalent pour effectuer des recherches dans Excel. Elle offre une flexibilité et une précision supérieures à la fonction RECHERCHEV, et elle peut être utilisée pour effectuer des recherches bidirectionnelles. En maîtrisant ces deux fonctions, vous serez en mesure d'analyser et d'exploiter vos données de manière plus efficace. N'hésitez pas à expérimenter avec les exemples présentés dans cet article et à adapter les formules à vos propres besoins. Et si vous avez accès à XLOOKUP, considérez cette fonction comme une alternative plus simple et plus performante.