Combo de fonctions INDEX et MATCH dans Excel (10 exemples faciles)

Excel a beaucoup de fonctions - environ 450+ d'entre elles.

Et beaucoup d'entre eux sont tout simplement géniaux. La quantité de travail que vous pouvez faire avec quelques formules me surprend toujours (même après avoir utilisé Excel pendant plus de 10 ans).

Et parmi toutes ces fonctions étonnantes, le combo de fonctions INDEX MATCH se démarque.

Je suis un grand fan du combo INDEX MATCH et je l'ai dit assez clairement à plusieurs reprises.

J'ai même écrit un article sur Index Match Vs VLOOKUP qui a suscité un peu de débat (vous pouvez consulter la section commentaires pour un feu d'artifice).

Et aujourd'hui, j'écris cet article uniquement axé sur Index Match pour vous montrer quelques scénarios simples et avancés dans lesquels vous pouvez utiliser ce puissant combo de formules et faire le travail.

Noter: Il existe d'autres formules de recherche dans Excel - telles que RECHERCHEV et RECHERCHEH et celles-ci sont excellentes. Beaucoup de gens trouvent que RECHERCHEV est plus facile à utiliser (et c'est aussi vrai). Je pense que INDEX MATCH est une meilleure option dans de nombreux cas. Mais comme les gens trouvent cela difficile, on s'y habitue moins. J'essaie donc de le simplifier en utilisant ce tutoriel.

Maintenant, avant de vous montrer comment la combinaison d'INDEX MATCH change le monde des analystes et des data scientists, permettez-moi d'abord de vous présenter les différentes parties - les fonctions INDEX et MATCH.

Fonction INDEX : recherche la valeur en fonction des coordonnées

La façon la plus simple de comprendre comment fonctionne la fonction Index est de la considérer comme un satellite GPS.

Dès que vous indiquerez au satellite les coordonnées de latitude et de longitude, il saura exactement où aller et trouver cet emplacement.

Ainsi, malgré un nombre ahurissant de combinaisons lat-long, le satellite saurait exactement où chercher.

J'ai rapidement fait une recherche pour mon lieu de travail et c'est ce que j'ai obtenu.

Bref, assez de géographie.

Tout comme un satellite a besoin de coordonnées de latitude et de longitude, la fonction INDEX dans Excel aurait besoin du numéro de ligne et de colonne pour savoir à quelle cellule vous faites référence.

Et c'est la fonction Excel INDEX en un mot.

Alors laissez-moi le définir avec des mots simples pour vous.

La fonction INDEX utilisera le numéro de ligne et le numéro de colonne pour trouver une cellule dans la plage donnée et retourner la valeur qu'elle contient.

En soi, INDEX est une fonction très simple, sans utilité. Après tout, dans la plupart des cas, il est peu probable que vous connaissiez les numéros de ligne et de colonne.

Mais…

Le fait que vous puissiez l'utiliser avec d'autres fonctions (indice : MATCH) qui peuvent trouver le numéro de ligne et le numéro de colonne fait d'INDEX une fonction Excel extrêmement puissante.

Voici la syntaxe de la fonction INDEX :

=INDEX (array, row_num, [col_num]) =INDEX (array, row_num, [col_num], [area_num])

• déployer - une plage de cellules ou une constante matricielle.
• num_ligne - le numéro de ligne à partir duquel la valeur doit être récupérée.
• [num_col] - le numéro de la colonne à partir de laquelle la valeur doit être récupérée. Bien qu'il s'agisse d'un argument facultatif, mais si row_num n'est pas fourni, il doit l'être.
• [zone_num] - (Facultatif) Si l'argument du tableau est composé de plusieurs plages, ce nombre serait utilisé pour sélectionner la référence parmi toutes les plages.

La fonction INDEX a 2 syntaxes (juste pour info).

Le premier est utilisé dans la plupart des cas. Le second n'est utilisé que dans les cas avancés (comme une recherche à trois voies) que nous aborderons dans l'un des exemples plus loin dans ce didacticiel.

Mais si vous êtes nouveau dans cette fonction, n'oubliez pas la première syntaxe.

Ci-dessous une vidéo qui explique comment utiliser la fonction INDEX

Fonction MATCH : recherche la position en fonction d'une valeur de recherche

Pour revenir à mon exemple précédent de longitude et de latitude, MATCH est la fonction qui peut trouver ces positions (dans le monde des feuilles de calcul Excel).

En langage simple, la fonction Excel MATCH peut trouver la position d'une cellule dans une plage.

Et sur quelle base trouverait-il la position d'une cellule ?

Basé sur la valeur de recherche.

Par exemple, si vous avez une liste comme indiqué ci-dessous et que vous souhaitez y trouver la position du nom « Mark », vous pouvez utiliser la fonction MATCH.

La fonction renvoie 3, car c'est la position de la cellule avec le nom Mark dedans.

La fonction MATCH commence à rechercher de haut en bas la valeur de recherche (qui est « Mark ») dans la plage spécifiée (qui est A1:A9 dans cet exemple). Dès qu'il trouve le nom, il renvoie la position dans cette plage spécifique.

Vous trouverez ci-dessous la syntaxe de la fonction MATCH dans Excel.

= MATCH(lookup_value, lookup_array, [match_type])

• valeur_recherche - La valeur pour laquelle vous recherchez une correspondance dans lookup_array.
• tableau_recherche - La plage de cellules dans laquelle vous recherchez la valeur lookup_value.
• [Type de match] - (Facultatif) Cela spécifie comment Excel doit rechercher une valeur correspondante. Il peut prendre trois valeurs -1, 0 ou 1.

Comprendre l'argument de type de correspondance dans la fonction MATCH

Il y a une chose supplémentaire que vous devez savoir sur la fonction MATCH, et c'est comment elle parcourt les données et trouve la position de la cellule.

Le troisième argument de la fonction MATCH peut être 0, 1 ou -1.

Vous trouverez ci-dessous une explication du fonctionnement de ces arguments :

• 0 - cela recherchera une correspondance exacte de la valeur. Si une correspondance exacte est trouvée, la fonction MATCH renvoie la position de la cellule. Sinon, il renverra une erreur.
• 1 - ceci trouve la plus grande valeur qui est inférieure ou égale à la valeur de recherche. Pour que cela fonctionne, votre plage de données doit être triée par ordre croissant.
• -1 - recherche la plus petite valeur supérieure ou égale à la valeur de recherche. Pour que cela fonctionne, votre plage de données doit être triée par ordre décroissant.

Vous trouverez ci-dessous une vidéo qui explique comment utiliser la fonction MATCH (avec l'argument de type de correspondance)

Pour résumer et dire simplement :

• INDEX a besoin de la position de la cellule (numéro de ligne et de colonne) et donne la valeur de la cellule.
• MATCH trouve la position en utilisant une valeur de recherche.

Combinons-les pour créer une centrale électrique (INDEX + MATCH)

Maintenant que vous avez une compréhension de base du fonctionnement individuel des fonctions INDEX et MATCH, combinons ces deux et découvrons toutes les choses merveilleuses qu'elles peuvent faire.

Pour mieux comprendre cela, j'ai quelques exemples qui utilisent la combinaison INDEX MATCH.

Je vais commencer par un exemple simple, puis vous montrer quelques cas d'utilisation avancés.

Cliquez ici pour télécharger le fichier exemple

Exemple 1 : Une recherche simple utilisant INDEX MATCH Combo

Faisons une simple recherche avec INDEX/MATCH.

Ci-dessous se trouve un tableau où j'ai les notes pour dix élèves.

A partir de ce tableau, je veux trouver les marques de Jim.

Vous trouverez ci-dessous la formule qui permet de le faire facilement :

=INDICE($A$2:$B$11,MATCH("Jim",$A$2:$A$11,0),2)

Maintenant, si vous pensez que cela peut être facilement fait en utilisant une fonction RECHERCHEV, vous avez raison ! Ce n'est pas la meilleure utilisation de l'impressionnant INDEX MATCH. Malgré le fait que je sois un fan d'INDEX MATCH, c'est un peu plus difficile que VLOOKUP. Si la récupération des données d'une colonne à droite est tout ce que vous voulez faire, je vous recommande d'utiliser RECHERCHEV.

La raison pour laquelle j'ai montré cet exemple, qui peut également être facilement réalisé avec RECHERCHEV, est de vous montrer comment fonctionne INDEX MATCH dans un cadre simple.

Permettez-moi maintenant de montrer un avantage de INDEX MATCH.

Supposons que vous ayez les mêmes données, mais au lieu de les avoir en colonnes, vous les avez en lignes (comme indiqué ci-dessous).

Vous savez quoi, vous pouvez toujours utiliser le combo INDEX MATCH pour obtenir les notes de Jim.

Voici la formule qui vous donnera le résultat :

=INDICE($B$1:$K$2,2,MATCH(“Jim”,$B$1:$K$1,0))

Notez que vous devez modifier la plage et changer les parties de ligne/colonne pour que cette formule fonctionne également pour les données horizontales.

Cela ne peut pas être fait avec RECHERCHEV, mais vous pouvez toujours le faire facilement avec RECHERCHEH.

La combinaison INDEX MATCH peut facilement gérer des données horizontales et verticales.

Cliquez ici pour télécharger le fichier exemple

Exemple 2 : recherche vers la gauche

C'est plus courant que vous ne le pensez.

Souvent, vous devrez peut-être récupérer les données d'une colonne située à gauche de la colonne contenant la valeur de recherche.

Quelque chose comme indiqué ci-dessous :

Pour connaître les ventes de Michael, vous devrez faire une recherche sur la gauche.

Si vous pensez RECHERCHEV, laissez-moi vous arrêter là.

RECHERCHEV n'est pas fait pour rechercher et récupérer les valeurs sur la gauche.

Pouvez-vous toujours le faire en utilisant RECHERCHEV ?

Oui, vous pouvez!

Mais cela peut devenir une formule longue et laide.

Donc, si vous souhaitez effectuer une recherche et récupérer des données dans les colonnes de gauche, vous feriez mieux d'utiliser le combo INDEX MATCH.

Vous trouverez ci-dessous la formule qui obtiendra le numéro de vente de Michael :

=INDICE($A$2:$C$11,MATCH("Michael",C2:C11,0),2)

Un autre point ici pour INDEX MATCH. RECHERCHEV peut extraire les données uniquement des colonnes situées à droite de la colonne contenant la valeur de recherche.

Exemple 3 : recherche bidirectionnelle

Jusqu'à présent, nous avons vu les exemples où nous voulions récupérer les données de la colonne adjacente à la colonne qui a la valeur de recherche.

Mais dans la vraie vie, les données s'étendent souvent sur plusieurs colonnes.

INDEX MATCH peut facilement gérer une recherche bidirectionnelle.

Vous trouverez ci-dessous un ensemble de données sur les notes de l'élève dans trois matières différentes.

Si vous souhaitez obtenir rapidement les notes d'un étudiant dans les trois matières, vous pouvez le faire avec INDEX MATCH.

La formule ci-dessous vous donnera les notes de Jim pour les trois sujets (copier et coller dans une cellule et faire glisser pour remplir d'autres cellules ou copier et coller sur d'autres cellules).

=INDICE($B$2:$D$11,MATCH($F$3,$A$2:$A$11,0),MATCH(G$2,$B$1:$D$1,0))

Permettez-moi également d'expliquer rapidement cette formule.

La formule INDEX utilise B2:D11 comme plage.

Le premier MATCH utilise le nom (Jim dans la cellule F3) et récupère sa position dans la colonne des noms (A2:A11). Cela devient le numéro de ligne à partir duquel les données doivent être extraites.

La deuxième formule MATCH utilise le nom du sujet (dans la cellule G2) pour obtenir la position de ce nom de sujet spécifique dans B1:D1. Par exemple, les mathématiques sont égales à 1, la physique à 2 et la chimie à 3.

Étant donné que ces positions MATCH sont introduites dans la fonction INDEX, elle renvoie le score en fonction du nom de l'étudiant et du nom de la matière.

Cette formule est dynamique, ce qui signifie que si vous modifiez le nom de l'étudiant ou les noms des matières, cela fonctionnera toujours et récupérera les données correctes.

Une grande chose à propos de l'utilisation d'INDEX/MATCH est que même si vous intervertissez les noms des sujets, il continuera à vous donner le résultat correct.

Exemple 4 : Valeur de recherche à partir de plusieurs colonnes/critères

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez récupérer les marques pour « Mark Long ».

Étant donné que les données sont dans deux colonnes, je ne peux pas rechercher Mark et obtenir les données.

Si je le fais de cette façon, je vais obtenir les données de marques pour Mark Frost et non Mark Long (car la fonction MATCH me donnera le résultat pour la MARQUE qu'elle rencontre).

Une façon de le faire est de créer une colonne d'aide et de combiner les noms. Une fois que vous avez la colonne d'aide, vous pouvez utiliser RECHERCHEV et obtenir les données des marques.

Si vous souhaitez apprendre comment procéder, lisez ce didacticiel sur l'utilisation de RECHERCHEV avec plusieurs critères.

Mais avec le combo INDEX/MATCH, vous n'avez pas besoin d'une colonne d'aide. Vous pouvez créer une formule qui gère plusieurs critères dans la formule elle-même.

La formule ci-dessous donnera le résultat.

=INDICE($C$2:$C$11,MATCH($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0))

Permettez-moi d'expliquer rapidement ce que fait cette formule.

La partie MATCH de la formule combine la valeur de recherche (Mark et Long) ainsi que l'ensemble du tableau de recherche. Lorsque $A$2:A11&”|”&$B$2:$B$11 est utilisé comme tableau de recherche, il vérifie en fait la valeur de recherche par rapport à la chaîne combinée du prénom et du nom (séparés par le symbole pipe).

Cela garantit que vous obtenez le bon résultat sans utiliser de colonnes d'aide.

Vous pouvez également effectuer ce type de recherche (lorsqu'il existe plusieurs colonnes/critères) avec RECHERCHEV, mais vous devez utiliser une colonne d'aide. Le combo INDEX MATCH permet de le faire légèrement facilement sans aucune colonne d'aide.

Exemple 5 : obtenir des valeurs de la ligne/colonne entière

Dans les exemples ci-dessus, nous avons utilisé la fonction INDEX pour obtenir la valeur d'une cellule spécifique. Vous fournissez le numéro de ligne et de colonne, et il renvoie la valeur dans cette cellule spécifique.

Mais vous pouvez faire plus.

Vous pouvez également utiliser la fonction INDEX pour obtenir les valeurs d'une ligne ou d'une colonne entière.

Et comment cela peut-il être utile demandez-vous!

Supposons que vous vouliez connaître le score total de Jim dans les trois matières.

Vous pouvez utiliser la fonction INDEX pour obtenir d'abord toutes les notes de Jim, puis utiliser la fonction SUM pour obtenir un total.

Voyons comment faire cela.

Ci-dessous, j'ai les scores de tous les étudiants dans trois matières.

La formule ci-dessous me donnera le score total de Jim dans les trois matières.

=SOMME(INDICE($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0),0))

Laissez-moi vous expliquer comment fonctionne cette formule.

L'astuce ici est d'utiliser 0 comme numéro de colonne.

Lorsque vous utilisez 0 comme numéro de colonne dans la fonction INDEX, toutes les valeurs de ligne seront renvoyées. De même, si vous utilisez 0 comme numéro de ligne, toutes les valeurs de la colonne seront renvoyées.

Ainsi, la partie ci-dessous de la formule renvoie un tableau de valeurs - {97, 70, 73}

INDICE($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0),0)

Si vous entrez simplement cette formule ci-dessus dans une cellule d'Excel et appuyez sur Entrée, vous verrez une #VALEUR ! Erreur. C'est parce qu'il ne renvoie pas une seule valeur, mais un tableau de valeurs.

Mais ne vous inquiétez pas, le tableau de valeurs est toujours là. Vous pouvez le vérifier en sélectionnant la formule et en appuyant sur la touche F9. Il vous montrera le résultat de la formule qui dans ce cas est un tableau de trois valeurs - {97, 70, 73}

Maintenant, si vous enveloppez cette formule INDEX dans la fonction SUM, elle vous donnera la somme de toutes les notes obtenues par Jim.

Vous pouvez également utiliser le même pour obtenir les notes les plus élevées, les plus basses et moyennes de Jim.

Tout comme nous l'avons fait pour un étudiant, vous pouvez également le faire pour un sujet. Par exemple, si vous voulez le score moyen d'un sujet, vous pouvez conserver le numéro de ligne à 0 dans la formule INDEX et cela vous donnera toutes les valeurs de colonne de ce sujet.

Cliquez ici pour télécharger le fichier exemple

Exemple 6 : Trouver la note de l'élève (technique de correspondance approximative)

Jusqu'à présent, nous avons utilisé la formule MATCH pour obtenir la correspondance exacte de la valeur de recherche.

Mais vous pouvez également l'utiliser pour faire une correspondance approximative.

Maintenant, qu'est-ce que le match approximatif?

Laisse-moi expliquer.

Lorsque vous recherchez des éléments tels que des noms ou des identifiants, vous recherchez une correspondance exacte. Mais parfois, vous devez connaître la plage dans laquelle se trouvent vos valeurs de recherche. C'est généralement le cas des nombres.

Par exemple, en tant que professeur de classe, vous voudrez peut-être savoir quelle est la note de chaque élève dans une matière, et la note est décidée en fonction du score.

Vous trouverez ci-dessous un exemple dans lequel je veux la note de tous les étudiants et la note est décidée en fonction du tableau de droite.

Donc, si un élève obtient moins de 33, la note est F et s'il obtient moins de 50 mais plus de 33, c'est E, et ainsi de suite.

Vous trouverez ci-dessous la formule qui le fera.

=INDICE($F$3:$F$8,MATCH(B2,$E$3:$E$8,1),1)

Laissez-moi vous expliquer comment fonctionne cette formule.

Dans la fonction MATCH, nous avons utilisé 1 comme argument [match_type]. Cet argument renverra la plus grande valeur inférieure ou égale à la valeur de recherche.

Cela signifie que la formule MATCH parcourt la plage de marques et dès qu'elle trouve une plage de marques égale ou inférieure à la valeur des marques de recherche, elle s'arrête là et renvoie sa position.

Donc, si la valeur de la marque de recherche est 20, la fonction MATCH renverrait 1 et si c'est 85, elle renverrait 5.

Et la fonction INDEX utilise cette valeur de position pour obtenir la note.

IMPORTANT : Pour que cela fonctionne, vos données doivent être triées par ordre croissant. Si ce n'est pas le cas, vous pouvez obtenir des résultats erronés.

Notez que ce qui précède peut également être fait en utilisant la formule VLOOKUP ci-dessous :

=RECHERCHEV(B2,$E$3:$F$8,2,VRAI)

Mais la fonction MATCH peut aller encore plus loin en ce qui concerne la correspondance approximative.

Vous pouvez également avoir des données décroissantes et utiliser la combinaison INDEX MATCH pour trouver le résultat. Par exemple, si je modifie l'ordre du tableau des notes (comme indiqué ci-dessous), je peux toujours trouver les notes des étudiants.

Pour ce faire, tout ce que j'ai à faire est de changer l'argument [match_type] en -1.

Ci-dessous la formule que j'ai utilisé :

=INDICE($F$3:$F$8,MATCH(B2,$E$3:$E$8,-1),1)

RECHERCHEV peut également faire une correspondance approximative, mais uniquement lorsque les données sont triées par ordre croissant (mais cela ne fonctionne pas si les données sont triées par ordre décroissant).

Exemple 7 : recherches sensibles à la casse

Jusqu'à présent, toutes les recherches que nous avons effectuées ont été insensibles à la casse.

Cela signifie que si la valeur de recherche était Jim ou JIM ou Jim, cela n'avait pas d'importance. Vous obtiendrez le même résultat.

Mais que faire si vous voulez que la recherche soit sensible à la casse.

C'est généralement le cas lorsque vous avez de grands ensembles de données et une possibilité de répétition ou de noms/identifiants distincts (la seule différence étant le cas)

Par exemple, supposons que j'ai l'ensemble de données d'étudiants suivant où il y a deux étudiants avec le nom Jim (la seule différence étant que l'un est entré comme Jim et l'autre comme Jim).

Notez qu'il y a deux étudiants avec le même nom - Jim (cellules A2 et A5).

Étant donné qu'une recherche normale ne fonctionnerait pas, vous devez effectuer une recherche sensible à la casse.

Voici la formule qui vous donnera le bon résultat. Comme il s'agit d'une formule matricielle, vous devez utiliser Control + Shift + Enter.

=INDICE($B$2:$B$11, CORRESPONDANCE(VRAI,EXACT(D3,A2:A11),0),1)

Laissez-moi vous expliquer comment fonctionne cette formule.

La fonction EXACT vérifie une correspondance exacte de la valeur de recherche (qui est « jim » dans ce cas). Il parcourt tous les noms et renvoie FALSE si ce n'est pas une correspondance et TRUE si c'est une correspondance.

Donc la sortie de la fonction EXACT dans cet exemple est - {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Notez qu'il n'y a qu'un seul VRAI, c'est-à-dire lorsque la fonction EXACT a trouvé une correspondance parfaite.

La fonction MATCH trouve ensuite la position de TRUE dans le tableau renvoyé par la fonction EXACT, qui est 4 dans cet exemple.

Une fois que nous avons la position, la fonction INDEX l'utilise pour trouver les marques.

Exemple 8 : Trouver la correspondance la plus proche

Avançons un peu maintenant.

Supposons que vous ayez un ensemble de données où vous souhaitez trouver la personne qui a l'expérience de travail la plus proche de l'expérience requise (mentionnée dans la cellule D2).

Bien que les formules de recherche ne soient pas conçues pour cela, vous pouvez les combiner avec d'autres fonctions (telles que MIN et ABS) pour y parvenir.

Ci-dessous la formule qui va trouver la personne avec l'expérience la plus proche de celle recherchée et retourner le nom de la personne. Notez que l'expérience doit être la plus proche (ce qui peut être inférieur ou supérieur).

=INDICE($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))

Comme il s'agit d'une formule matricielle, vous devez utiliser Control + Shift + Enter.

L'astuce de cette formule consiste à modifier la valeur de recherche et le tableau de recherche pour trouver la différence d'expérience minimale entre les valeurs requises et réelles.

Avant d'expliquer la formule, comprenons comment vous le feriez manuellement.

Vous allez parcourir chaque cellule de la colonne B et trouver la différence dans l'expérience entre ce qui est requis et celui qu'une personne a. Une fois que vous avez toutes les différences, vous trouverez celle qui est minimale et récupérez le nom de cette personne.

C'est exactement ce que nous faisons avec cette formule.

Laisse-moi expliquer.

La valeur de recherche dans la formule MATCH est MIN(ABS(D2-B2:B15)).

Cette partie vous donne la différence minimale entre l'expérience donnée (qui est de 2,5 ans) et toutes les autres expériences. Dans cet exemple, il renvoie 0,3

Notez que j'ai utilisé l'ABS pour m'assurer que je recherche le plus proche (ce qui peut être plus ou moins que l'expérience donnée).

Maintenant, cette valeur minimale devient notre valeur de recherche.

Le tableau de recherche dans la fonction MATCH est ABS(D2-$B$2:$B$15).

Cela nous donne un tableau de nombres dont 2,5 (l'expérience requise) a été soustrait.

Nous avons donc maintenant une valeur de recherche (0,3) et un tableau de recherche ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})

La fonction MATCH trouve la position de 0,3 dans ce tableau, qui est également la position du nom de la personne qui a l'expérience la plus proche.

Ce numéro de position est ensuite utilisé par la fonction INDEX pour renvoyer le nom de la personne.

Lecture connexe: rechercher la correspondance la plus proche dans Excel (exemples utilisant des formules de recherche)

Cliquez ici pour télécharger le fichier exemple

Exemple 9 : Utiliser INDEX MATCH avec des caractères génériques

Si vous souhaitez rechercher une valeur lorsqu'il existe une correspondance partielle, vous devez utiliser des caractères génériques.

Par exemple, vous trouverez ci-dessous un ensemble de données sur le nom de l'entreprise et ses capitalisations boursières et vous souhaitez obtenir la capitalisation boursière. données pour les trois sociétés sur la droite.

Comme ce ne sont pas des correspondances exactes, vous ne pouvez pas effectuer une recherche régulière dans ce cas.

Mais vous pouvez toujours obtenir les bonnes données en utilisant un astérisque (*), qui est un caractère générique.

Vous trouverez ci-dessous la formule qui vous donnera les données en faisant correspondre les noms de sociétés de la colonne principale et en récupérant le chiffre de la capitalisation boursière correspondant.

=INDICE($B$2:$B$10,MATCH(D2&”*”,$A$2:$A$10,0),1)

Laissez-moi vous expliquer comment fonctionne cette formule.

Comme il n'y a pas de correspondance exacte des valeurs de recherche, j'ai utilisé D2&”*” comme valeur de recherche dans la fonction MATCH.

Un astérisque est un caractère générique qui représente un nombre quelconque de caractères. Cela signifie que Apple* dans la formule signifierait n'importe quelle chaîne de texte commençant par le mot Apple et pouvant avoir n'importe quel nombre de caractères après.

Donc quand Pomme* est utilisée comme valeur de recherche et la formule MATCH la recherche dans la colonne A, elle renvoie la position de « Apple Inc. », car elle commence par le mot Apple.

Vous pouvez également utiliser des caractères génériques pour rechercher des chaînes de texte où la valeur de recherche se situe entre les deux. Par exemple, si vous utilisez *Apple* comme valeur de recherche, il trouvera toute chaîne contenant le mot pomme n'importe où.

Remarque : Cette technique fonctionne bien lorsque vous n'avez qu'une seule instance de correspondance. Mais si vous avez plusieurs instances de correspondance (par exemple Apple Inc et Apple Corporation, la fonction MATCH renverrait uniquement la position de la première instance correspondante.

Exemple 10 : Recherche à trois voies

Il s'agit d'une utilisation avancée d'INDEX MATCH, mais je vais quand même la couvrir pour vous montrer la puissance de cette combinaison.

Rappelez-vous que j'ai dit que la fonction INDEX a deux syntaxes :

=INDEX (array, row_num, [col_num]) =INDEX (array, row_num, [col_num], [area_num])

Jusqu'à présent, dans tous nos exemples, nous n'avons utilisé que le premier.

Mais pour une recherche à trois voies, vous devez utiliser la deuxième syntaxe.

Permettez-moi d'abord d'expliquer ce que signifie un regard à trois voies.

Dans une recherche bidirectionnelle, nous utilisons la formule INDEX MATCH pour obtenir les notes lorsque nous avons le nom de l'étudiant et le nom du sujet. Par exemple, récupérer les notes de Jim en mathématiques est une recherche bidirectionnelle.

Un regard à trois voies y ajouterait une autre dimension. Par exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous vouliez connaître le score de Jim en mathématiques à l'examen de mi-parcours, il s'agirait alors d'une recherche à trois voies.

Ci-dessous la formule qui donnera le résultat.

=INDICE(($B$3:$D$7,$B$11:$D$15,$B$19:$D$23),MATCH($F$5,$A$3:$A$7,0),MATCH(G$4 ,$B$2:$D$2,0),(IF(G$3="Test unitaire",1,IF(G$3="Mid Term",2,3))))

La formule ci-dessus a vérifié trois choses - le nom de l'étudiant, le sujet et l'examen. Après avoir trouvé la bonne valeur, il la renvoie dans la cellule.

Laissez-moi vous expliquer comment fonctionne cette formule en décomposant la formule en plusieurs parties.

• tableau - ($B$3:$D$7,$B$11:$D$15,$B$19:$D$23): Au lieu d'utiliser un seul tableau, dans ce cas, j'ai utilisé trois tableaux entre parenthèses.
• num_ligne - MATCH($F$5,$A$3:$A$7,0): La fonction MATCH est utilisée pour trouver la position du nom de l'étudiant dans la cellule $F$5 dans la liste des noms de l'étudiant.
• col_num - MATCH(G$4,$B$2:$D$2,0): La fonction MATCH est utilisée pour trouver la position du nom du sujet dans la cellule $B$2 dans la liste des noms de sujet.
• [area_num] - IF(G$3=”Unit Test”,1,IF(G$3=”Mid Term”,2,3)): La valeur du numéro de zone indique à la fonction INDEX lequel des trois tableaux utiliser pour récupérer la valeur. Si l'examen est Unit Term, la fonction IF renverrait 1 et la fonction INDEX utiliserait le premier tableau pour récupérer la valeur. Si l'examen est à mi-parcours, la formule SI renverrait 2, sinon elle renverrait 3.

Il s'agit d'un exemple avancé d'utilisation d'INDEX MATCH, et il est peu probable que vous trouviez une situation où vous devez l'utiliser. Mais il est toujours bon de savoir ce que les formules Excel peuvent faire.

Cliquez ici pour télécharger le fichier exemple

Pourquoi INDEX/MATCH est-il meilleur que VLOOKUP ?

Ou est-ce?

Oui, c'est - dans la plupart des cas.

Je présenterai mon cas dans quelques temps.

Mais avant de faire ça, permettez-moi de dire ceci - RECHERCHEV est une fonction extrêmement utile et je l'adore. Il peut faire beaucoup de choses dans Excel et je l'utilise moi-même de temps en temps. Cela dit, cela ne veut pas dire qu'il ne peut y avoir rien de mieux, et INDEX/MATCH (avec plus de flexibilité et de fonctionnalités) est meilleur.

Donc, si vous voulez faire une recherche de base, vous feriez mieux d'utiliser RECHERCHEV.

INDEX/MATCH est VLOOKUP sur les stéroïdes. Et une fois que vous aurez appris INDEX/MATCH, vous préférerez peut-être toujours l'utiliser (notamment en raison de sa flexibilité).

Sans aller trop loin, permettez-moi de vous donner rapidement les raisons pour lesquelles INDEX/MATCH est meilleur que VLOOKUP.

INDEX/MATCH peut regarder à gauche (ainsi qu'à droite) de la valeur de recherche

Je l'ai couvert dans l'un des exemples ci-dessus.

Si vous avez une valeur qui se trouve à gauche de la valeur de recherche, vous ne pouvez pas le faire avec RECHERCHEV

Du moins pas avec VLOOKUP.

Oui, vous pouvez combiner RECHERCHEV avec d'autres formules et le faire, mais cela devient compliqué et désordonné.

INDEX/MATCH, d'autre part, est fait pour rechercher partout (que ce soit à gauche, à droite, en haut ou en bas)

INDEX/MATCH peut fonctionner avec des plages verticales et horizontales

Encore une fois, avec tout le respect de RECHERCHEV, il n'est pas fait pour le faire.

Après tout, le V dans RECHERCHEV signifie vertical.

VLOOKUP ne peut parcourir que les données verticales, tandis que INDEX/MATCH peut parcourir les données verticalement et horizontalement.

Bien sûr, il existe la fonction RECHERCHEH pour s'occuper de la recherche horizontale, mais ce n'est pas RECHERCHEV alors… n'est-ce pas ?

J'aime le fait que le combo INDEX MATCH soit suffisamment flexible pour fonctionner avec des données verticales et horizontales.

RECHERCHEV ne peut pas fonctionner avec des données décroissantes

En ce qui concerne la correspondance approximative, RECHERCHEV et INDEX/MATCH sont au même niveau.

Mais INDEX MATCH prend le point car il peut également gérer des données par ordre décroissant.

Je montre cela dans l'un des exemples de ce tutoriel où nous devons trouver la note des étudiants en fonction du tableau de notation. Si le tableau est trié par ordre décroissant, RECHERCHEV ne fonctionnera pas (mais INDEX MATCH le ferait).

INDEX/MATCH peut être légèrement plus rapide

Je serai véridique. Je n'ai pas fait ce test moi-même.

Je compte sur la sagesse d'un maître Excel - Charley Kyd.

La différence de vitesse dans RECHERCHEV et INDEX/MATCH est à peine perceptible lorsque vous avez de petits ensembles de données. Mais si vous avez des milliers de lignes et de nombreuses colonnes, cela peut être un facteur décisif.

Dans son article, Charley Kyd déclare :

« Au pire, la méthode INDEX-MATCH est à peu près aussi rapide que VLOOKUP; au mieux, c'est beaucoup plus rapide.

INDEX/MATCH est indépendant de la position réelle de la colonne

Si vous avez un ensemble de données comme indiqué ci-dessous lorsque vous récupérez le score de Jim en physique, vous pouvez le faire en utilisant RECHERCHEV.

Et pour ce faire, vous pouvez spécifier le numéro de colonne comme 3 dans RECHERCHEV.

Tout est bon.

Mais que se passe-t-il si je supprime la colonne Math.

Dans ce cas, la formule RECHERCHEV se brisera.

Pourquoi? - Parce qu'il a été codé en dur pour utiliser la troisième colonne, et lorsque je supprime une colonne entre les deux, la troisième colonne devient la deuxième colonne.

L'utilisation d'INDEX/MATCH, dans ce cas, est préférable car vous pouvez rendre le numéro de colonne dynamique en utilisant MATCH. Ainsi, au lieu d'un numéro de colonne, il vérifie le nom du sujet et l'utilise pour renvoyer le numéro de colonne.

Vous pouvez sûrement le faire en combinant RECHERCHEV avec MATCH, mais si vous combinez quand même, pourquoi ne pas le faire avec INDEX qui est beaucoup plus flexible.

Lorsque vous utilisez INDEX/MATCH, vous pouvez insérer/supprimer en toute sécurité des colonnes dans votre ensemble de données.

Malgré tous ces facteurs, il y a une raison pour laquelle RECHERCHEV est si populaire.

Et c'est une grande raison.

RECHERCHEV est plus facile à utiliser

RECHERCHEV ne prend qu'un maximum de quatre arguments. Si vous pouvez envelopper votre tête autour de ces quatre, vous êtes prêt à partir.

Et comme la plupart des cas de recherche de base sont également gérés par RECHERCHEV, il est rapidement devenu la fonction Excel la plus populaire.

Je l'appelle le roi des fonctions Excel.

INDEX/MATCH, en revanche, est un peu plus difficile à utiliser. Vous pouvez avoir un problème lorsque vous commencez à l'utiliser, mais pour un débutant, RECHERCHEV est beaucoup plus facile à expliquer et à apprendre.

Et ce n'est pas un jeu à somme nulle.

Donc, si vous êtes nouveau dans le monde de la recherche et que vous ne savez pas comment utiliser RECHERCHEV, mieux vaut l'apprendre.

J'ai un guide détaillé sur l'utilisation de RECHERCHEV dans Excel (avec de nombreux exemples)

Mon intention dans cet article n'est pas de dresser deux fonctions géniales l'une contre l'autre. Je voulais vous montrer la puissance du combo INDEX MATCH et toutes les grandes choses qu'il peut faire.

J'espère que vous avez trouvé cet article utile.

Faites-moi part de vos réflexions dans la section commentaires, et si vous trouvez une erreur dans ce tutoriel, faites-le moi savoir.