Pourquoi INDEX et MATCH sont meilleurs que VLOOKUP dans Excel

Nous avons précédemment expliqué aux débutants comment utiliser les fonctions de base de VLOOKUP (en anglais VLOOKUP, l'abréviation signifie "vertical lookup function"). Et les utilisateurs expérimentés ont vu plusieurs formules plus compliquées.

Et dans cet article, nous essaierons de donner des informations sur une autre méthode de travail avec la recherche verticale.

Vous vous demandez peut-être : « Pourquoi est-ce nécessaire ? ». Et cela est nécessaire pour afficher toutes les méthodes de recherche possibles. De plus, de nombreuses restrictions VLOOKUP empêchent souvent d'obtenir le résultat souhaité. À cet égard, INDEX( ) MATCH( ) est beaucoup plus fonctionnel et diversifié, et ils ont également moins de restrictions.

INDEX MATCH

Étant donné que le but de ce guide est de montrer à quel point cette fonctionnalité est bonne, nous Regardons les informations de base concernant les principes de son fonctionnement. Et nous montrerons des exemples, et examinerons également pourquoi, c'est mieux que VLOOKUP ().

Syntaxe et utilisation de la fonction INDEX

Cette fonction aide à trouver la valeur souhaitée parmi les zones de recherche spécifiées en fonction du numéro de colonne ou de ligne. syntaxe:

=INDEX(tableau, numéro de ligne, numéro de colonne) :

• tableau - la zone dans laquelle la recherche aura lieu ;
• numéro de ligne – le numéro de la ligne à rechercher dans le tableau spécifié. Si le numéro de ligne est inconnu, le numéro de colonne doit être spécifié ;
• numéro de colonne – le numéro de la colonne à trouver dans le tableau spécifié. Si la valeur est inconnue, un numéro de ligne est requis.

Un exemple de formule simple :

=INDICE(A1:S10,2,3)

La fonction recherchera dans la plage de A1 à C10. Les nombres indiquent la ligne (2) et la colonne (3) à partir desquelles afficher la valeur souhaitée. Le résultat sera la cellule C2.

Assez simple, non? Mais lorsque vous travaillez avec de vrais documents, il est peu probable que vous disposiez d'informations concernant les numéros de colonne ou les cellules. C'est à cela que sert la fonction MATCH().

Syntaxe et utilisation de la fonction MATCH

La fonction MATCH() recherche la valeur souhaitée et affiche son nombre approximatif dans la zone de recherche spécifiée.

La syntaxe searchpos() ressemble à ceci :

=MATCH(valeur à rechercher, tableau à rechercher, type de correspondance)

• valeur de recherche – le nombre ou le texte à trouver ;
• tableau recherché – la zone où la recherche aura lieu ;
• type de correspondance – spécifie s'il faut rechercher la valeur exacte ou les valeurs les plus proches :
  • 1 (ou aucune valeur spécifiée) – renvoie la plus grande valeur égale ou inférieure à la valeur spécifiée ;
  • 0 – affiche une correspondance exacte avec la valeur recherchée. Dans la combinaison INDEX() MATCH() vous aurez presque toujours besoin d'une correspondance exacte, nous écrivons donc 0 ;
  • -1 – Affiche la plus petite valeur supérieure ou égale à la valeur spécifiée dans la formule. Le tri s'effectue par ordre décroissant.

Par exemple, dans la gamme B1:B3 New York, Paris, Londres sont enregistrés. La formule ci-dessous affichera le chiffre 3 car Londres est troisième sur la liste :

=EXPOSER(Londres,B1:B3,0)

Comment travailler avec la fonction INDEX MATCH 

Vous avez probablement déjà commencé à comprendre le principe selon lequel se construit le travail conjoint de ces fonctions. Bref, alors INDEX() recherche la valeur souhaitée parmi les lignes et colonnes spécifiées. Et MATCH() affiche les numéros de ces valeurs :

=INDEX(colonne à partir de laquelle la valeur est renvoyée, MATCH(valeur à rechercher, colonne à rechercher, 0))

Vous avez encore du mal à comprendre comment cela fonctionne ? Peut-être qu'un exemple expliquera mieux. Supposons que vous disposiez d'une liste des capitales mondiales et de leur population :

Afin de connaître la taille de la population d'une certaine capitale, par exemple la capitale du Japon, nous utilisons la formule suivante :

=INDEX(C2:C10, CORRESPONDANCE(Japon, A2:A10,0))

Explication:

• La fonction MATCH() recherche la valeur - "Japan" dans le tableau A2: A10 et renvoie le nombre 3, car Japan est la troisième valeur de la liste. 
• Ce chiffre va ànuméro de ligne” dans la formule INDEX() et indique à la fonction d'imprimer une valeur à partir de cette ligne.

La formule ci-dessus devient donc la formule standard INDICE(C2:C10,3). La formule recherche des cellules C2 à C10 et renvoie les données de la troisième cellule de cette plage, c'est-à-dire C4, car le compte à rebours commence à partir de la deuxième ligne.

Vous ne voulez pas prescrire le nom de la ville dans la formule ? Ensuite, écrivez-le dans n'importe quelle cellule, dites F1, et utilisez-le comme référence dans la formule MATCH(). Et vous vous retrouvez avec une formule de recherche dynamique :

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Important! Nombre de lignes dans tableau INDEX() doit être le même que le nombre de lignes dans tableau considéré dans MATCH(), sinon vous obtiendrez un mauvais résultat.

Attendez une minute, pourquoi ne pas simplement utiliser la formule RECHERCHEV() ?

=RECHERCHEV(F1, A2:C10, 3, Faux)

 Quel est l'intérêt de perdre du temps à essayer de comprendre toutes ces complexités d'INDEX MATCH ?

Dans ce cas, peu importe la fonction à utiliser. Ceci est juste un exemple pour comprendre comment les fonctions INDEX() et MATCH() fonctionnent ensemble. D'autres exemples montreront de quoi ces fonctions sont capables dans des situations où RECHERCHEV est impuissante. 

INDEX MATCH ou VLOOKUP

Lors du choix de la formule de recherche à utiliser, beaucoup s'accordent à dire que INDEX() et MATCH() sont largement supérieurs à VLOOKUP. Cependant, de nombreuses personnes utilisent encore RECHERCHEV(). Premièrement, VLOOKUP() est plus simple, et deuxièmement, les utilisateurs ne comprennent pas pleinement tous les avantages de travailler avec INDEX() et MATCH(). Sans cette connaissance, personne n'acceptera de passer son temps à étudier un système complexe.

Voici les principaux avantages d'INDEX() et MATCH() par rapport à VLOOKUP() :

• Recherche de droite à gauche. VLOOKUP() ne peut pas rechercher de droite à gauche, donc les valeurs que vous recherchez doivent toujours se trouver dans les colonnes les plus à gauche du tableau. Mais INDEX() et MATCH() peuvent gérer cela sans problème. Cet article vous expliquera à quoi cela ressemble en pratique : comment trouver la valeur souhaitée sur le côté gauche.

1. Ajout ou retrait de colonnes en toute sécurité. La formule VLOOKUP() affiche des résultats incorrects lors de la suppression ou de l'ajout de colonnes car VLOOKUP() a besoin du numéro de colonne exact pour réussir. Naturellement, lorsque des colonnes sont ajoutées ou supprimées, leurs numéros changent également. 

Et dans les formules INDEX() et MATCH(), une plage de colonnes est spécifiée, pas des colonnes individuelles. Par conséquent, vous pouvez ajouter et supprimer des colonnes en toute sécurité sans avoir à mettre à jour la formule à chaque fois.

1. Aucune limite sur les volumes de recherche. Lorsque vous utilisez RECHERCHEV(), le nombre total de critères de recherche ne doit pas dépasser 255 caractères ou vous obtiendrez une #VALEUR ! Donc, si vos données contiennent un grand nombre de caractères, INDEX() et MATCH() sont la meilleure option.
2. Vitesse de traitement élevée. Si vos tables sont relativement petites, il est peu probable que vous remarquiez une différence. Mais, si la table contient des centaines ou des milliers de lignes et, par conséquent, il existe des centaines et des milliers de formules, INDEX () et MATCH () s'en sortiront beaucoup plus rapidement que VLOOKUP (). Le fait est qu'Excel ne traitera que les colonnes spécifiées dans la formule, au lieu de traiter l'intégralité du tableau. 

L'impact sur les performances de VLOOKUP() sera particulièrement visible si votre feuille de calcul contient un grand nombre de formules telles que VLOOKUP() et SUM(). Des vérifications séparées des fonctions VLOOKUP() sont nécessaires pour analyser chaque valeur dans un tableau. Excel doit donc traiter une énorme quantité d'informations, ce qui ralentit considérablement le travail.

Exemples de formules 

Nous avons déjà compris l'utilité de ces fonctions, nous pouvons donc passer à la partie la plus intéressante : l'application des connaissances dans la pratique.

Formule pour rechercher de droite à gauche

Comme déjà mentionné, VLOOKUP ne peut pas effectuer cette forme de recherche. Ainsi, si les valeurs souhaitées ne se trouvent pas dans la colonne la plus à gauche, VLOOKUP() ne produira pas de résultat. Les fonctions INDEX() et MATCH() sont plus polyvalentes et l'emplacement des valeurs ne joue pas un grand rôle pour leur fonctionnement.

Par exemple, nous allons ajouter une colonne de classement sur le côté gauche de notre tableau et essayer de déterminer quel rang en termes de population occupe la capitale de Notre Pays.

Dans la cellule G1, nous écrivons la valeur à trouver, puis utilisons la formule suivante pour rechercher dans la plage C1:C10 et renvoyons la valeur correspondante de A2:A10 :

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Rapide. Si vous prévoyez d'utiliser cette formule pour plusieurs cellules, assurez-vous de fixer les plages à l'aide d'un adressage absolu (par exemple, 2 $A : 10 $A et 2 $C : 4 10 $C).

INDEX PLUS EXPOSÉ PLUS EXPOSÉ  pour rechercher dans les colonnes et les lignes

Dans les exemples ci-dessus, nous avons utilisé ces fonctions en remplacement de RECHERCHEV() pour renvoyer des valeurs à partir d'une plage prédéfinie de lignes. Mais que se passe-t-il si vous devez effectuer une recherche matricielle ou bilatérale ?

Cela semble compliqué, mais la formule pour de tels calculs est similaire à la formule standard INDEX() MATCH(), avec une seule différence : la formule MATCH() doit être utilisée deux fois. La première fois pour obtenir le numéro de ligne et la deuxième fois pour obtenir le numéro de colonne :

=INDEX(tableau, MATCH(valeur de recherche verticale, colonne de recherche, 0), MATCH(valeur de recherche horizontale, ligne de recherche, 0))

Regardons le tableau ci-dessous et essayons de faire une formule INDEX() EXPRESS() EXPRESS() afin d'afficher les données démographiques d'un pays spécifique pour une année sélectionnée.

Le pays cible se trouve dans la cellule G1 (recherche verticale) et l'année cible se trouve dans la cellule G2 (recherche horizontale). La formule ressemblera à ceci :

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Comment fonctionne cette formule

Comme pour toutes les autres formules complexes, elles sont plus faciles à comprendre en les décomposant en équations individuelles. Et puis vous pouvez comprendre ce que fait chaque fonction individuelle :

• CORRESPONDANCE(G1,A2:A11,0) – cherche une valeur (G1) dans la plage A2:A11 et affiche le numéro de cette valeur, dans notre cas c'est 2 ;
• RECHERCHE(G2,B1:D1,0) – recherche une valeur (G2) dans la plage B1:D1. Dans ce cas, le résultat était 3.

Les numéros de ligne et de colonne trouvés sont envoyés à la valeur correspondante dans la formule INDEX() :

=INDICE(B2:D11,2,3)

En conséquence, nous avons une valeur qui se trouve dans une cellule à l'intersection de 2 lignes et 3 colonnes dans la plage B2:D11. Et la formule affiche la valeur souhaitée, qui se trouve dans la cellule D3.

Recherche par conditions multiples avec INDEX et MATCH

Si vous avez lu notre guide sur VLOOKUP(), vous avez probablement essayé plusieurs formules de recherche. Mais cette méthode de recherche a une limitation importante - la nécessité d'ajouter une colonne auxiliaire.

Mais la bonne nouvelle est que Avec INDEX() et MATCH(), vous pouvez rechercher plusieurs conditions sans avoir à modifier ou à modifier votre feuille de calcul.

Voici la formule générale de recherche multi-conditions pour INDEX() MATCH() :

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

La note: cette formule doit être utilisée avec le raccourci clavier CTRL+MAJ+ENTRÉE.

Supposons que vous ayez besoin de trouver la valeur que vous recherchez en fonction de 2 conditions : Acheteur и Produit.

Cela nécessite la formule suivante :

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Dans cette formule, C2:C10 est la plage dans laquelle la recherche aura lieu, F1 - cette condition, A2:A10 — est la plage de comparaison de la condition, F2 – condition 2, V2: V10 – plage de comparaison de la condition 2.

N'oubliez pas d'appuyer sur la combinaison à la fin du travail avec la formule CTRL + MAJ + ENTRÉE – Excel fermera automatiquement la formule avec des accolades, comme indiqué dans l'exemple :

Si vous ne souhaitez pas utiliser une formule matricielle pour votre travail, ajoutez un autre INDEX() à la formule et appuyez sur ENTREE, cela ressemblera à l'exemple :

Comment fonctionnent ces formules

Cette formule fonctionne de la même manière que la formule standard INDEX() MATCH(). Pour rechercher plusieurs conditions, il vous suffit de créer plusieurs conditions False et True qui représentent les conditions individuelles correctes et incorrectes. Et puis ces conditions s'appliquent à tous les éléments correspondants du tableau. La formule convertit les arguments False et True en 0 et 1, respectivement, et génère un tableau où 1 correspond aux valeurs correspondantes trouvées dans la chaîne. MATCH() trouvera la première valeur qui correspond à 1 et la transmettra à la formule INDEX(). Et celui-ci, à son tour, renverra la valeur déjà souhaitée dans la ligne spécifiée à partir de la colonne souhaitée.

Une formule sans tableau dépend de la capacité d'INDEX() à les gérer par elle-même. Le deuxième INDEX() de la formule correspond à faux (0), il transmet donc tout le tableau avec ces valeurs à la formule MATCH(). 

C'est une explication assez longue de la logique derrière cette formule. Pour plus d'informations lire l'article "INDEX MATCH avec plusieurs conditions».

MOYENNE, MAX et MIN dans INDEX et MATCH

Excel a ses propres fonctions spéciales pour trouver des moyennes, des maximums et des minimums. Mais que se passe-t-il si vous souhaitez obtenir des données de la cellule associée à ces valeurs ? Dans ce cas AVERAGE, MAX et MIN doivent être utilisés conjointement avec INDEX et MATCH.

INDEX MATCH et MAX

Pour trouver la plus grande valeur dans la colonne D et l'afficher dans la colonne C, utilisez la formule : 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH et MIN

Pour trouver la plus petite valeur dans la colonne D et l'afficher dans la colonne C, utilisez la formule suivante :

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

RECHERCHE INDEX et SERPENT

Pour trouver la valeur moyenne dans la colonne D et afficher cette valeur dans C :

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Selon la manière dont vos données sont écrites, le troisième argument de MATCH() est 1, 0 ou -1 :

• si les colonnes sont triées par ordre croissant, définissez 1 (alors la formule calculera la valeur maximale, qui est inférieure ou égale à la valeur moyenne) ;
• si le tri est décroissant, alors -1 (la formule affichera la valeur minimale supérieure ou égale à la moyenne) ;
• si le tableau de recherche contient une valeur exactement égale à la moyenne, définissez-la sur 0. 

 Dans notre exemple, la population est triée par ordre décroissant, nous mettons donc -1. Et le résultat est Tokyo, puisque la valeur de la population (13,189 000) est la plus proche de la valeur moyenne (XNUMX).

VLOOKUP() peut également effectuer de tels calculs, mais uniquement sous forme de formule matricielle : VLOOKUP avec AVERAGE, MIN et MAX.

INDEX MATCH et ESND/IFERROR

Vous avez probablement déjà remarqué que si la formule ne trouve pas la valeur souhaitée, elle renvoie une erreur #N / A. Vous pouvez remplacer le message d'erreur standard par quelque chose de plus informatif. Par exemple, définissez l'argument dans la formule Au 13ème:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Avec cette formule, si vous entrez des données qui ne sont pas dans le tableau, le formulaire vous donnera le message spécifié.

Si vous voulez attraper toutes les erreurs, alors à l'exception de Au 13ème peut être utilisé IFERREUR:

=SIERREUR(INDEX(C2:C10,EQUIV(F1,A2:A10,0)), "Quelque chose s'est mal passé !")

Mais rappelez-vous que masquer les erreurs de cette manière n'est pas une bonne idée, car les erreurs standard signalent des violations dans la formule.

Nous espérons que vous avez trouvé notre guide d'utilisation de la fonction INDEX MATCH() utile.