Associez INDEX et EQUIV pour effectuer mieux vos recherches

Ajourd’hui vous allez voir dans cette formation Excel, comment utiliser conjointement les deux fonctions INDEX et EQUIV pour effectuer vos recherches mieux que lorsque vous utilisiez la fonction RECHERCHEV.

Comme nous l’avons vu dans l’article "Comment utiliser la fonction RECHERCHEV ?" la fonction RECHERCHEV nous permet de renvoyer la valeur qui correspond à une valeur cherchée dans un tableau.

Et si nous voulons l’appliquer ici pour rechercher le prix de l’ampoule « LED E27 », nous procédons comme suit :

Cette même valeur retournée pourra être renvoyée aussi en utilisant conjointement les deux fonctions INDEX et EQUIV.

Beaucoup d’utilisateurs d’Excel préfèrent l’utilisation de ces deux fonctions au lieu de la fonction RECHERCHEV pour de nombreuses raisons.

Tout d’abord veuillez jeter un coup d’œil sur l'utilisation de ces deux fonctions INDEX et EQUIV, puis découvrons ensemble comment faire des recherches en les associant toutes les deux.

La fonction INDEX

La fonction INDEX permet de renvoyer une valeur qui se trouve dans une cellule, d’un tableau ou d’une plage de cellules, à l ‘intersection d’une ligne et d’une colonne spécifiées.

La fonction INDEX a deux syntaxes :

INDEX(matrice, no_lig, [no_col]) et INDEX(réf, no_lig, [no_col], [no_zone]).

Je vous invite à découvrir en détails comment utiliser la fonction INDEX à partir de cet article : La fonction INDEX

La fonction EQUIV

La fonction EQUIV renvoie la position d’un élément recherché dans une plage de cellules.

Voici sa syntaxe : EQUIV(valeur_cherchée, matrice_recherche, [type])

Découvrez plus d’infos sur cette fonction en suivant ce lien : La fonction EQUIV

Regardez cette courte vidéo pour découvrir brièvement comment utiliser conjointement les deux fonctions INDEX+EQUIV :

Pourquoi INDEX + EQUIV est considérée meilleure que RECHERCHEV ?

Insérer ou supprimer des colonnes sans affecter au résultat renvoyé :

Le problème de la fonction RECHERCHEV qui se pose lors de l’insertion ou de la suppression d’une ou de plusieurs colonnes c’est qu’elle retourne des valeurs incorrectes.

Prenez l’exemple suivant:

En utilisant la fonction RECHERCHEV pour trouver le prix de l’ampoule « LED E27 » ; nous avons déterminé pour le numéro d’index de colonne la valeur 3.

Or, si nous insérons une colonne par exemple avant la deuxième colonne « Puiss en Watt », cette dernière et la colonne Prix seront décalées vers la droite, et la colonne « Puiss en Watt » deviendra la troisième colonne de la table_matrice. 

RECHERCHEV renvoie donc 4 au lieu de 8.

Nous serons alors obligés de modifier notre formule pour régler le problème.

Mais imaginez combien de temps et d’efforts vous allez fournir si vous êtes devant un grand tableau de plusieurs colonnes et de milliers de données !!

Avec INDEX+EQUIV vous pouvez insérer ou supprimer des colonnes sans affecter à vos résultats et sans avoir besoin d’intervenir pour modifier la formule.

En reprenant l’exemple précédent, voici comment va dérouler le travail des deux fonctions INDEX et EQUIV associées :

• La valeur (le prix) qui correspond à la valeur cherchée « LED E27 » se trouve à l’intersection de la ligne n°6 et de la colonne n°3 de la plage de cellules A2:C7.
• Si nous utilisons la fonction INDEX, nous aurons l’écriture suivante =INDEX(A2:C7;6;3)

Et pour rendre le jeu dynamique, nous allons utiliser la fonction EQUIV pour renvoyer le numéro de la ligne et de la colonne utilisées dans la fonction INDEX.

Voici ce que nous allons faire :

Renvoyer le numéro de la ligne :

• La valeur 8 correspond à la valeur cherchée « LED E27 » et toutes les deux se trouvent dans la même ligne : Ligne n°6
• Alors la formule à utiliser pour renvoyer ce chiffre est la suivante : =EQUIV(F1;A2:A7;0)

Renvoyer le numéro de la colonne :

• La colonne contenant la valeur 8 est la colonne « Prix », nous utiliserons donc la fonction EQUIV de la façon suivante pour nous renvoyer le numéro de cette colonne : =EQUIV(C1;A1:C1;0) 

• Intégrant maintenant EQUIV dans INDEX :

Insérez ou supprimez des colonnes et remarquez ce qui surviendra:

Copiez vos formules sans problèmes

Souvenez-vous de cet exemple vu dans l’article Comment utiliser la fonction RECHECHEV ?, où le copier-coller de la fonction RECHECHEV impose qu’on doit modifier « manuellement » le no_col_index qui est déterminé comme valeur figée?!

En utilisant INDEX+EQUIV, vous n’aurez pas ce genre de difficulté.

Voyons donc comment nous allons procéder :

• La cellule G3 va afficher le montant réalisé par le vendeur spécifié dans la cellule G1, dans notre exemple c’est "Nom 1".
• La colonne affichant les montants réalisés est placée en 4^ème position dans la matrice A2:D22 qu’on a nommée « Table1 ».

• Et pour utiliser la fonction EQUIV qui va renvoyer cette position, nous écrivons :

=EQUIV($F3;$A$1:$D$1;0)

• F3 contient le nom de l’intitulé de la colonne « Mt réalisé » tel qu’il est écrit dans le tableau. Et nous avons figé la colonne F. (vous allez voir pourquoi !)
• $A$1:$D$1 : référence absolue de la plage des intitulés des colonnes du tableau.
• Quant à la valeur « Nom 1 », elle se trouve dans la 1^ère ligne.
• Alors la fonction EQUIV s’écrira comme suit pour retourner la position de cette ligne :

=EQUIV($G$1;$A$2:$A$22;0)

Nous allons maintenant intégrer les deux formules dans la fonction INDEX, et voici ce que nous obtiendrons :

=INDEX(Table1; EQUIV($G$1;$A$2:$A$22;0); EQUIV($F3;$A$1:$D$1;0))

Excel renvoie alors le montant 1156,00 Euros.

En copiant cette formule sur la cellule G4, Excel affiche « Paris » et c’est le bon résultat souhaité.

Vous remarquez donc que toutes les références restent inchangées dans les deux formules, sauf F3 qui est incrémentée après la copie et est devenue F4 pour s’adapter à ce que nous recherchons, c’est-à-dire le nom de la ville.

De droite à gauche

En utilisant la fonction RECHERCHEV, la valeur cherchée doit se trouver dans la colonne la plus à gauche de la table_matrice (plage de recherche).

Avec INDEX+EQUIV cette condition n’est pas obligatoire.

Voici le tableau utilisé dans l’exemple précédent, où nous avons déplacé la colonne "Nom" en deuxième position dans la plage de cellules A2:D22 (Table1).

Et voici la formule utilisée :

=INDEX(Table1; EQUIV($G$1;$B$2:$B$22;0); EQUIV($F3;$A$1:$D$1;0))

Excel renvoie donc le bon résultat.

INDEX+EQUIV avec plusieurs critères

Exemple 1

Le tableau suivant contient des notes de quelques étudiants selon les matières étudiées :

Nous voulons trouver la note d’une matière spécifiée pour un étudiant sélectionné en utilisant les deux fonctions INDEX et EQUIV :

• Supposons que la cellule H1 affiche le nom de l’étudiant cherché, et la cellule H2 contient le nom de la matière dont on veut trouver la note.
• Nous avons donc créé une liste de choix dans H1 pour afficher tous les noms des étudiants, et une autre liste dans H2 pour afficher toutes les matières.
• Pour ce faire, sélectionnez la cellule H1 et cliquez sur Validation des données sous l’onglet Données.
• Sélectionnez Liste sous Autorise, puis cliquez dans la zone Source et sélectionnez les noms des étudiants, et cliquez sur OK pour valider.

• Procédez ainsi pour créer la liste des matières.
• Sélectionnez H4 et tapez la formule suivante :

=INDEX($B$2:$E$6;EQUIV($H$2;$A$2:$A$6;0);EQUIV($H$1;$B$1:$E$1;0))

• Validez par Entrée.
• Voici le résultat obtenu :

Exemple 2

Le tableau suivant liste plusieurs types d’ampoules avec leurs puissances en Watt et prix:

Prix des ampoules

Ce que nous souhaitons dans cet exemple c’est chercher le prix d’une ampoule selon sa puissance en Watt:

Par exemple, nous allons procéder en utilisant conjointement INDEX et EQUIV pour trouver le prix de l’ampoule "Halogène (230V) 1000W".

• L’ampoule "Halogène (230V) 1000W" se trouve donc dans la ligne n°4.
• Et son prix correspondant se trouve dans la colonne Prix.

• En utilisant la fonction INDEX, nous écrivons : =INDEX(C2:C15;4)

• Vous remarquez que nous avons sélectionné uniquement la colonne Prix, c’est pour cela que n’avons pas déterminé le numéro de colonne.
• Intégrant maintenant EQUIV :
• La formule s’écrira de cette façon :

=INDEX(C2:C15;EQUIV(F1&F2;A2:A15&B2:B15;0))

• Validez ensuite par Ctrl+Shift+Entrée pour obtenir le résultat escompté :

Pour renvoyer donc le numéro de la ligne 4, Nous avons effectué la recherche des deux valeurs Halogène (230V) et 1000W concaténées dans les deux plages de cellules concaténées également A2:A15&B2:B15.

C’est-à-dire que la fonction EQUIV dans ce cas va chercher la valeur résultante de la concaténation F1&F2 qui est « Halogène (230V)1000 » dans le nouveau tableau obtenu de la concaténation des deux plages A2:A15&B2:B15 et qui sera affiché de cette façon:

Bien entendu, la valeur « Halogène (230V)1000 » se trouve en 4^ème ligne.

Voyons à présent comment utiliser la même formule mais en évitant de la rendre matricielle. ( Vous ne serez pas obligés de combiner Ctrl+Shift+Entrée pour la valider)

Copiez la formule suivante dans la cellule F4 :

=INDEX(C2:C15;EQUIV(F1&F2;INDEX(A2:A15&B2:B15;0);0))

Validez alors par Entrée:

À la place de A2:A15&B2:B15 nous avons intégré une autre fonction INDEX pour nous renvoyer toutes les valeurs de ces deux plages concaténées en spécifiant 0 pour l’argument no_lig. (reportez vous à l’article La fonction INDEX pour plus de détails sur cette astuce).

PARTAGEZ PARTAGEZ PARTAGEZ