Exceller

Comment utiliser INDEX et MATCH

How Use Index Match

INDEX et MATCH est l'outil le plus populaire dans Excel pour effectuer des recherches plus avancées. En effet, INDEX et MATCH sont incroyablement flexibles : vous pouvez effectuer des recherches horizontales et verticales, des recherches bidirectionnelles, des recherches à gauche, des recherches sensibles à la casse et même des recherches basées sur plusieurs critères. Si vous souhaitez améliorer vos compétences Excel, INDEX et MATCH devraient figurer sur votre liste.





Cet article explique en termes simples comment utiliser INDEX et MATCH ensemble pour effectuer des recherches. Il adopte une approche étape par étape, expliquant d'abord INDEX, puis MATCH, puis vous montrant comment combiner les deux fonctions pour créer une recherche bidirectionnelle dynamique. Il y a des exemples plus avancés plus bas sur la page.

fonction INDEX | Fonction MATCH | INDEX et MATCH | recherche bidirectionnelle | Recherche à gauche | Sensible aux majuscules et minuscules | Correspondance la plus proche | Critères multiples | Plus d'exemples





La fonction INDEX

La fonction INDEX dans Excel est incroyablement flexible et puissante, et vous la trouverez dans un grand nombre de formules Excel, en particulier les formules avancées. Mais que fait réellement INDEX ? En un mot, INDEX récupère la valeur à un emplacement donné dans une plage. Par exemple, disons que vous avez un tableau des planètes de notre système solaire (voir ci-dessous) et que vous souhaitez obtenir le nom de la 4ème planète, Mars, avec une formule. Vous pouvez utiliser INDEX comme ceci :

 
= INDEX (B3:B11,4)

Utiliser INDEX pour obtenir le nom de la 4ème planète
INDEX renvoie la valeur de la 4e ligne de la plage.



Vidéo: Comment rechercher des choses avec INDEX

Et si vous vouliez obtenir le diamètre de Mars avec INDEX ? Dans ce cas, nous pouvons fournir à la fois un numéro de ligne et un numéro de colonne, et fournir une plage plus large. La formule INDEX ci-dessous utilise la gamme complète de données dans B3:D11, avec un numéro de ligne de 4 et un numéro de colonne de 2:

 
= INDEX (B3:D11,4,2)

Utiliser INDEX pour obtenir le diamètre de la 4ème planète
INDEX récupère la valeur à la ligne 4, colonne 2.

Pour résumer, INDEX obtient une valeur à un emplacement donné dans une plage de cellules en fonction de la position numérique. Lorsque la plage est unidimensionnelle, il vous suffit de fournir un numéro de ligne. Lorsque la plage est bidimensionnelle, vous devrez fournir à la fois le numéro de ligne et le numéro de colonne.

À ce stade, vous pensez peut-être « Et alors ? À quelle fréquence connaissez-vous réellement la position de quelque chose dans une feuille de calcul ? »

Exactement raison. Nous avons besoin d'un moyen de localiser la position des choses que nous recherchons.

Entrez dans la fonction MATCH.

La fonction MATCH

La fonction MATCH est conçue dans un seul but : trouver la position d'un élément dans une plage. Par exemple, nous pouvons utiliser MATCH pour obtenir la position du mot « pêche » dans cette liste de fruits comme ceci :

 
= MATCH ('peach',B3:B9,0)

Utilisation de MATCH pour trouver une position dans une plage verticale
MATCH renvoie 3, puisque 'Peach' est le 3ème élément. MATCH n'est pas sensible à la casse.

MATCH ne se soucie pas de savoir si une plage est horizontale ou verticale, comme vous pouvez le voir ci-dessous :

 
= MATCH ('peach',C4:I4,0)

Utilisation de MATCH pour trouver une position dans une plage horizontale
Même résultat avec une plage horizontale, MATCH renvoie 3.

Vidéo: Comment utiliser MATCH pour des correspondances exactes

Important : le dernier argument de la fonction MATCH est le type de correspondance. Le type de correspondance est important et contrôle si la correspondance est exacte ou approximative. Dans de nombreux cas, vous voudrez utiliser zéro (0) pour forcer le comportement de correspondance exacte. Le type de correspondance est défini par défaut sur 1, ce qui signifie une correspondance approximative. Il est donc important de fournir une valeur. Voir le page MATCH pour plus de détails.

INDEX et MATCH ensemble

Maintenant que nous avons couvert les bases d'INDEX et de MATCH, comment combinons-nous les deux fonctions dans une seule formule ? Considérez les données ci-dessous, un tableau présentant une liste de vendeurs et des chiffres de ventes mensuels pour trois mois : janvier, février et mars.

Ventes par vendeur par mois

Disons que nous voulons écrire une formule qui renvoie le nombre de ventes de février pour un vendeur donné. D'après la discussion ci-dessus, nous savons que nous pouvons donner à INDEX un numéro de ligne et de colonne pour récupérer une valeur. Par exemple, pour retourner le numéro de vente de février pour Frantz, nous fournissons la plage C3:E11 avec une ligne 5 et une colonne 2 :

 
= INDEX (C3:E11,5,2) // returns 94

Mais nous ne voulons évidemment pas coder en dur les numéros. Au lieu de cela, nous voulons un dynamique chercher.

Comment allons-nous faire cela? La fonction MATCH bien sûr. MATCH fonctionnera parfaitement pour trouver les positions dont nous avons besoin. En travaillant étape par étape, laissons la colonne codée en dur sur 2 et rendons le numéro de ligne dynamique. Voici la formule révisée, avec la fonction MATCH imbriquée dans INDEX à la place de 5:

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

Pour aller plus loin, nous utiliserons la valeur de H2 dans MATCH :

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

INDEX et MATCH pour trouver les ventes de février pour n'importe quel nom
MATCH trouve 'Frantz' et renvoie 5 à INDEX pour la ligne.

Résumer:

  1. INDEX a besoin de positions numériques.
  2. MATCH trouve ces positions.
  3. MATCH est imbriqué à l'intérieur de INDEX.

Intéressons-nous maintenant au numéro de colonne.

Recherche bidirectionnelle avec INDEX et MATCH

Ci-dessus, nous avons utilisé la fonction MATCH pour trouver le numéro de ligne de manière dynamique, mais codé en dur le numéro de colonne. Comment pouvons-nous rendre la formule entièrement dynamique, afin que nous puissions retourner les ventes pour n'importe quel vendeur donné au cours d'un mois donné ? L'astuce consiste à utiliser MATCH deux fois – une fois pour obtenir une position de ligne et une fois pour obtenir une position de colonne.

D'après les exemples ci-dessus, nous savons que MATCH fonctionne bien avec les tableaux horizontaux et verticaux. Cela signifie que nous pouvons facilement trouver la position d'un mois donné avec MATCH. Par exemple, cette formule renvoie la position de mars, qui est 3 :

 
= MATCH ('Mar',C2:E2,0) // returns 3

Mais bien sûr, nous ne voulons pas coder en dur tout valeurs, mettons donc à jour la feuille de calcul pour permettre la saisie d'un nom de mois et utilisons MATCH pour trouver le numéro de colonne dont nous avons besoin. L'écran ci-dessous montre le résultat :

Recherche dynamique avec INDEX et MATCH
Une recherche bidirectionnelle entièrement dynamique avec INDEX et MATCH.

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

La première formule MATCH renvoie 5 à INDEX comme numéro de ligne, la deuxième formule MATCH renvoie 3 à INDEX comme numéro de colonne. Une fois MATCH exécuté, la formule se simplifie en :

 
= INDEX (C3:E11,5,3)

et INDEX renvoie correctement 10 525 $, le nombre de ventes pour Frantz en mars.

Remarque : vous pouvez utiliser La validation des données pour créer des menus déroulants pour sélectionner le vendeur et le mois.

Vidéo: Comment faire une recherche bidirectionnelle avec INDEX et MATCH

Vidéo: Comment déboguer une formule avec F9 (pour voir les valeurs de retour de MATCH)

Recherche à gauche

L'un des principaux avantages d'INDEX et de MATCH par rapport à la fonction RECHERCHEV est la possibilité d'effectuer une « recherche à gauche ». En termes simples, cela signifie simplement une recherche où la colonne ID est à la droit des valeurs que vous souhaitez récupérer, comme le montre l'exemple ci-dessous :

Recherche à gauche avec INDEX et MATCH

Lire une explication détaillée ici .

Recherche sensible à la casse

En soi, la fonction MATCH n'est pas sensible à la casse. Cependant, vous utilisez le Fonction EXACTE avec INDEX et MATCH pour effectuer une recherche qui respecte les majuscules et les minuscules, comme indiqué ci-dessous :

Recherche sensible à la casse avec INDEX et MATCH

Lire une explication détaillée ici .

Remarque : il s'agit d'un formule matricielle et doit être saisi avec control + shift + enter, sauf dans Excel 365 .

Correspondance la plus proche

Un autre exemple qui montre la flexibilité de INDEX et MATCH est le problème de trouver le correspondance la plus proche . Dans l'exemple ci-dessous, nous utilisons le Fonction MIN avec le Fonction ABS à créer une valeur de recherche et un tableau de recherche à l'intérieur la fonction MATCH. Essentiellement, nous utilisons MATCH pour trouver la plus petite différence. Ensuite, nous utilisons INDEX pour récupérer le voyage associé de la colonne B.

Trouvez la correspondance la plus proche avec INDEX et MATCH

Lire une explication détaillée ici .

Remarque : il s'agit d'un formule matricielle et doit être saisi avec control + shift + enter, sauf dans Excel 365 .

Recherche de critères multiples

L'un des problèmes les plus délicats dans Excel est une recherche basée sur plusieurs critères. En d'autres termes, une recherche qui correspond à plusieurs colonnes en même temps. Dans l'exemple ci-dessous, nous utilisons INDEX et MATCH et logique booléenne à faire correspondre sur 3 colonnes : Article, Couleur et Taille :

INDEX et MATCH avec plusieurs critères

Lire une explication détaillée ici .

Remarque : il s'agit d'un formule matricielle et doit être saisi avec control + shift + enter, sauf dans Excel 365 .

mise en forme conditionnelle basée sur une autre valeur de cellule

Plus d'exemples d'INDEX + MATCH

Voici quelques exemples plus basiques d'INDEX et de MATCH en action, chacun avec une explication détaillée :

Auteur Dave Bruns Pièces jointes Déposer index exceljet et match.xlsx


^