INDEX EQUIV – Excel et Google Sheets

Ce tutoriel vous apprendra à utiliser la combinaison INDEX & EQUIV pour effectuer des recherches dans Excel et Google Sheets.

INDEX & EQUIV, la paire parfaite

Examinons de plus près certaines des façons dont vous pouvez combiner les fonctions INDEX et EQUIV. La fonction EQUIV est conçue pour renvoyer la position relative d’un élément dans un tableau, tandis que la fonction INDEX peut extraire un élément d’un tableau à partir d’une position spécifique. Cette synergie entre les deux fonctions leur permet d’effectuer presque tous les types de recherche dont vous pouvez avoir besoin.

La combinaison INDEX / EQUIV a historiquement été utilisée pour remplacer la fonction RECHERCHEV. L’une des principales raisons en est la possibilité d’effectuer une consultation par la gauche (voir la section suivante).

Remarque : la nouvelle fonction RECHERCHEX peut désormais effectuer des recherches vers la gauche.

Consultation Vers la Gauche

Utilisons ce tableau de statistiques de basket-ball :

Nous voulons trouver le numéro de joueur de Bob. Comme le numéro de joueur se trouve à gauche de la colonne des noms, nous ne pouvons pas utiliser une RECHERCHEV.

Alternativement, nous pourrions faire une requête EQUIV de base pour trouver le numéro de ligne de Bob dans le tableau de recherche.

EQUIV(H2;B2:B5;0)

Cette requête recherchera une correspondance exacte du mot « Bob », et notre fonction renverra donc le nombre 2, puisque « Bob » est en^2ème position.

Ensuite, nous pouvons utiliser la fonction INDEX pour renvoyer le numéro de joueur, correspondant à une ligne. Pour l’instant, entrons manuellement « 2 » dans la fonction :

=INDEX(A2:A5; 2)

Ici, INDEX fera référence à A3, puisqu’il s’agit de la^deuxième cellule de la plage A2:A5, et renverra le résultat 42. Pour notre objectif global, nous pouvons alors combiner ces deux éléments en :

=INDEX(A2:A5;EQUIV(H2;B2:B5;0))

L’avantage ici est que nous avons pu renvoyer un résultat provenant d’une colonne située à gauche de celle où nous cherchions.

Recherche à Deux Dimensions

Reprenons notre tableau précédent :

Cette fois, cependant, nous voulons récupérer une statistique spécifique. Nous avons indiqué que nous voulions rechercher les rebonds dans la cellule H1. Plutôt que de devoir écrire plusieurs instructions IF pour déterminer dans quelle colonne obtenir le résultat, vous pouvez à nouveau utiliser une fonction EQUIV. La fonction INDEX vous permet de spécifier la valeur de la ligne et la valeur de la colonne. Nous allons ajouter une autre fonction EQUIV ici pour déterminer la colonne que nous voulons. Cela ressemblera à

=EQUIV(H1; A1:E1; 0)

Notre cellule dans H1 est une liste déroulante qui nous permet de choisir la catégorie que nous voulons rechercher, puis notre fonction MATCH détermine à quelle colonne du tableau elle appartient. Insérons cette nouvelle partie dans notre formule précédente. Notez que nous devons modifier le premier argument pour qu’il soit à deux dimensions, car nous ne voulons plus seulement un résultat provenant de la colonne A.

=INDEX(A2:E5; EQUIV(H2; B2:B5; 0); EQUIV(H1; A1:E1; 0))

Dans notre exemple, nous voulons trouver les rebonds pour Charlie. Notre formule va l’évaluer comme suit :

=INDEX(A2:E5; EQUIV("Charlie"; B2:B5, 0); EQUIV("Rebonds"; A1:E1; 0))
=INDEX(A2:E5; 3; 4)
=D4
=6

Nous avons maintenant créé une configuration flexible qui permet à l’utilisateur d’extraire n’importe quelle valeur de notre table sans avoir à écrire de multiples formules ou instructions IF imbriquées.

Sections Multiples

Il n’est pas souvent utilisé, mais INDEX possède un cinquième argument qui peut être donné pour déterminer quelle zone de l’argument 1 utiliser. Cela signifie que nous devons trouver un moyen de passer plusieurs zones dans le premier argument. Vous pouvez le faire en utilisant une série supplémentaire de parenthèses. Cet exemple illustre la manière dont vous pouvez extraire des résultats de différentes tables d’une feuille de calcul à l’aide de la fonction INDEX. Voici la mise en page que nous allons utiliser.

Nous avons ici des statistiques pour trois quarts différents d’une saison sportive.

Dans les cellules H1:H3, nous avons créé des listes déroulantes de validation des données pour nos différents choix. La liste déroulante pour le trimestre provient de J2:J4. Nous allons l’utiliser pour une autre instruction EQUIV, afin de déterminer la zone à utiliser. Notre formule dans H4 va ressembler à ceci :

=INDEX(MyTable; EQUIV(H2; Table134[Nom]; 0); EQUIV(H1; Table134[#En-têtes]; 0); EQUIV(H3; J2:J4; 0))

Nous avons déjà vu comment les deux fonctions EQUIV internes fonctionnent, alors concentrons-nous sur le premier et le dernier argument :

=INDEX((A3:E6; A10:E13; A17:E20); ...; EQUIV(H3; J2:J4; 0))

Nous avons donné à la fonction INDEX plusieurs tableaux dans le premier argument en les mettant tous entre parenthèses. L’autre façon de procéder est d’utiliser Formules – Définir le nom. Vous pourriez définir un nom appelé « MyTables » avec une définition de :

= 'Sections Multiples'!$A$3:$E$6 ; 'Sections Multiples'!$A$10:$E$13 ; 'Sections Multiples'!$A$17:$E$20

De cette façon, nous obtenons la formule suivante :

=INDEX(MyTable;EQUIV(H2;Table134[Nom];0);EQUIV(H1;Table134[#En-têtes];0);EQUIV(H3;J2:J4;0))

Reprenons l’ensemble de la déclaration. Nos différentes fonctions EQUIV vont indiquer à la fonction INDEX où chercher exactement. Tout d’abord, nous allons déterminer que « Charlie » est la^3ème ligne. Ensuite, nous voulons « Rebonds », qui est la^4e colonne. Enfin, nous avons déterminé que nous voulons le résultat du^2ème tableau. La formule sera évaluée de la manière suivante

=INDEX((A3:E6; A10:E13; A17:E20); EQUIV(H2; B3:B6; 0); EQUIV(H1; A2:E2; 0); EQUIV(H3; J2:J4; 0))
=INDEX((A3:E6; A10:E13; A17:E20); 3; 4; 2)
=INDEX(A10:E13; 3; 4)
=D13
=14

Comme nous l’avons mentionné au début de cet exemple, vous êtes limité au fait que les tableaux doivent se trouver sur la même feuille de calcul. Si vous pouvez rédiger des méthodes correctes pour indiquer à votre INDEX la ligne, la colonne et/ou la zone dont vous souhaitez extraire les données, l’INDEX vous sera très utile.

Google Sheets – INDEX et EQUIV

Tous les exemples ci-dessus fonctionnent exactement de la même manière dans Google Sheets et dans Excel.