Correspondance d'index dans Excel : Une meilleure façon de rechercher des données

INDEX MATCHLa combinaison des deux fonctions Excel, « INDEX() » et « MATCH() », constitue une alternative à la fonction « VLOOKUP() » couramment utilisée.

Dans ce guide, je vais vous expliquer comment fonctionnent individuellement et conjointement les services INDEX() et MATCH(), comment effectuer des recherches verticales et horizontales, et vous présenter les meilleures pratiques pour utiliser INDEX MATCH à l'aide d'exemples concrets.

Qu'est-ce que la fonction INDEX MATCH dans Excel ?

INDEX MATCH est une manière abrégée de désigner l'imbrication des deux fonctions. Cela équivaut à l'idée d'INDEX(MATCH()). Voyons comment ces deux éléments fonctionnent individuellement et ensemble.

Fonction INDEX() dans Excel

La fonction INDEX() dans Excel renvoie une valeur ou une référence à une valeur dans une plage ou un tableau. Vous pouvez l'utiliser de deux manières : sous forme de tableau et sous forme de référence.

Forme de tableau

Vous pouvez utiliser la forme tableau lorsque vous travaillez avec un tableau ou une matrice fixe. Dans ce format, INDEX() extrait une valeur en fonction du numéro de ligne et de colonne que vous spécifiez. Sa syntaxe est la suivante :

INDEX(array, row_num, [column_num])

Comprenons ces arguments :

• array (obligatoire) est la plage ou le tableau de valeurs avec lequel vous travaillez.

• row_num (obligatoire) est le numéro de la ligne à partir de laquelle une valeur doit être renvoyée.

• column_num (facultatif) est le numéro de la colonne à partir de laquelle une valeur doit être renvoyée.

Il est nécessaire de spécifier au moins l'un des deux éléments suivants : row_num ou column_num afin qu'Excel puisse localiser la valeur.

Cependant, voici quelques remarques importantes à garder à l'esprit lorsque vous utilisez cette syntaxe :

• Si le tableau ne comporte qu'une seule ligne ou colonne, vous pouvez ignorer les lignes « row_num » ou « column_num ».

• Si row_num et column_num sont tous deux inclus, INDEX renvoie la valeur où ils se croisent.

• Si vous définissez row_num ou column_num sur 0, Excel renvoie la colonne ou la ligne entière (vous devrez la saisir sous forme de formule matricielle pour voir le résultat).

Formulaire de référence

Vous pouvez utiliser le formulaire de référence lorsque vous travaillez avec plusieurs plages et que vous devez préciser celle que vous souhaitez consulter.

Sa syntaxe est la suivante :

INDEX(reference, row_num, [column_num], [area_num])

Comprenons ces arguments :

• reference (obligatoire) est utilisé pour faire référence à une ou plusieurs plages de cellules.

• row_num (obligatoire) est le numéro de ligne dans la zone sélectionnée.

• column_num (facultatif) correspond au numéro de colonne dans la zone sélectionnée.

• area_num (facultatif) indique à Excel quelle plage utiliser s'il y en a plusieurs.

Voici quelques points supplémentaires à retenir :

• Si l'area_num n'est pas spécifiée, Excel utilise par défaut la première plage.

• Il n'est pas possible de mélanger des plages provenant de différentes feuilles dans ce formulaire ; cela entraînerait une erreur.

• Si la ligne et la colonne sont omises, la fonction renvoie la zone complète spécifiée.

Fonction MATCH() dans Excel

Le MATCH() fonction vous aide à trouver la position d'une valeur dans une ligne ou une colonne. Au lieu de renvoyer la valeur elle-même, il vous indique où elle se trouve dans la liste.

Sa syntaxe est la suivante :

MATCH(lookup_value, lookup_array, [match_type])

Comprenons ces arguments :

• lookup_value (obligatoire) est la valeur que vous souhaitez trouver. Il peut s'agir d'un nombre, d'un texte, d'une valeur logique (VRAI/FAUX) ou d'une référence à une cellule.

• lookup_array (obligatoire) correspond à la plage de cellules dans laquelle Excel effectuera la recherche.

• match_type (facultatif) définit la manière dont Excel recherche la correspondance. La valeur par défaut est 1.. Ici, 1 trouve la plus grande valeur inférieure ou égale à lookup_value (ordre croissant requis). 0 trouve la correspondance exacte (ordre indifférent). -1 trouve la plus petite valeur supérieure ou égale à lookup_value (ordre décroissant requis).

La fonction « MATCH » n'est pas sensible à la casse, elle traite donc les lettres majuscules et minuscules de la même manière. Si vous travaillez avec du texte et souhaitez utiliser des caractères génériques tels que * (n'importe quel nombre de caractères) ou ? (un seul caractère), veuillez vous assurer que l'option « match_type » (Autoriser les caractères génériques) est définie sur « 0 » (Autoriser les caractères génériques). Car c'est le seul mode qui prend en charge la recherche avec caractères génériques.

Comment les fonctions INDEX() et MATCH() fonctionnent-elles ensemble dans Excel ?

L'une des utilisations les plus courantes de l'MATCH() est de l'associer à la fonction INDEX(). Alors que la fonction « MATCH() » permet de déterminer la position d'une valeur, la fonction « INDEX() » récupère la valeur à cette position. 

En imbriquant l'MATCH() dans l'INDEX(), vous obtenez une formule dynamique et résiliente lorsque les colonnes ou les lignes se déplacent au fil du temps. 

Par exemple, j'ai un échantillon de données. Et si je souhaite déterminer ce qui se trouve dans la même ligne dela colonne B d' s que celui de Banana dans la colonne A, je peux utiliser INDEX MATCH ensemble comme suit :

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))

Ici, MATCH("Banana", A2:A5, 0) trouve la position de Banana dans la colonne A et INDEX(B2:B5, ...) renvoie la valeur de la même ligne dans la colonne B.

INDEX MATCH par rapport à RECHERCHEV() : Lequel choisir ?

INDEX MATCH peut effectuer des tâches que VLOOKUP() ne peut pas réaliser. Cependant, le choix entre ces deux options dépend du niveau de flexibilité et de performance que vous attendez de vos feuilles Excel. Comparons-les.

Flexibilité des colonnes et intégrité des données

L'un des principaux inconvénients de l'VLOOKUP() est sa dépendancesur un nombre fixe de colonnes. Si vous insérez ou déplacez des colonnes, votre formule peut être endommagée.

Prenons l'exemple suivant :

Si une nouvelle colonne est ajoutée entre colonne A et colonne B, la troisième colonne se déplace et la formule renvoie des résultats incorrects, comme vous pouvez le constater dans l'image ci-dessous.

=VLOOKUP("Apple", A2:C5, 2, FALSE)

INDEX MATCH évite ce problème en travaillant avec des plages définies plutôt qu'avec des positions de colonnes codées en dur :

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))

Même si les colonnes sont réorganisées, cette formule reste efficace, ce qui la rend plus fiable et plus facile à maintenir. Ici, une colonne est insérée avant la colonne B ; la formule est mise à jour automatiquement et les résultats restent corrects. C'est la magie de l'INDEX MATCH.

Direction de recherche et polyvalence

VLOOKUP() La recherche ne peut s'effectuer que de gauche à droite. Cela signifie que votre colonne de recherche doit toujours être la première de votre plage. Si la valeur que vous recherchez se trouve à gauche de la colonne de recherche, VLOOKUP() ne pourra pas la trouver.

Cependant, si nous recherchons le nom du fruit pour trouver sa couleur à droite, cette formule fonctionnera. Par exemple, si nous souhaitons trouver la couleur d'une banane dans notre liste, le résultat obtenu est exact : 

Avec l'INDEX MATCH, cette limitation n'existe pas. Vous pouvez effectuer une recherche dans toutes les directions : à gauche, à droite, vers le haut ou vers le bas. Il prend également en charge les recherches horizontales pour vous offrir davantage de contrôle sur l'organisation de vos données. 

Appliquons l'INDEX MATCH à cet exemple et voyons comment il permet les recherches à gauche et à droite.

Pour une recherche à gauche, vous pouvez utiliser la formule suivante, qui vous donnera un résultat précis : 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))

Pour effectuer la recherche appropriée, veuillez utiliser la formule suivante, qui vous donnera le résultat correct : 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))

Performances et efficacité dans Excel

VLOOKUP() analyse l'ensemble du tableau pour trouver une correspondance et récupérer une valeur en une seule fois. Cependant, INDEX MATCH divise la tâche : MATCH() recherche la ligne ou la colonne, et INDEX() récupère la valeur. 

Cela permet un fonctionnement plus rapide et une utilisation plus efficace de la mémoire, en particulier dans les classeurs complexes ou volumineux.

Voici un tableau qui vous permettra de mieux comprendre quelles fonctionnalités sont prises en charge par chaque fonction :

Quand utiliser INDEX MATCH

Vous pouvez utiliser INDEX MATCH lorsque :

• Vous travaillez avec de grands ensembles de données où la rapidité est essentielle.
• Votre modèle est dynamique, avec des colonnes qui peuvent être ajoutées ou réorganisées.
• Vous devez rechercher les valeurs à gauche ou dans les plages horizontales.

Si vos fichiers Excel sont petits et simples, VLOOKUP() peut encore faire l'affaire. Toutefois, pour une modélisation et un reporting avancés, INDEX MATCH constitue une option plus sûre.

Comment utiliser INDEX MATCH étape par étape

Supposons que vous ayez deux colonnes : Fruits et ventes. Vous souhaitez déterminer la valeur des ventes pour « Apple ».

Pour ce faire, saisissez la formule combinée d'INDEX MATCH, puis appuyez sur la touche Entrée:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Ici, MATCH("Apple", A2:A6, 0) trouve la position de « Apple » dans la listeFruits . Et INDEX(B2:B6, ...) utilise cette position pour renvoyer la valeur des ventes correspondante à partir de la colonneVentes de l' .

Vous pouvez constater que la formule renvoie 12 000, qui correspond à la valeur des ventes d'Apple. Voilà donc votre résultat.

Éviter les erreurs INDEX MATCH

Bien que l'INDEX MATCH soit une bonne combinaison, elle peut néanmoins générer des erreurs si elle n'est pas configurée correctement. Voici comment résoudre les problèmes les plus courants et éviter les erreurs susceptibles de perturber vos formules.

Erreurs #N/A et comment les résoudre

#N/A L'erreur « Valeur inconnue » signifie qu'Excel n'a pas trouvé la valeur que vous recherchez. Certaines causes courantes peuvent expliquer ce phénomène :

• Les fautes de frappe, les espaces supplémentaires ou les incohérences dans le texte (par exemple, « pomme » au lieu de « pommes ») peuvent entraîner des erreurs dans la recherche et la classification des documents. "Apple")

• Aucune valeur dans la plage de recherche

• match_type incorrecte, par exemple 1 au lieu de 0 pour une correspondance exacte.

• Caractères cachés (tels que les espaces insécables) dans les données

Il existe donc deux solutions pour résoudre ce problème :

Utilisez TRIM() pour supprimer les espaces avant et après :

=MATCH(TRIM("Apple"), A2:A6, 0)

Ajoutez IFERROR() pour afficher une valeur de remplacement à la place d'une erreur :

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")

Erreurs #REF ! dans les formules INDEX MATCH

L'erreur « #REF! » indique généralement une référence incorrecte, souvent due à l'un des éléments suivants :

• Une incompatibilité entre le nombre de lignes dans vos plages INDEX() et MATCH()

• Lignes ou colonnes supprimées utilisées dans votre formule

• Copier et coller des formules sans mettre à jour les références de cellules

Cependant, il existe des solutions simples pour remédier à ce problème : 

• Veuillez vous assurer que les plages d'INDEX() s et d'MATCH() s sont identiques :

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

• Verrouillez les plages à l'aide de références absolues (telles que $A$2:$A$6) afin de garantir la stabilité des formules lors du copier-coller.

• Évitez de supprimer les cellules sur lesquelles reposent des formules.

Performances lentes et ensembles de données volumineux

Si vous travaillez avec des milliers de lignes, les formules d'INDEX MATCH non optimisées peuvent ralentir Excel.

Voici comment vous pouvez l'optimiser :

• Limitez les plages de recherche au lieu de référencer des colonnes entières :

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))

• Évitez les fonctions volatiles telles que OFFSET(), INDIRECT()et NOW() à proximité de vos recherches.

• Utilisez des tableaux Excel ou des plages nommées pour organiser vos données et améliorer les performances.

Gestion des erreurs de débordement dans Excel 365

Excel 365 introduit les tableaux dynamiques, qui peuvent « déborder » dans les cellules adjacentes. Si ce processus rencontre un problème, vous obtiendrez une erreur erreur #SPILL!.

Certaines causes courantes peuvent être les suivantes :

• Les cellules adjacentes ne sont pas vides.
• La formule renvoie plusieurs valeurs dans une plage de cellules bloquées ou fusionnées.
• Vous utilisez un tableau dynamique alors qu'une seule valeur est nécessaire.

Cependant, ces problèmes peuvent être résolus. Voici comment vous pouvez les surmonter :

• Veuillez effacer les cellules situées sous ou à côté de la formule.
• Désolidarisez toutes les cellules de la zone de débordement.
• Si vous ne souhaitez obtenir qu'une seule valeur, forcez un résultat unique avec l'@:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Dernières réflexions

Si vous utilisez actuellement VLOOKUP(),, il est temps de passer à une solution plusstable. Vous pouvez commencer par quelques recherches simples à l'aide de la commande « INDEX MATCH » dans une copie de votre fichier de travail. Essayez de remplacer la nouvelle formule lorsque les modifications de colonne provoquent des erreurs ou lorsque la structure de vos données exige une certaine flexibilité. Une fois que vous aurez constaté la facilité avec laquelle il traite ces cas, il vous sera difficile de revenir en arrière.