Indexer plusieurs critères

Indexer plusieurs lignes et colonnes de critères

Nous utilisons tous VLOOKUP jour après jour pour récupérer les données et nous sommes également conscients du fait que VLOOKUP peut récupérer les données de gauche à droite, donc la valeur de recherche doit toujours être sur le côté gauche des colonnes de résultats. Cependant, nous avons plusieurs alternatives qui peuvent être utilisées comme alternative à la fonction RECHERCHEV dans Excel. Avec une technologie avancée, nous pouvons utiliser ces formules INDEX + MATCH pour correspondre à plusieurs critères pour les lignes et les colonnes. Donc, cet article spécial vous expliquera en détail cette technique.

Comment utiliser la formule INDEX + MATCH pour correspondre à plusieurs critères?

Nous expliquons ici comment utiliser la formule index + match pour faire correspondre plusieurs critères de lignes et de colonnes avec des exemples.

Vous pouvez télécharger ce modèle Excel Index Match Multiple Criteria ici - Index Match Multiple Criteria Excel Template

Exemple # 1 - Formule INDEX + MATCH

Pas la majorité des fonctions de recherche des utilisateurs Excel au-delà de VLOOKUP, les raisons pourraient être si nombreuses. Quoi qu'il en soit, nous allons avoir une introduction simple à cette formule avant de passer au niveau avancé.

Par exemple, regardez la structure de données ci-dessous dans Excel.

Nous avons les noms des «représentants commerciaux» et leurs valeurs de vente respectives. D'autre part, nous avons une liste déroulante de «Sales Rep» dans la cellule D2.

En fonction de la sélection que nous faisons dans la liste déroulante, le montant des ventes doit apparaître dans la cellule E2.

Le problème est que nous ne pouvons pas appliquer la formule RECHERCHEV car la valeur de recherche «Sales Rep» se trouve à droite de la colonne de résultat «Ventes». Dans ces cas, nous pouvons donc utiliser la formule de valeur de recherche combinée INDEX + MATCH.

INDEX recherche la valeur du numéro de ligne mentionnée dans la plage A2: A11 et dans cette plage, nous devons fournir de quelle ligne nous avons besoin de la valeur des ventes. Cette valeur de ligne est basée sur le nom "Sales Rep" sélectionné dans la liste déroulante dans Excel, donc la fonction MATCH recherche le numéro de ligne "Sales Rep" dans la plage B2: B11 et renvoie le numéro de ligne de la valeur correspondante .

Exemple # 2 - Critères multiples dans la formule INDEX + MATCH

Nous avons maintenant une structure de données comme celle ci-dessous.

Nous avons des valeurs de ventes mensuelles de «Sales Rep». À partir de ce tableau, nous avons besoin de résultats dynamiques comme dans la cellule A15, j'ai créé une liste déroulante «Représentant des ventes» et dans la cellule B14, j'ai créé une liste déroulante «Mois».

Sur la base de la sélection effectuée dans ces deux cellules, notre formule doit extraire les données du tableau ci-dessus.

Par exemple, si je choisis «Rep 8» et «Apr», il doit indiquer la valeur des ventes de «Rep 8» pour le mois «Avr».

Donc, dans ces cas, nous devons faire correspondre les lignes et les colonnes. Suivez les étapes ci-dessous pour appliquer la formule afin qu'elle corresponde à la fois aux lignes et aux colonnes.

Étape 1: Ouvrez la fonction INDEX dans la cellule B15.

Étape 2: Le premier argument de la fonction INDEX est «Array», c'est-à-dire à partir de quelle plage de cellules nous avons besoin du résultat. Donc, dans ce cas, nous avons besoin de valeurs de vente, alors choisissez la plage de cellules de B2 à G11.

Étape 3: prochain argument de la fonction INDEX à partir de quelle ligne de la plage sélectionnée nous avons besoin du résultat. Dans ce cas, nous devons arriver au numéro de ligne «Sales Rep» en fonction de la sélection effectuée dans la cellule déroulante de la cellule A15. Donc, pour récupérer dynamiquement le numéro de ligne en fonction de la sélection, ouvrez la fonction MATCH.

Étape 4: LOOKUP VALUE de la fonction MATCH est «Sales Rep», choisissez la cellule A15 comme référence.

Étape 5: Lookup Array sera la plage de noms «Sales Rep» dans le tableau principal. Choisissez donc une plage allant de A2 à A11.

Étape 6: Le type de correspondance de la fonction MATCH sera exact, alors entrez zéro comme valeur d'argument.

Étape 7: Le prochain argument de la fonction INDEX est «Numéro de colonne», c'est-à-dire à partir de la plage de cellules sélectionnée à partir de laquelle nous avons besoin du résultat. Cela dépend du mois que nous sélectionnons dans la liste déroulante de la cellule B14. Donc, pour obtenir le numéro de colonne, ouvrez automatiquement une autre fonction MATCH.

Étape 8: Cette valeur de recherche de temps sera le nom du mois, alors sélectionnez la cellule B14 comme référence.

Étape 9: Le tableau de recherche sera la plage mensuelle des cellules du tableau principal, c'est-à-dire de B1 à G1.

Étape 10: Le dernier argument est le type de correspondance, choisissez «Correspondance exacte» comme critère. Fermez deux crochets et appuyez sur la touche Entrée pour obtenir le résultat.

Comme nous pouvons le voir ci-dessus, nous avons choisi "Rep 6" et "Apr" comme mois et notre formule a renvoyé la valeur des ventes pour le mois de "Apr" pour "Rep 6".

Remarque: la cellule de couleur jaune est la référence pour vous.

Choses à retenir

• Une combinaison de INDEX + MATCH peut être plus puissante que la formule RECHERCHEV.
• INDEX & MATCH peut correspondre à la fois aux en-têtes de lignes et de colonnes et renvoyer le résultat du tableau du milieu.
• MATCH peut renvoyer le numéro de ligne et le numéro de colonne des en-têtes de tableau des lignes et des colonnes.