Comment inverser VLOOKUP dans Excel avec INDEX-MATCH ?

Pour comprendre le fonctionnement d’INDEX et de MATCH en tant que formule de recherche inversée, vous devez choisir une chose simple : Match indique à l’index la position (numéro de cellule) d’une valeur dans une colonne ou dans une ligne, puis l’index renvoie cette valeur. valeur en utilisant cette position (numéro de cellule) .

Pensez comme ça, la fonction MATCH est un agent d’infiltration qui trouve le criminel et la fonction INDEX est un flic qui arrête ce criminel par la suite.

Mais, apprenons en détail comment nous pouvons combiner ces deux fonctions. Vous trouverez ci-dessous la syntaxe d’INDEX, comme vous le savez.

INDEX(tableau, num_ligne, [num_colonne])

Dans la fonction INDEX, l’argument row_num lui indique à partir de quelle ligne il doit renvoyer la valeur. Disons que si vous entrez 4, il renverra la valeur de la 4ème ligne.

Pour créer la formule de recherche inversée, nous devons remplacer MATCH par row_argument de INDEX.

Lorsque nous utilisons MATCH, il recherche la valeur dans la colonne de recherche et renvoie le numéro de cellule de cette valeur. Et puis, INDEX utilise ce nombre pour déterminer la position de la cellule à partir de la colonne de valeur.

En fin de compte, il renvoie la valeur de cette cellule et vous obtenez la valeur que vous recherchez. Mais maintenant, travaillons avec un exemple réel. Ci-dessous, nous avons une liste des villes et des noms des employés qui y travaillent.

Ici, nous devons rechercher le nom de l’employé qui travaille à Mumbai. Maintenant, si vous regardez les données, dans la colonne où vous avez les villes, c’est notre colonne de recherche, et dans la colonne où vous avez les noms des employés, c’est votre colonne de valeur. Et la formule sera :

=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

Décomposons cette formule en deux parties pour la comprendre.

Partie 1 : Dans la première partie, nous avons utilisé la fonction match pour rechercher la valeur « Mumbai » et elle a renvoyé le « 5 » qui est la position de la cellule dans laquelle vous avez la valeur « Mumbai » dans la colonne des villes.

Partie 2 : Dans la deuxième partie, nous avons utilisé INDEX et fait référence à la colonne du nom de l’employé pour la recherche de la valeur. Ici, la fonction d’index sait que vous voulez la valeur de la 5ème cellule de la colonne. Ainsi, il a renvoyé « Siya » dans le résultat.

Plus d’exemples d’INDEX et de correspondance

Nous avons devant nous certains des problèmes courants que nous avons résolus à l’aide de la formule INDEX MATCH. Accédez aux exemples de fichiers : assurez-vous de télécharger ces exemples de fichiers à partir d’ici pour suivre chaque exemple.

1. Recherche de base avec INDEX – MATCH

Une recherche normale est l’une des tâches les plus importantes que vous devez effectuer avec les formules de recherche et INDEX MATCH est parfait pour cela. Ici, nous avons un tableau de données avec l’ID et le nom de l’employé. Chaque identifiant est unique et vous devez rechercher le nom de l’employé avec son identifiant.

Supposons que vous souhaitiez rechercher le nom EMP-132. Pour cela, la formule sera :

=INDEX(name_column,MATCH(emp-id,emp-id_column,0))

voici comment cette formule fonctionne

Tout d’abord , MATCH correspond à l’emp id dans la colonne emp id et renvoie le numéro de cellule de l’id que vous recherchez. Ici, le numéro de ligne est 6.

Après cela , INDEX renvoie le nom de l’employé à partir de la colonne de nom en utilisant le même numéro de cellule.

2. Regardez vers la gauche

VLOOKUP ne peut pas aller à gauche lors de la recherche d’une valeur. Comme je l’ai mentionné, dans INDEX et MATCH, vous pouvez effectuer une recherche dans n’importe quelle direction. Dans le tableau de données ci-dessous, vous avez la colonne du numéro de facture après la colonne du montant.

Donc, si vous souhaitez rechercher le montant d’une facture particulière, cela n’est pas possible avec VLOOKUP. Dans VLOOKUP lorsque vous sélectionnez une table, la première colonne de cette table sera la colonne de recherche.

Mais, ici dans ce tableau, nous devons utiliser la dernière colonne du tableau comme colonne de recherche. Donc, pouce vers le bas pour VLOOKUP ici. Appelons INDEX et MATCH pour le sauvetage et la formule sera :

=INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

… voici comment cette formule fonctionne

• Tout d’abord , vous vous êtes référé à la colonne montant dans la fonction index. C’est la colonne à partir de laquelle nous devons obtenir la valeur.
• Deuxièmement , dans l’argument row_number de la fonction d’index, vous avez utilisé la fonction de correspondance et spécifié le numéro de facture, fait référence à la colonne de facture et utilisé zéro pour la correspondance exacte.
• Troisièmement , la fonction de correspondance renvoie le numéro de cellule de la facture à partir de la plage.

Et à la fin, INDEX utilise ce nombre pour renvoyer le montant en positionnant la cellule à partir de la colonne du montant.

3. Recherche approximative

Tout comme VLOOKUP, vous pouvez également utiliser INDEX/MATCH pour une recherche approximative.

La recherche approximative peut être utile lorsque la valeur que vous recherchez ne figure pas dans la liste et que vous souhaitez obtenir la correspondance la plus proche. Dans le tableau ci-dessous, vous avez une liste de notes en fonction des notes.

Et, si vous voulez obtenir une note de 79, vous pouvez utiliser la formule ci-dessous.

=INDEX(B2:B6,MATCH(D3,A2:A6,1))

… voici comment cette formule fonctionne

Dans cette formule, nous avons utilisé 1 dans la fonction de correspondance pour le match_type qui lui permet d’effectuer une recherche approximative. Il renvoie la première valeur qui est inférieure ou égale à la valeur de recherche.

Pour 79, la première valeur la plus basse est 75, et pour 75, la note est B. C’est pourquoi vous obtenez un B dans le résultat.

4. RECHERCHE horizontale

Comme vous le savez, HLOOKUP est destiné à une recherche horizontale, mais vous pouvez également utiliser INDEX et MATCH pour cela. Ici, dans le tableau de données ci-dessous, vous avez un tableau horizontal pour les ventes mensuelles et vous souhaitez obtenir la valeur des ventes pour « Mai ».

Et la formule sera :

=INDEX(amount,0,MATCH(lookup_month,months,0))

… voici comment cette formule fonctionne

Dans la formule ci-dessus, au lieu d’utiliser MATCH dans l’argument row_num de l’index, nous l’avons utilisé dans column_num. Et, match renvoie le numéro de colonne du mois de mai.

Et puis INDEX renvoie la valeur de la colonne de résultat en fonction du numéro de position.

5. Recherche bidirectionnelle

Dans une recherche bidirectionnelle, nous devons obtenir une valeur à partir d’une table. Il suffit de regarder le tableau ci-dessous où vous avez les montants des ventes par zone et par produit.

Maintenant, si vous voulez obtenir le montant des ventes d’un produit pour une zone particulière, vous avez besoin d’une recherche bidirectionnelle et pour cela, vous devez utiliser une combinaison d’INDEX MATCH MATCH. Oui, ici, vous devez utiliser MATCH deux fois.

Dans une combinaison normale de INDEX et MATCH, vous utilisez MATCH pour le numéro de ligne, mais dans une recherche bidirectionnelle, vous devez également l’utiliser pour le numéro de colonne. Les formules seront :

=INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

… voici comment cette formule fonctionne

Dans la formule ci-dessus, pour obtenir le montant des ventes à partir de la table, vous avez utilisé la fonction d’index, puis la fonction de correspondance pour ses arguments row_num et column_num. La fonction de correspondance qui est l’argument column_num renvoie 5 comme valeur Product-D dans la 5ème ligne de la plage que vous avez référencée.

Et, la fonction de correspondance qui se trouve dans l’argument row_num renvoie 2 car la valeur zone nord se trouve dans la deuxième colonne de la plage que vous avez référencée.

Maintenant, avec ces valeurs, la fonction d’index a renvoyé la valeur qui se trouve dans la 2e colonne et la 5e ligne : 1456.

6. Sensible à la casse

Si vous rencontrez un problème lorsque vous avez deux mêmes valeurs dans une liste ou dans une colonne mais dans une casse de texte différente, vous pouvez effectuer une recherche sensible à la casse pour rechercher la bonne valeur. Jetons un coup d’œil à la liste des étudiants ci-dessous où vous avez le prénom et dans la deuxième colonne, vous avez marqué.

Et, au début, il y a des noms qui sont identiques mais dans des cas de texte différents. Par exemple, John Parker et JOHN Mathew. Supposons que vous souhaitiez rechercher les marques de « JOHN » et non de « John », vous pouvez créer une recherche de correspondance exacte avec INDEX et MATCH. Et la formule sera :

=INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

… voici comment cette formule fonctionne

Ici, dans cette formule, vous avez utilisé la fonction EXACT dans la fonction de correspondance. Comme la fonction de correspondance n’est pas en mesure de rechercher une valeur sensible à la casse et EXACT est une fonction parfaite pour cela.

Il peut comparer deux valeurs et renvoyer TRUE si elles sont exactement identiques (y compris la casse), mais vous devez entrer cette formule sous forme de tableau car vous devez comparer la colonne entière avec une seule valeur dans EXACT. Lorsque vous y entrez, il renverra un tableau comme celui-ci.

=INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

À partir de là, vous devez utiliser la fonction de correspondance afin d’obtenir la position de TRUE à partir du tableau.

À ce stade, vous avez le numéro de cellule (numéro de ligne) de la valeur que vous devez rechercher. Et, vous pouvez utiliser INDEX pour cette valeur de la colonne.

Point important : Si vous obtenez plus d’un VRAI avec EXACT, la correspondance renverra simplement le nombre pour le premier VRAI

7. Avec des caractères génériques avec INDEX MATCH

Les caractères génériques sont très utiles. Vous pouvez effectuer une recherche partielle à l’aide de caractères génériques . Et, la meilleure partie est que, comme toutes les autres formules de recherche, vous pouvez également utiliser des caractères génériques avec index et correspondance.

Jetez simplement un coup d’œil à la liste de noms ci-dessous où vous avez les noms et prénoms des employés et leur âge.

À partir de cette liste, vous devez obtenir l’âge d’un employé particulier (Sondra). Mais le fait est que vous ne connaissez que le prénom.

Et, si vous utilisez un astérisque, vous pouvez rechercher l’âge de Sondra en utilisant le prénom. Pour cela, la formule sera :

=INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

… voici comment cette formule fonctionne

Un astérisque est un caractère générique qui peut remplacer n nombre de caractères. Ainsi, lorsque vous l’avez utilisé après le prénom, il a remplacé le nom de famille.

8. Valeur la plus basse

Disons que vous avez une liste d’étudiants avec leurs scores comme ci-dessous. Et maintenant, à partir de cette liste, vous voulez rechercher le nom de l’étudiant qui a le score le plus bas.

Pour cela, vous pouvez utiliser la fonction MIN avec index et match et la formule sera :

=INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

Vous avez les noms des élèves dans la colonne A et leurs scores dans la colonne B.

Ainsi, lorsque vous insérez cette formule dans une cellule et appuyez sur Entrée, elle renverra le nom de l’étudiant avec le score le plus bas, c’est-à-dire Librada Bastian.

Explication

Dans cette formule, nous avons trois parties différentes.

Dans la première partie, la fonction MIN renvoie le score le plus bas.

Après cela, dans la deuxième partie, la fonction de correspondance renvoie la cellule pour ce score le plus bas.

Au final, la fonction d’index renvoie la valeur de la colonne du nom de l’étudiant en utilisant la même position de cellule renvoyée par match.

Astuce : De la même manière, vous pouvez également obtenir le nom de l’élève qui obtient le score le plus élevé.

9. Meilleur nième score

Maintenant, pensez comme ça, vous avez une liste d’étudiants avec leurs scores aux examens et à partir de cette liste, vous voulez obtenir le nom de l’étudiant qui a obtenu le 2ème score le plus élevé.

Le truc, c’est que vous ne savez pas quel est le deuxième score le plus élevé.

Normalement, lorsque vous recherchez une valeur avec des formules de recherche, vous êtes sûr de la valeur que vous recherchez. Mais, ici, vous ne savez pas quel est le deuxième score le plus élevé.

Donc, pour cela, vous pouvez combiner une grande fonction avec un index et le faire correspondre. La grande fonction vous aidera à déterminer la deuxième valeur la plus élevée de la plage.

Et la formule sera :

 =INDEX(student_names,MATCH(LARGE(score,2),score,0)) 

… voici comment cette formule fonctionne

Dans cette formule, vous avez utilisé la grande fonction à l’intérieur de la fonction de correspondance pour l’argument lookup_value. Et dans la grande fonction, vous avez mentionné la plage de score et 2 pour obtenir la deuxième valeur la plus élevée.

Une fois que la grande fonction renvoie la deuxième plus grande valeur, la fonction de correspondance utilise cette valeur et renvoie le numéro de cellule correspondant.

Et à la fin, la fonction d’index utilise ce numéro de cellule et renvoie le nom de l’étudiant.

10. Critères multiples

Normalement, la combinaison de l’index et de la correspondance est destinée à rechercher une seule valeur. Et c’est pourquoi vous utilisez une seule plage dans la fonction de correspondance.

Mais, parfois, lorsque vous êtes confronté à des données dans le monde réel, vous devez utiliser plusieurs critères pour rechercher une valeur.

Prenons l’exemple ci-dessous. Ici, vous avez une liste de produits avec plusieurs détails comme le nom du produit, la catégorie et la taille.

Et à partir de ces données, vous souhaitez obtenir le prix d’un produit particulier en utilisant tous les critères.

La formule sera donc :

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0)) 

Remarque : il s’agit d’une formule matricielle, vous devez donc la saisir à l’aide de ctrl + shift + enter.

… voici comment cette formule fonctionne

Dans cette formule, vous avez trois tableaux différents pour faire correspondre trois valeurs différentes et ces tableaux renvoient VRAI et FAUX où les valeurs correspondent.

Après cela, lorsque vous les multipliez les uns avec les autres, vous obtenez un tableau ou quelque chose comme ça.

=INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

Et la fonction match renvoie la position de 1 pour le tableau.

À la fin, INDEX renvoie le prix de la colonne de prix en utilisant le nombre renvoyé par la correspondance.

Astuce : si vous ne souhaitez pas utiliser de formule matricielle, vous pouvez utiliser la condition SUMPRODUCT .

11. Première valeur numérique d’une plage

Disons que vous avez une liste dans laquelle vous avez à la fois du texte et des valeurs numériques, et maintenant à partir de cette liste, vous voulez obtenir la première valeur numérique.

Pour cela, vous pouvez combiner la fonction ISNUMBER avec index/match. ISNUMBER peut vous aider à identifier quelle valeur est un nombre et laquelle est un texte.

La formule sera :

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0)) 

Vous devez entrer cette formule sous forme de tableau (en utilisant Ctrl + Maj + Entrée).

… voici comment cette formule fonctionne

Dans cette formule, ISNUMBER renvoie un tableau égal à la longueur de la liste et dans ce tableau, vous avez TRUE pour les valeurs qui sont des nombres et FALSE pour le reste des valeurs.

Après cela, vous avez utilisé TRUE dans la fonction de correspondance comme valeur de recherche. Ainsi, il renvoie le numéro de position du premier TRUE du tableau.

En fin de compte, l’utilisation de cet index de numéro de position renvoie la première valeur numérique.

12. Obtenez la première valeur non vide

Pensons comme ceci, vous avez une liste de valeurs où certaines des premières cellules sont vides et vous voulez obtenir la première valeur non vide.

Et, vous pouvez utiliser cette formule pour obtenir cette première valeur non vide.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0)) 

… voici comment cette formule fonctionne

Nous devons diviser cette formule en trois parties différentes pour la comprendre judicieusement.

Tout d’abord, vous avez utilisé la fonction ISBLANK dans la fonction match pour obtenir un tableau où vous avez TRUE pour les cellules vides et FALSE pour les cellules non vides.

Deuxièmement , MATCH renvoie le numéro de position du premier TRUE du tableau renvoyé par ISBLANK.

Donc, à ce stade, vous avez le numéro de cellule de la première valeur non vide.

Troisièmement , la fonction index renvoie simplement la première valeur non vide de la liste.

13. Texte le plus fréquent

Supposons maintenant qu’à partir d’une liste de valeurs de texte, vous deviez compter le texte le plus fréquent.

Dans la liste ci-dessous, vous avez des noms.

Mais, il y a quelques noms qui sont plus d’une fois.

Donc, maintenant, vous devez obtenir le nom qui a la plus haute occurrence dans la liste. Vous pouvez utiliser la formule ci-dessous qui est une combinaison de MODE, INDEX et MATCH.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0))) 

Lorsque vous insérez cette formule, elle renverra « Tamesha » qui est le nom le plus courant.

… voici comment cette formule fonctionne

Tout d’abord, MATCH comparera toute la gamme de noms avec elle-même. Et, ce faisant, il renverra un tableau où chaque texte représentera sa première position.

Prenons un exemple du nom « Tamesha » qui est notre nom le plus fréquent sur la liste. Maintenant , si vous regardez la liste, cela s’est d’abord produit sur la 8e cellule et ensuite sur la 12e cellule.

Mais, si vous regardez le tableau, pour toutes les positions où nous avons « Tamesha », il a renvoyé 8 qui est sa première position.

Après cela, à partir du tableau renvoyé par le MATCH, la fonction mode renverra le nombre le plus fréquent qui est le numéro de cellule de la première occurrence de  » Tamesha « .

Et à la fin, INDEX renverra le texte en utilisant ce numéro de cellule.

14. Créer un lien hypertexte

Maintenant, disons qu’en plus de rechercher une valeur, vous souhaitez également créer un lien hypertexte pour cette valeur. De cette façon, vous pouvez accéder rapidement à la cellule où se trouve votre recherche dans la colonne.

Par exemple, dans le tableau ci-dessous, vous devez obtenir l’âge d’une personne. Et, si vous créez un lien hypertexte pour cette valeur, vous pouvez facilement accéder à la cellule où se trouve cette valeur.

Et, pour cela, nous devons utiliser HYPERLINK + Cell avec INDEX et MATCH et la formule sera :

Voici comment cette formule fonctionne

Séparons cette formule en plusieurs parties pour mieux la comprendre.

• Tout d’abord, vous avez utilisé index et match dans la fonction cell. Et, lorsque vous utilisez les deux fonctions dans la fonction de cellule, vous obtenez une référence de cellule de la valeur correspondante au lieu de la valeur correspondante.
• Deuxièmement , vous avez concaténé « # » avec la référence de cellule.
• Troisièmement , vous avez de nouveau utilisé index et match pour obtenir la valeur correspondante à utiliser comme texte de lien. De cette façon, vous avez la valeur correspondante ainsi que le lien vers la cellule où se trouve cette valeur.