Bien que la fonction VLOOKUP soit utile pour rechercher des valeurs dans Excel, elle a ses limites. Avec une combinaison des fonctions INDEX et MATCH à la place, vous pouvez rechercher des valeurs dans n’importe quel emplacement ou direction dans votre feuille de calcul.
La fonction INDEX renvoie une valeur basée sur un emplacement que vous entrez dans la formule tandis que MATCH fait l’inverse et renvoie un emplacement basé sur la valeur que vous entrez. Lorsque vous combinez ces fonctions, vous pouvez trouver n’importe quel numéro ou texte dont vous avez besoin.
VLOOKUP Versus INDEX et MATCH
La différence entre ces fonctions et VLOOKUP est que VLOOKUP trouve des valeurs de gauche à droite. D’où le nom de la fonction ; VLOOKUP effectue une recherche verticale.
Microsoft explique le mieux le fonctionnement de VLOOKUP :
L’utilisation de VLOOKUP comporte certaines limitations : la fonction VLOOKUP ne peut rechercher une valeur que de gauche à droite. Cela signifie que la colonne contenant la valeur que vous recherchez doit toujours être située à gauche de la colonne contenant la valeur de retour.
Microsoft poursuit en disant que si votre feuille n’est pas configurée de manière à ce que VLOOKUP puisse vous aider à trouver ce dont vous avez besoin, vous pouvez utiliser INDEX et MATCH à la place. Voyons donc comment utiliser INDEX et MATCH dans Excel.
Bases des fonctions INDEX et MATCH
Pour utiliser ces fonctions ensemble, il est important de comprendre leur objectif et leur structure.
La syntaxe pour INDEX sous forme de tableau est INDEX(array, row_number, column_number) avec les deux premiers arguments obligatoires et le troisième facultatif.
INDEX recherche une position et renvoie sa valeur. Pour trouver la valeur dans la quatrième ligne de la plage de cellules D2 à D8, vous devez saisir la formule suivante :
=INDEX(D2:D8,4)
Le résultat est 20 745 car il s’agit de la valeur en quatrième position de notre plage de cellules.
Pour plus de détails sur les formes matricielles et de référence d’INDEX ainsi que sur d’autres façons d’utiliser cette fonction, consultez notre tutoriel pour INDEX dans Excel.
La syntaxe de MATCH est MATCH(value, array, match_type) avec les deux premiers arguments obligatoires et le troisième facultatif.
MATCH recherche une valeur et renvoie sa position. Pour trouver la valeur dans la cellule G2 dans la plage A2 à A8, vous devez saisir la formule suivante :
=MATCH(G2,A2:A8)
Le résultat est 4 car la valeur de la cellule G2 est en quatrième position dans notre plage de cellules.
Pour plus de détails sur le match_type argument et d’autres façons d’utiliser cette fonction, consultez notre tutoriel pour MATCH dans Excel.
EN RELATION: Comment trouver la position d’une valeur avec MATCH dans Microsoft Excel
Comment utiliser INDEX et MATCH dans Excel
Maintenant que vous savez ce que fait chaque fonction et sa syntaxe, il est temps de mettre ce duo dynamique au travail. Ci-dessous, nous utiliserons les mêmes données que ci-dessus pour INDEX et MATCH individuellement.
Vous placerez la formule de la fonction MATCH dans la formule de la fonction INDEX à la place de la position à rechercher.
Pour trouver la valeur (ventes) en fonction de l’identifiant de l’emplacement, vous utiliseriez cette formule :
=INDEX(D2:D8,MATCH(G2,A2:A8))
Le résultat est 20 745. MATCH trouve la valeur dans la cellule G2 dans la plage A2 à A8 et la fournit à INDEX qui recherche le résultat dans les cellules D2 à D8.
Prenons un autre exemple. Nous voulons savoir dans quelle ville les ventes correspondent à un certain montant. En utilisant notre feuille, vous saisiriez cette formule :
=INDEX(B2:B8,MATCH(G5,D2:D8))
Le résultat est Houston. MATCH trouve la valeur dans la cellule G5 dans la plage D2 à D8 et la fournit à INDEX qui recherche le résultat dans les cellules B2 à B8.
Voici un exemple utilisant une valeur réelle au lieu d’une référence de cellule. Nous allons rechercher la valeur (ventes) pour une ville spécifique avec cette formule :
=INDEX(D2:D8,MATCH("Houston",B2:B8))
Dans la formule MATCH, nous avons remplacé la référence de cellule contenant la valeur de recherche par la valeur de recherche réelle de « Houston » de B2 à B8, ce qui nous donne le résultat 20 745 de D2 à D8.
Noter: Assurez-vous que lorsque vous utilisez la valeur réelle pour rechercher, plutôt qu’une référence de cellule, vous l’entourez de guillemets, comme indiqué ici.
Pour obtenir le même résultat en utilisant l’ID de lieu au lieu de la ville, nous changeons simplement la formule en ceci :
=INDEX(D2:D8,MATCH("2B",A2:A8))
Ici, nous avons modifié la formule MATCH pour rechercher « 2B » dans la plage de cellules A2 à A8 et fournir ce résultat à INDEX qui renvoie ensuite 20 745.
Les fonctions de base d’Excel comme celles qui vous aident à ajouter des nombres dans les cellules ou à entrer la date actuelle sont certainement utiles. Mais lorsque vous commencez à ajouter plus de données et à faire progresser vos besoins de saisie ou d’analyse de données, les fonctions de recherche telles que INDEX et MATCH dans Excel peuvent être très utiles.
EN RELATION: 12 fonctions Excel de base que tout le monde devrait connaître