Ce didacticiel vous apprendra à utiliser la combinaison INDEX & MATCH pour effectuer des recherches dans Excel et Google Sheets.
INDEX & MATCH, la paire parfaite
Examinons de plus près certaines des façons dont vous pouvez combiner les fonctions INDEX et MATCH. La fonction MATCH 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 en fonction d'une position spécifique. Cette synergie entre les deux leur permet d'effectuer presque tous les types de recherche dont vous pourriez avoir besoin.
La combinaison INDEX / MATCH a historiquement été utilisée en remplacement de la fonction RECHERCHEV. L'une des principales raisons étant la possibilité d'effectuer une recherche vers la gauche (voir la section suivante).
Remarque : la nouvelle fonction XLOOKUP peut désormais effectuer des recherches à gauche.
Recherche à gauche
Utilisons ce tableau de statistiques de basket-ball :
Nous voulons trouver le numéro de joueur de Bob. Étant donné que le numéro de joueur se trouve à gauche de la colonne du nom, nous ne pouvons pas utiliser de RECHERCHEV.
Au lieu de cela, nous pourrions faire une requête MATCH de base pour calculer la ligne de Bob
= CORRESPONDANCE(H2, B2:B5, 0)
Cela cherchera une correspondance exacte du mot "Bob", et donc notre fonction renverrait le nombre 2, puisque "Bob" est dans le 2sd position.
Ensuite, nous pouvons utiliser la fonction INDEX pour retourner le Player #, correspondant à une ligne. Pour l'instant, saisissons simplement « 2 » manuellement dans la fonction :
=INDICE(A2:A5, 2)
Ici, INDEX fera référence à A3, puisque c'est le 2sd cellule dans la plage A2:A5 et renvoyer le résultat de 42. Pour notre objectif global, nous pouvons ensuite combiner ces deux en :
=INDICE(A2:A5, CORRESPONDANCE(H2, B2:B5, 0))
L'avantage ici est que nous avons pu renvoyer un résultat d'une colonne à gauche de l'endroit où nous cherchions.
Recherche en deux dimensions
Regardons notre table d'avant :
Cette fois cependant, nous voulons récupérer une statistique spécifique. Nous avons annoncé que nous voulions rechercher des rebonds dans la cellule H1. Plutôt que d'avoir à écrire plusieurs instructions IF pour déterminer de quelle colonne obtenir le résultat, vous pouvez à nouveau utiliser une fonction MATCH. 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 MATCH ici pour déterminer quelle colonne nous voulons. Cela ressemblera
= CORRESPONDANCE(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 MATCH détermine à quelle colonne du tableau appartient. Branchons ce nouveau bit 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 simplement un résultat de la colonne A.
=INDICE(A2:E5, CORRESPONDANCE(H2, B2:B5, 0), CORRESPONDANCE(H1, A1:E1, 0))
Dans notre exemple, nous voulons trouver des rebonds pour Charlie. Notre formule va évaluer ceci comme ceci :
=INDICE(A2:E5, MATCH("Charlie", B2:B5, 0), MATCH("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 plusieurs formules ou à brancher des instructions IF.
Plusieurs sections
Ce n'est pas souvent utilisé, mais INDEX a un cinquième argument qui peut être donné pour déterminer quel Région dans l'argument un à utiliser. Cela signifie que nous avons besoin d'un moyen de passer plusieurs zones dans le premier argument. Vous pouvez le faire en utilisant un jeu supplémentaire de parenthèses. Cet exemple illustrera comment vous pouvez récupérer les résultats de différentes tables sur une feuille de calcul à l'aide d'INDEX.
Voici la mise en page que nous utiliserons. Nous avons des statistiques pour trois quarts de jeu différents.
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 vient de J2:J4. Nous l'utiliserons pour une autre instruction MATCH, afin de déterminer la zone à utiliser. Notre formule dans H4 va ressembler à ceci :
=INDICE((A3:E6, A10:E13, A17:E20), CORRESPONDANCE(H2, B3:B6, 0), CORRESPONDANCE(H1, A2:E2, 0), CORRESPONDANCE(H3, J2:J4, 0))
Nous avons déjà discuté du fonctionnement des deux fonctions MATCH internes, alors concentrons-nous sur le premier et le dernier argument :
=INDICE((A3:E6, A10:E13, A17:E20),… , CORRESPONDANCE(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 consiste à utiliser Formules - Définir le nom. Vous pouvez définir un nom appelé "MyTables" avec une définition de
=INDEX(MaTable,MATCH(H2,Table1347[Nom],0),MATCH(H1,Table1347[#En-têtes],0),MATCH(H3,J2:J4,0))
Revenons à l'ensemble de l'énoncé. Nos différentes fonctions MATCH vont indiquer à la fonction INDEX exactement où chercher. Tout d'abord, nous déterminerons que "Charlie" est le 3rd ligne. Ensuite, nous voulons des « rebonds », qui est le 4e colonne. Enfin, nous avons déterminé que nous voulons le résultat de 2sd table. La formule évaluera à travers ceci comme ceci:
=INDICE((A3:E6, A10:E13, A17:E20), CORRESPONDANCE(H2, B3:B6, 0), CORRESPONDANCE(H1, A2:E2, 0), CORRESPONDANCE(H3, J2:J4, 0)) =INDICE((A3:E6, A10:E13, A17:E20), 3, 4, 2) =INDICE(A10:E13, 3, 4) =D13 =14
Comme nous l'avons mentionné au début de cet exemple, vous êtes limité à ce que les tables soient sur la même feuille de calcul. Si vous pouvez écrire des manières correctes d'indiquer à votre INDEX de quelle ligne, colonne et/ou zone vous souhaitez récupérer des données, INDEX vous sera très utile.
Feuilles Google -INDEX & CORRESPONDANCE
Tous les exemples ci-dessus fonctionnent exactement de la même manière dans Google Sheets que dans Excel.
