INDEX et MATCH avec plusieurs tableaux
Supposons que vous disposiez de plusieurs tableaux dotés des mêmes en-têtes, comme illustré ci-dessous. Rechercher une valeur correspondant à des critères donnés parmi ces tableaux peut s’avérer une tâche complexe. Dans ce tutoriel, nous vous expliquons comment retrouver une valeur dans plusieurs tableaux, plages ou groupes en faisant correspondre des critères spécifiques à l’aide des fonctions INDEX, MATCH et CHOOSE.
Comment rechercher une valeur dans plusieurs tableaux ?
Pour identifier les chefs des différents groupes appartenant à divers départements, commencez par utiliser la fonction CHOOSE afin de sélectionner le tableau contenant les noms des chefs. La fonction MATCH détermine ensuite la position du chef dans ce tableau. Enfin, la fonction INDEX récupère le nom du chef en combinant cette position avec la colonne où les noms des chefs sont répertoriés.
Syntaxe générique
=INDEX(CHOOSE())array_num,array1 ,array2 ,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num : Le numéro utilisé par la fonction CHOOSE pour désigner, dans la liste array1, array2, …, le tableau à partir duquel renvoyer le résultat.
- array1, array2, … : Les tableaux à partir desquels le résultat est renvoyé. Ici, il s’agit des trois tableaux.
- lookup_value : La valeur que la formule combinée utilise pour trouver la position de son chef correspondant. Il s’agit ici du groupe indiqué.
- lookup_array : La plage de cellules dans laquelle la lookup_value est répertoriée. Ici, il s’agit de la plage des groupes.Remarque : vous pouvez utiliser la plage de groupes de n’importe quel département, car elles sont toutes identiques — nous avons simplement besoin d’obtenir le numéro de position.
- column_num : La colonne que vous spécifiez pour extraire les données.
Pour connaître le chef du groupe D appartenant au département A, veuillez copier ou saisir la formule ci-dessous dans la cellule G5, puis appuyer sur Entréepour obtenir le résultat :
=INDEX(CHOOSE())1,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
√ Remarque : les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que les plages de noms et de classes dans la formule ne changeront pas lorsque vous déplacerez ou copierez la formule vers d’autres cellules. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour l’appliquer aux cellules situées en dessous, puis modifiez en conséquence la valeur de array_num.
Explication de la formule
=INDEX()CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20):La fonction CHOOSE renvoie le 1er tableau parmi les trois listés dans la formule. Elle renverra donc $B$5:$C$8, c’est-à-dire la plage de données du département A.
- MATCH(F5,$B$5:$B$8,0): Le match_type 0 oblige la fonction MATCH à renvoyer la position de la première occurrence de Group D, la valeur contenue dans la cellule F5, dans le tableau $B$5:$B$8, qui est 4.
- INDEX()CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2) = INDEX($B$5:$C$8,4,2La fonction INDEX extrait la valeur située à l’intersection de la 4e ligne et de la 2e colonne de la plage $B$5:$C$8, qui est Emily.
Pour éviter de modifier manuellement array_numdans la formule chaque fois que vous la copiez, vous pouvez utiliser une colonne d’aide, ici la colonne D. La formule sera alors la suivante :
=INDEX(CHOOSE())D5,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
√ Remarque : les nombres 1,2,3figurant dans la colonne d’aide correspondent respectivement à array1,array2,array3dans la fonction CHOOSE.
Fonctions associées
La fonction INDEX d’Excel renvoie la valeur affichée à une position donnée dans une plage ou un tableau.
La fonction RECHERCHE d’Excel localise une valeur précise au sein d’une plage de cellules et renvoie sa position relative.
La fonction CHOOSE renvoie une valeur issue d’une liste d’arguments en fonction d’un numéro d’index donné. Par exemple, CHOOSE(3, « Apple », « Peach », « Orange ») renvoie « Orange » : l’index est 3, et « Orange » correspond à la troisième valeur de la liste.
Formules associées
Plage de valeur de recherche à partir d’une autre feuille de calcul ou d’un autre classeur
Si vous maîtrisez la fonction VLOOKUP pour rechercher des valeurs dans une feuille de calcul, étendre cette recherche à une autre feuille ou à un autre classeur sera un jeu d’enfant.
Recherche verticale avec nom de feuille dynamique
Dans de nombreux cas, vous devrez collecter des données issues de plusieurs feuilles de calcul pour établir un résumé. En combinant les fonctions VLOOKUP et INDIRECT, vous pouvez créer une formule qui recherche des valeurs spécifiques dans différentes feuilles à l’aide d’un nom de feuille dynamique.
Recherche selon plusieurs critères avec INDEX et MATCH
Lorsque vous travaillez avec une grande base de données dans une feuille Excel comportant plusieurs colonnes et libellés de lignes, il est souvent délicat de trouver une entrée répondant à plusieurs critères. Dans ce cas, vous pouvez utiliser une formule matricielle combinant les fonctions INDEX et MATCH.
Les meilleurs outils de productivité Office
Kutools pour Excel – Vous permet de vous démarquer de la foule
Kutools pour Excel propose plus de 300 fonctionnalités,garantissant que ce dont vous avez besoin est toujours à portée de clic...
Office Tab – Active la lecture et l’édition par onglets dans Microsoft Office (y compris Excel)
- Passez d’un document à l’autre en une seconde, même avec des dizaines de documents ouverts !
- Réduisez de centaines vos clics de souris chaque jour et dites adieu au syndrome de la main de la souris.
- Augmentez votre productivité de 50 % en consultant et en modifiant simultanément plusieurs documents.
- Apporte à Office (y compris Excel) une efficacité comparable à celle des navigateurs tels que Chrome, Edge et Firefox.