Comment faire une correspondance d'indexation entre plusieurs critères dans Excel ?

Il y a quelques années, j'ai travaillé sur l'analyse d'une campagne de marketing dans le cadre de laquelle je devais comparer les performances des ventes dans plusieurs régions. Les données étaient réparties sur plusieurs feuilles Excel et je devais rassembler les chiffres de vente de produits spécifiques dans un seul rapport récapitulatif. J'ai d'abord essayé de rechercher et de copier manuellement les données, mais ce n'était pas aussi facile que je le pensais. Si une seule ligne est erronée, l'ensemble du rapport peut s'effondrer.

C'est alors que j'ai découvert INDEX MATCH().. Il m'a fallu quelques essais pour trouver la bonne formule, mais elle est devenue partie intégrante de ma routine une fois que j'ai vu avec quelle facilité elle pouvait localiser et extraire les chiffres exacts dont j'avais besoin. Avec seulement deux fonctions, je pouvais extraire exactement les données dont j'avais besoin, quelle que soit leur dispersion dans les feuilles de calcul.

Dans cet article, je vais vous expliquer comment vous pouvez faire la même chose en utilisant les fonctions INDEX() et MATCH(). Il y a toujours plus à apprendre avec Excel. Si vous êtes débutant, je vous recommande vivement notre cours d'introduction à Excel. Si vous avez plus d'expérience, essayez notre cours Fonctions Excel avancées.

Remise à niveau sur l'INDEX MATCH

INDEX MATCH est est une façon abrégée de parler de la combinaison de deux fonctions Excel qui fonctionnent ensemble pour effectuer des recherches avancées. la combinaison de deux fonctions Excel qui fonctionnent ensemble pour effectuer des recherches avancées. Nous pourrions également nous référer à cette idée comme INDEX(MATCH()), mais je choisirai INDEX MATCH dans cet article. Voyons maintenant ce qu'il en est pour chacun d'entre eux :

La fonction INDEX() permet de récupérer la valeur d'une cellule en fonction de sa position dans un intervalle spécifié. Voici sa syntaxe :

=INDEX(array, row_num, [column_num])

Ici :

• array est la plage de cellules à partir de laquelle vous souhaitez extraire une valeur.

• row_num est le numéro de ligne du tableau à partir duquel une valeur doit être renvoyée.

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

La fonction MATCH() permet d'identifier la position relative d'une valeur à l'intérieur d'une plage. Sa syntaxe est la suivante :

=MATCH(lookup_value, lookup_array, [match_type])

Ici :

• lookup_value est la valeur que vous souhaitez trouver.

• lookup_array est l'intervalle dans lequel la fonction recherche la valeur.

match_type est facultatif. 1 (par défaut) recherche la valeur inférieure ou égale à lookup_value (le tableau doit être trié par ordre croissant). 0 trouve une correspondance exacte (le tableau n'a pas besoin d'être trié). -1 recherche la plus petite valeur supérieure ou égale à lookup_value (le tableau doit être trié par ordre décroissant).

Comment combiner INDEX() et MATCH() ?

En imbriquant MATCH() dans INDEX(), nous pouvons créer une recherche dynamique. Comprenons-le à l'aide d'un exemple : Supposons que vous souhaitiez trouver la position de David Wilson dans l'ensemble de données. Au lieu de coder en dur le numéro de ligne dans INDEX(), utilisez MATCH() pour le déterminer :

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

Dans la formule ci-dessus, MATCH("David Wilson", A2:A6, 0) renvoie 4, qui est la position de la ligne. Et INDEX(C2:C6, 4) récupère la valeur de la 4e ligne de la plage C2:C6, qui est Seattle.

Combinez INDEX() avec MATCH(). Image par l'auteur.

Pour rendre la situation encore plus dynamique, vous pouvez remplacer le David Wilson codé en dur par une référence de cellule. De cette façon, la formule s'ajuste automatiquement sur la base de la valeur figurant dans D4:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

Remplacez la valeur codée en dur dans la correspondance d'index. Image par l'auteur.

MATCH D'INDEX vs. VLOOKUP()

Maintenant que vous savez comment INDEX() et MATCH() fonctionnent individuellement et comment leur combinaison rend les recherches plus dynamiques, voyons pourquoi INDEX MATCH est un meilleur choix que VLOOKUP().

• Contrairement à VLOOKUP(), qui exige que la colonne de consultation soit à gauche, INDEX MATCH vous permet d'extraire des données de n'importe quelle colonne, quelle que soit sa position.

• INDEX MATCH ne traite que la plage de cellules requise, contrairement à VLOOKUP(), qui parcourt des tableaux entiers. 

• Les formules utilisant VLOOKUP() peuvent être interrompues si des colonnes sont insérées ou supprimées, car elles reposent sur des indices de colonne statiques. D'autre part, INDEX MATCH fait référence à des plages dynamiques pour s'assurer que vos formules restent intactes malgré les changements structurels de vos données.

• Avec INDEX MATCH,, il n'est pas nécessaire de compter manuellement le nombre de colonnes. Spécifiez la colonne de recherche et la colonne de retour, et vous avez terminé. 

MATCH D'INDEX avec critères multiples

Je dois souvent travailler sur des ensembles de données qui contiennent des entrées en double, et il est extrêmement difficile d'y trouver des valeurs. Mais j'utilise désormais INDEX MATCH parce qu'il gère ces scénarios très facilement, contrairement à d'autres formules de recherche standard. Laissez-moi vous expliquer étape par étape comment je l'utilise.

Configurer les données pour des critères multiples

Tout d'abord, créez votre ensemble de données et assurez-vous qu'il est bien organisé dans un tableau avec des en-têtes clairs pour chaque colonne. Chaque ligne doit représenter un enregistrement unique et chaque colonne doit contenir un attribut de données spécifique.

Par exemple, voici un échantillon de données :

Ensemble de données pour INDEX MATCH critères multiples. Image par l'auteur.

Ecrivez la formule pour les critères multiples

Une fois vos données correctement organisées, il est temps d'écrire la formule. La formule INDEX MATCH permet d'extraire une valeur d'une autre colonne en identifiant une ligne qui remplit plusieurs conditions. Cela se fait en combinant des tests logiques dans la fonction MATCH() et en l'intégrant dans la fonction INDEX().

Voici la syntaxe de base : 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Ici : 

• Return_range est l'intervalle à partir duquel la valeur sera renvoyée.

• Criteria1, Criteria2, … sont les conditions à remplir.

• Range1, Range2, … sont des plages correspondant aux critères.

Maintenant que nous disposons de données, examinons de plus près les deux méthodes pour répondre à notre question : comment utiliser INDEX MATCH avec plusieurs critères.

Utiliser des colonnes d'aide pour les critères complexes

Si votre ensemble de données comporte plusieurs conditions, utilisez des colonnes d'aide pour simplifier le processus. Il combinera toutes les conditions dans une seule colonne pour faciliter les recherches. Par exemple, j'utilise le même ensemble de données pour créer une colonne d'aide en combinant les colonnesPrénom et Salairede :

=A2&B2

Créez une colonne d'aide. Image par l'auteur.

Cette colonne d'aide simplifie ma formule INDEX MATCH. Au lieu d'écrire une formule de tableau complexe avec plusieurs conditions, j'ai référencé la colonne d'aide dans ma formule pour une approche plus simple :

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH avec colonne d'aide. Image par l'auteur.

Combinez plusieurs critères avec une formule de tableau

Si vous ne préférez pas les colonnes d'aide, vous pouvez utiliser des formules de tableau pour obtenir le même résultat. Ils vous permettent d'évaluer plusieurs critères directement dans la fonction MATCH(). Par exemple, voici comment je trouve Le salaire d'Alice dans le département desressources humaines :

Étape 1 : Écrivez la fonction MATCH() avec des conditions logiques :

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

Dans cette formule, 1 garantit que la fonction MATCH() recherche les lignes où toutes les conditions sont remplies. (F4=A2:A11) vérifie si la valeur de F4 correspond à une valeur de la plage A2:A11. (F5=B2:B11) vérifie si la valeur de F5 correspond à une valeur de la plage B2:B11. L'opérateur * agit comme une logique AND, en s'assurant que toutes les conditions sont remplies.

Étape 2 : Enveloppez la fonction MATCH() dans la fonction INDEX():

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Étape 3: Finaliser la formule. Si vous utilisez une version plus ancienne d'Excel, appuyez sur Ctrl+Shift+Enterpour en faire une formule de tableau. Dans les versions plus récentes, appuyez sur Enter.

Array INDEX MATCH avec plusieurs critères. Image par l'auteur.

Utilisations avancées de INDEX MATCH avec des critères multiples

Vous pouvez faire bien plus avec la fonction INDEX MATCH. Voyons comment : 

Utilisez INDEX MATCH avec des plages nommées et des plages dynamiques.

J'utilise lesplages nommées dans Excel pour définir des noms significatifs tels que results ou totalSales au lieu des références standard telles que A1:A10. De cette façon, il devient plus facile de gérer les formules sur différentes feuilles. 

Pour nommer une plage de cellules, sélectionnez les cellules et appuyez sur Ctrl + F3 (pour Windows) ou Cmd + F3 (pour Mac) pour ouvrir le gestionnaire de noms. Cliquez ensuite sur New, saisissez un nom et cliquez sur OK.

Nommez la plage. Image par l'auteur.

La seule différence entre une plage nommée et une plage dynamique est qu'une plage nommée se réfère à un groupe fixe de cellules, alors qu'une plage dynamique s'ajuste automatiquement lorsque des données sont ajoutées ou supprimées.

Pour définir une plage dynamique, sélectionnez les cellules. Dans l'onglet Formules, cliquez sur Gestionnaire de noms ou appuyez sur Ctrl + F3 pour ouvrir le Gestionnaire de nomsExcel et cliquez sur Nouveau. La boîte de dialogue New Name apparaît. Dans le champ Name , entrez le nom que vous souhaitez. Ensuite, dans lechamp Refers to , entrez la formule de la plage dynamique .

Définissez une plage dynamique. Image par l'auteur. 

Prenons maintenant un exemple : J'ai défini deux gammes dynamiques et une gamme statique :

• montant_total : =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list : =$A$2:INDEX($A:$A, COUNTA($A:$A))

• valeur_de_recherche : =$I$3

J'utilise maintenant ces fourchettes dans la formule INDEX MATCH:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

Vous pouvez voir que la formule devient beaucoup plus facile à comprendre avec des noms clairs.

Utilisez des plages dynamiques et nommées avec INDEX MATCH. Image par l'auteur.

MATCH INDEX imbriqué pour les recherches complexes

Outre le travail de base, vous pouvez utiliser les fonctions imbriquées de INDEX MATCH pour gérer des recherches complexes. Par exemple, je dispose d'un ensemble de données montrant les ventes par catégorie de produits dans différentes régions. 

Ensemble de données brutes. Image par l'auteur.

Je souhaite trouver des ventes de meubles dans l'est du pays. Mais pour ce faire, je dois faire correspondre à la fois la catégorie de produits (ligne) et la région (colonne), ce qu'un site INDEX MATCH de base ne peut pas faire. C'est pourquoi j'utilise ici la formule imbriquée INDEX MATCH suivante :

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Voici comment cela fonctionne : Le site INDEX() extrait une valeur de la plage B2:D4, mais il a besoin d'un numéro de ligne et d'un numéro de colonne pour savoir exactement où chercher. Le premier site MATCH(D6, A2:A4, 0) calcule donc le numéro de la ligne. Si D6contient Furniture, il cherche dans la colonne A2:A4 et le trouve dans la deuxième ligne.

Ensuite, MATCH(D7, B1:D1, 0) détermine le numéro de la colonne. Si D7 indique " Est", il parcourt B1:D1 et le trouve dans la deuxième colonne.

Une fois que INDEX() connaît la ligne et la colonne, il affiche les valeurs de sortie. Dans notre cas, les ventes de Furniture dans l'Est sont de 450.

Utilisez un INDEX MATCH imbriqué. Image par l'auteur.

J'utilise cette formule au lieu de chercher manuellement dans les lignes et les colonnes, car elle traite tout avec précision. 

Défis communs et conseils de dépannage

Lorsque j'ai commencé à utiliser INDEX MATCH,, j'ai rencontré plusieurs difficultés, et je ne veux pas que vous viviez les mêmes frustrations. Je vais donc vous présenter les défis les plus courants et vous montrer comment les surmonter.

Gestion des erreurs dans les formules INDEX MATCH

Des erreurs telles que #N/A et #VALUE! peuvent sembler frustrantes au départ, mais elles sont assez faciles à corriger. Voyons comment identifier la cause du problème et les étapes simples pour le résoudre.

L 'erreur #N/A se produit lorsque la fonction MATCH() ne trouve pas de valeur. Cela est dû au fait que la valeur de recherche n'existe pas dans le tableau de recherche ou que les données contiennent des espaces cachés. Par exemple, il m'est arrivé une fois de référencer la mauvaise colonne lors de l'extraction de Noms des employés:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

#N/A error in INDEX MATCH. Image par l'auteur.

Pour résoudre ces problèmes, confirmez l'existence de la valeur de référence dans le tableau et utilisez la fonction TRIM() pour nettoyer les espaces :

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

L'erreur #N/A a été corrigée dans INDEX MATCH. Image par l'auteur.

#VALUE! apparaît lorsque la formule n'est pas définie comme une formule de tableau. Par exemple, si j'utilise la fonction MATCH() et que j'inclus plus d'une plage, Excel la considère comme une formule de tableau. Cependant, s'il n'est pas configuré correctement, Excel affichera une erreur #VALUE!.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

#Erreur de valeur dans INDEX MATCH. Image par l'auteur.

Pour résoudre ce problème, appuyez sur Ctrl + Shift + Enter après avoir saisi la formule. De cette façon, Excel enveloppera la formule dans des accolades {}, indiquant qu'il s'agit maintenant d'une formule de tableau. Mais ne tapez pas les accolades manuellement, car cela casserait la formule.

#Erreur de valeur corrigée dans INDEX MATCH. Image par l'auteur.

Optimisez les performances avec les grands ensembles de données

Dans les grands ensembles de données, mes formules ralentissaient de temps en temps, ce qui m'obligeait à attendre que les calculs soient mis à jour. Si vous êtes confronté à des problèmes similaires, essayez ces conseils : 

• Limitez la plage de recherche : Limitez les gammes au strict nécessaire. Par exemple, au lieu de A:A, utilisez A1:A100 pour réduire le temps de calcul.

• Utilisez des colonnes d'aide : Précalculez des critères complexes avec des colonnes d'aide. Cela réduira la charge de calcul des formules de tableaux.

• Activer le mode de calcul manuel : Passez Excel en mode de calcul manuel pour éviter les recalculs constants. Après avoir effectué des modifications, appuyez sur F9 pour mettre à jour les formules manuellement.

• Évitez les fonctions volatiles : Minimisez l'utilisation de fonctions volatiles telles que NOW(), RAND(), et TODAY() en combinaison avec INDEX MATCH. Ces fonctions déclenchent des recalculs à chaque mise à jour du classeur.

Réflexions finales 

INDEX MATCH permettent de gagner du temps et de simplifier l'analyse de données complexes. Si vous travaillez avec des ensembles de données volumineux, ils peuvent valoir la peine d'être essayés. Mais la meilleure façon de consolider votre compréhension est la pratique. Je vous conseille donc de vous attaquer à quelques ensembles de données et d'expérimenter avec ce que vous avez appris. C'est ainsi que j'ai aiguisé mes compétences. 

Pour approfondir vos connaissances, consultez notre cours Fonctions Excel avancées afin de maîtriser une gamme plus large d'outils puissants. Mais si vous souhaitez acquérir une expertise complète en matière d'analyse de données dans Excel, je vous recommande notre cours Analyse de données dans Excel. Il couvre tout, de la préparation des données à la visualisation.