Exceller

Comment utiliser INDEX et MATCH

How Use Index Match

INDEX and MATCH est l'outil le plus populaire d'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 être sur votre liste.

Cet article explique en termes simples comment utiliser ensemble INDEX et MATCH 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 ensemble pour créer une recherche dynamique bidirectionnelle. Il y a des exemples plus avancés plus bas dans la page.



Fonction INDEX | Fonction MATCH | INDEX et MATCH | Recherche bidirectionnelle | Recherche à gauche | Sensible aux majuscules et minuscules | Match le plus proche | Critères multiples | Plus d'exemples



La fonction INDEX

La fonction INDEX d'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 une table des planètes dans notre système solaire (voir ci-dessous), et que vous voulez 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 4ème ligne de la plage.



Vidéo: Comment rechercher des choses avec INDEX

Et si vous voulez 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 devez fournir à la fois le numéro de ligne et de colonne.

À ce stade, vous pensez peut-être «Et alors? À quelle fréquence connaissez-vous réellement la position d'un élément 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 la position dans une plage verticale
MATCH renvoie 3, car «Peach» est le troisième élément. MATCH n'est pas sensible à la casse.

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

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

Utilisation de MATCH pour trouver la 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 souhaiterez 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 de INDEX et MATCH, comment combiner les deux fonctions en une seule formule? Considérez les données ci-dessous, un tableau affichant une liste de vendeurs et les chiffres des ventes mensuelles pendant trois mois: janvier, février et mars.

Ventes par vendeur par mois

Disons que nous voulons écrire une formule qui renvoie le chiffre des 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 renvoyer 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 les nombres en dur. 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 postes dont nous avons besoin. En travaillant une étape à la fois, laissons la colonne codée en dur comme 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 d'INDEX.

Abordons maintenant le 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 nous avons codé en dur le numéro de colonne. Comment pouvons-nous rendre la formule pleinement 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 très 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 numéro de vente de 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 de INDEX et 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 se trouve dans le droit des valeurs que vous souhaitez récupérer, comme illustré dans l'exemple ci-dessous:

Recherche à gauche avec INDEX et MATCH

Lisez 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 EXACT 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

Lisez une explication détaillée ici .

Remarque: il s'agit d'un formule matricielle et doit être entré avec Ctrl + Maj + Entrée, sauf dans Excel 365 .

Match le 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 la 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 trajet associé de la colonne B.

Trouvez la correspondance la plus proche avec INDEX et MATCH

Lisez une explication détaillée ici .

Remarque: il s'agit d'un formule matricielle et doit être entré avec Ctrl + Maj + Entrée, 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 à plus d'une colonne en même temps. Dans l'exemple ci-dessous, nous utilisons INDEX et MATCH et logique booléenne pour correspondre sur 3 colonnes: article, couleur et taille:

INDEX et MATCH avec plusieurs critères

Lisez une explication détaillée ici .

Remarque: il s'agit d'un formule matricielle et doit être entré avec Ctrl + Maj + Entrée, 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 Attachments Déposer index exceljet et match.xlsx


^