Excel-VLOOKUP vs. INDEX/MATCH-Quel est le meilleur?



indexing (1)

Je comprends comment utiliser chaque méthode: VLOOKUP (ou HLOOKUP ) ou INDEX / MATCH .

Je cherche des différences entre eux non pas en termes de préférence personnelle, mais principalement dans les domaines suivants:

  1. Y a-t-il quelque chose qu'une méthode peut faire que l'autre ne peut pas?

  2. Lequel est le plus efficace en général (ou dépend-il de la situation)?

  3. Tout autre avantage / inconvénient à utiliser une méthode par rapport à l'autre

NOTE : Je réponds à ma propre question ici, mais je cherche à savoir si quelqu'un d'autre a d'autres idées auxquelles je n'avais pas pensé.


Je préfère utiliser INDEX / MATCH dans pratiquement toutes les situations, car il est beaucoup plus flexible et peut être beaucoup plus efficace en fonction de la taille de la table de consultation.

Le seul moment où je peux vraiment justifier l'utilisation de VLOOKUP concerne des tables très simples où le numéro d'index de colonne est dynamique, bien que même dans ce cas, INDEX / MATCH soit également viable.

Je vais donner quelques exemples spécifiques ci-dessous pour illustrer les différences détaillées entre les deux méthodes.

INDEX / MATCH peut chercher à gauche (ou n'importe où ailleurs)

C’est probablement l’un des avantages les plus évidents d’ INDEX / MATCH ainsi que l’un des plus gros inconvénients de VLOOKUP . VLOOKUP ne peut rechercher que vers la droite, INDEX / MATCH peut rechercher à partir de n’importe quelle plage, y compris différentes feuilles si nécessaire.

L'exemple ci-dessous ne peut pas être réalisé avec VLOOKUP .

INDEX / MATCH a le potentiel d’utiliser des plages de cellules plus petites (augmentant ainsi l’efficacité)

Considérons l'exemple ci-dessous. Cela peut être accompli avec l'une ou l'autre méthode.

Ces deux formules fonctionnent bien. Toutefois, comme la formule VLOOKUP contient une plage plus étendue que la formule INDEX / MATCH , elle est inutilement volatile.

Si une cellule de la plage B1:G4 change, la formule VLOOKUP doit recalculer (car B1:G4 situe dans la plage A1:H4 ), même si la modification d'une cellule dans B1:G4 n'affectera pas le résultat de la formule. Ce n'est pas un problème pour INDEX / MATCH car sa formule ne contient pas la plage B1:G4 .

Utiliser VLOOKUP avec col_index_number corrigé est dangereux

Le principal problème que je vois avec un numéro d'index de colonne fixe est qu'il ne sera pas mis à jour comme il se doit si des colonnes complètes sont insérées. Prenons l'exemple suivant:

Cette formule fonctionne correctement sauf si une colonne est insérée dans la table de recherche. Dans ce cas, la formule recherchera la valeur située à gauche de l'endroit où elle devrait l'être. Voir ci-dessous, résultat après l'insertion d'une colonne.

Cela peut réellement être atténué en utilisant VLOOKUP formule suivante VLOOKUP :

= VLOOKUP("s",A1:H4,COLUMN(H1)-COLUMN(A1)+1,FALSE)

Désormais, H1 passera automatiquement à I1 si une colonne est insérée, préservant ainsi la référence à la même colonne. Cependant, ceci est totalement inutile car INDEX / MATCH peut accomplir cela sans ce problème avec la formule ci-dessous.

= INDEX(H1:H4,MATCH("s",A1:A4,0))

Je me rends compte que ce scénario est peu probable, mais cela me gênait toujours que VLOOKUP par défaut en fonction d'un index de colonne fixe qui ne se met pas à jour automatiquement si des colonnes sont insérées. Pour moi, cela semble simplement rendre la fonction VLOOKUP plus fragile.

INDEX / MATCH peut tout aussi bien gérer les index de colonnes variables, mais une formule plus longue

Si le numéro d'index de la colonne est lui-même dynamique, c'est vraiment le seul cas où je pense que VLOOKUP simplifie un peu les choses, mais là encore, la variante INDEX / MATCH est tout aussi bonne, mais un peu plus déroutante. Voir ci-dessous des exemples.

INDEX / MATCH est plus efficace pour les recherches multiples

(merci à @jeffreyweir)

Si plusieurs valeurs de recherche sont nécessaires pour une seule valeur de correspondance, il est beaucoup plus efficace de disposer d'une cellule auxiliaire avec la valeur de correspondance. De cette façon, la correspondance ne doit être calculée qu'une seule fois, au lieu d'une pour chaque formule de recherche. Voir exemple ci-dessous.

Cette valeur de correspondance peut ensuite être utilisée pour renvoyer les valeurs de recherche appropriées. Voir l'exemple ci-dessous (la formule a été déplacée vers la droite).

Ce "fractionnement" manuel de la valeur de correspondance et des valeurs d'index n'est pas une option avec VLOOKUP car la valeur de correspondance est une variable "interne" dans VLOOKUP et est VLOOKUP .

INDEX / MATCH peut rechercher une plage, permettant une autre opération

Supposons par exemple que vous souhaitiez trouver une valeur maximale dans une colonne en fonction du nom de la colonne.

Vous pouvez d'abord utiliser MATCH pour trouver la colonne appropriée, puis INDEX pour renvoyer la plage de cette colonne entière, puis utiliser MAX pour rechercher le maximum de cette plage.

Voir l'exemple ci-dessous, la formule dans H4 recherche la valeur maximale du nom de colonne spécifié dans la cellule G4 . Cela ne peut pas être accompli en utilisant uniquement VLOOKUP .

En résumé

VLOOKUP mieux, VLOOKUP est aussi bon que INDEX / MATCH et VLOOKUP est VLOOKUP dans certaines situations, il est un peu moins déroutant. Et au pire, VLOOKUP est beaucoup plus dangereux et volatile que INDEX / MATCH .

Il convient également de noter que si vous souhaitez rechercher une plage au lieu d’une valeur unique, vous devez utiliser INDEX / MATCH . VLOOKUP ne peut pas être utilisé pour rechercher une plage.

Pour ces raisons, je préfère généralement INDEX / MATCH dans pratiquement toutes les situations.





vlookup