Pour ceux d'entre vous qui connaissent bien Excel , vous connaissez probablement très bien la fonction RECHERCHEV^(VLOOKUP) . La fonction VLOOKUP est utilisée pour trouver une valeur dans une cellule différente en fonction d'un texte correspondant dans la même ligne.

Si vous êtes encore novice dans la fonction VLOOKUP , vous pouvez consulter mon article précédent sur l'utilisation de VLOOKUP dans Excel^{(how to use VLOOKUP in Excel)} .

Aussi puissant soit-il, VLOOKUP a une limitation sur la façon dont la table de référence correspondante doit être structurée pour que la formule fonctionne.

Cet article vous montrera la limitation où VLOOKUP ne peut pas être utilisé et présentera une autre fonction dans Excel appelée INDEX-MATCH qui peut résoudre le problème.

INDEX MATCH Exemple Excel

En utilisant l' exemple de feuille de calcul Excel suivant , nous avons une liste de noms de propriétaires de voitures et le nom de la voiture. Dans cet exemple, nous allons essayer de saisir l'  ID^{(Car ID)} de voiture en fonction du modèle^{(Car Model)} de voiture répertorié sous plusieurs propriétaires, comme indiqué ci-dessous :

Sur une feuille séparée appelée CarType , nous avons une simple base de données de voitures avec l'  ID , le modèle de voiture^{(Car Model)} et la couleur^(Color) .

Avec cette configuration de table, la  fonction RECHERCHEV^(VLOOKUP) ne peut fonctionner que si les données que nous voulons récupérer se trouvent dans la colonne à droite de ce que nous essayons de faire correspondre ( champ Modèle de voiture^{(Car Model )} ).

En d'autres termes, avec cette structure de table, puisque nous essayons de la faire correspondre en fonction du modèle de voiture^{(Car Model)} , la seule information que nous pouvons obtenir est la couleur^{(Color )} (pas l' ID car la  colonne  ID est située à gauche de la colonne Modèle de voiture .)^{(Car Model )}

En effet, avec VLOOKUP , la valeur de recherche doit apparaître dans la première colonne et les colonnes de recherche doivent être à droite. Aucune de ces conditions n'est remplie dans notre exemple.

La bonne nouvelle est qu'INDEX-MATCH^{(INDEX-MATCH)}  pourra nous aider à y parvenir. En pratique, il s'agit en fait de combiner deux fonctions Excel qui peuvent fonctionner individuellement : la fonction INDEX et la fonction MATCH .

Cependant, pour les besoins de cet article, nous ne parlerons que de la combinaison des deux dans le but de répliquer la fonction de VLOOKUP .

La formule peut sembler un peu longue et intimidante au premier abord. Cependant, une fois que vous l'aurez utilisé plusieurs fois, vous apprendrez la syntaxe par cœur.

Voici la formule complète dans notre exemple :

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Voici la répartition pour chaque section

=INDEX( - Le “=” indique le début de la formule dans la cellule et INDEX est la première partie de la fonction Excel que nous utilisons.

CarType!$A$2:$A$5 – les colonnes de la feuille CarType  où se trouvent les données que nous aimerions récupérer. Dans cet exemple, l' ID  de chaque modèle de voiture.^{(Car Model.)}

MATCH( – La deuxième partie de la fonction Excel que nous utilisons.

B4 - La cellule contenant le texte de recherche que nous utilisons ( Modèle de voiture^{(Car Model)} ) .

CarType!$B$2:$B$5 – Les colonnes de la feuille CarType   avec les données que nous utiliserons pour faire correspondre le texte de recherche.

0)) – Pour indiquer que le texte de recherche doit correspondre exactement au texte de la colonne correspondante (c'est-à-dire CarType!$B$2:$B$5 ). Si la correspondance exacte n'est pas trouvée, la formule renvoie #N/A .

Remarque : n'oubliez pas la double parenthèse fermante à la fin de cette fonction "))" et les virgules entre les arguments.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Personnellement, je me suis éloigné de VLOOKUP et j'utilise maintenant INDEX-MATCH car il est capable de faire plus que VLOOKUP .

Les fonctions INDEX-MATCH ont également d'autres avantages par rapport à VLOOKUP :

1. Calculs plus rapides^{(Faster Calculations)}

Lorsque nous travaillons avec de grands ensembles de données où le calcul lui-même peut prendre beaucoup de temps en raison de nombreuses fonctions VLOOKUP , vous constaterez qu'une fois que vous aurez remplacé toutes ces formules par INDEX-MATCH , le calcul global sera calculé plus rapidement.

1. Pas besoin de compter les colonnes relatives^{(No Need to Count Relative Columns)}

Si notre table de référence contient le texte clé que nous voulons rechercher dans la colonne C et que les données que nous devons obtenir se trouvent dans la colonne AQ , nous devrons savoir/compter le nombre de colonnes entre la colonne C et la colonne AQ lors de l'utilisation de VLOOKUP^(VLOOKUP) .

Avec les  fonctions INDEX-MATCH , nous pouvons sélectionner directement la colonne d'index (c'est-à-dire la colonne AQ) où nous devons obtenir les données et sélectionner la colonne à faire correspondre (c'est-à-dire la colonne C).

1. Ça a l'air plus compliqué^{(It Looks More Complicated)}

VLOOKUP est assez courant de nos jours, mais peu de gens savent utiliser les fonctions INDEX-MATCH ensemble.

La chaîne plus longue dans la fonction INDEX-MATCH vous aide à ressembler à un expert dans la gestion des fonctions Excel complexes et avancées . Profitez!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are well-versed in Excel, you are most likely very fаmiliar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Comment corriger les erreurs #N/A dans les formules Excel telles que VLOOKUP

• Comment supprimer des lignes vides dans Excel

• Comment utiliser la fonctionnalité Speak Cells d'Excel

• Comment insérer une feuille de calcul Excel dans un document Word

• Comment utiliser l'analyse What-If d'Excel

• Utilisation de l'outil de recherche d'objectifs d'analyse What-If d'Excel

• Pourquoi devriez-vous utiliser des plages nommées dans Excel

• Comment fusionner des cellules, des colonnes et des lignes dans Excel

• Comment insérer CSV ou TSV dans une feuille de calcul Excel

• Comment fusionner des données dans plusieurs fichiers Excel

• Comment créer plusieurs listes déroulantes liées dans Excel

• Tri de base des données à une colonne et à plusieurs colonnes dans les feuilles de calcul Excel

• Connecter Excel à MySQL

• Comment partager un fichier Excel pour une collaboration facile

• 10 trucs et astuces Excel pour 2019

• Comment créer un tableau croisé dynamique simple dans Excel

• Comment créer une macro ou un script VBA dans Excel

• Comment utiliser les instructions If et If imbriquées dans Excel

• Comment tracer les personnes à charge dans Excel

• Comment alphabétiser dans Excel