Excel INDEX MATCH : Recherches basiques et avancées

Dans Excel, récupérer avec précision des données spécifiques est souvent une nécessité fréquente. Bien que les fonctions INDEX et MATCH aient chacune leurs propres forces, les combiner débloque un ensemble d'outils puissant pour la recherche de données. Ensemble, elles facilitent une gamme de capacités de recherche, allant des recherches horizontales et verticales basiques à des fonctionnalités plus avancées comme les recherches bidirectionnelles, sensibles à la casse et multicritères. Offrant des capacités améliorées par rapport à RECHERCHEV, l'association d'INDEX et MATCH permet une gamme plus large d'options de recherche de données. Dans ce tutoriel, explorons en profondeur les possibilités qu'elles peuvent réaliser ensemble.

• Fonction INDEX
• Fonction MATCH
• Combiner INDEX et MATCH

• Recherche bidirectionnelle
• Recherche à gauche
• Recherche sensible à la casse
• Correspondance la plus proche
• Recherche avec plusieurs critères
• Recherche sur plusieurs colonnes
• Recherche pour la première valeur non vide
• Recherche pour la première valeur numérique
• Recherche pour les associations MAX ou MIN
• Astuce : Personnalisez vos propres messages d'erreur #N/A

Comment utiliser INDEX et MATCH dans Excel

Avant d'utiliser les fonctions INDEX et MATCH, assurons-nous de savoir comment INDEX et MATCH peuvent nous aider à rechercher des valeurs.

Comment utiliser la fonction INDEX dans Excel

La fonction INDEX dans Excel renvoie la valeur à un emplacement donné dans une plage spécifique. La syntaxe de la fonction INDEX est la suivante :

• array (requis) fait référence à la plage d'où vous souhaitez renvoyer la valeur.
• row_num (requis, sauf si column_num est présent) fait référence au numéro de ligne de l'array.
• column_num (facultatif, mais requis si row_num est omis) fait référence au numéro de colonne de l'array.

Par exemple, pour connaître le score de Jeff, le6ème étudiant de la liste, vous pouvez utiliser la fonction INDEX comme ceci :

=INDEX(C2:C11,6)

√ Remarque : La plage C2:C11 est l'endroit où les scores sont listés, tandis que le numéro6 trouve le score d'examen du6ème étudiant.

Faisons maintenant un petit test. Pour la formule =INDEX(A1:C1,2), quelle valeur renverra-t-elle ? --- Oui, elle renverra Date de naissance, la2ème valeur dans la ligne donnée.

Nous devrions maintenant savoir que la fonction INDEX peut fonctionner parfaitement avec des plages horizontales ou verticales. Mais que faire si nous avons besoin qu'elle renvoie une valeur dans une plage plus grande avec plusieurs lignes et colonnes ? Eh bien, dans ce cas, nous devrions appliquer à la fois un numéro de ligne et un numéro de colonne. Par exemple, pour trouver le score de Jeff dans la plage du tableau au lieu d'une seule colonne, nous pouvons localiser son score avec un numéro de ligne de6 et un numéro de colonne de3 dans les cellules de A2 à C11 comme ceci :

=INDEX(A2:C11,6,3)

Cependant, pour une base de données vraiment grande avec plusieurs lignes et colonnes, il n'est certainement pas pratique pour nous d'appliquer la formule avec un numéro de ligne et un numéro de colonne exacts. Et c'est là que nous devrions combiner l'utilisation de la fonction MATCH.

Comment utiliser la fonction MATCH dans Excel

La fonction MATCH dans Excel renvoie une valeur numérique, l'emplacement d'un élément spécifique dans la plage donnée. La syntaxe de la fonction MATCH est la suivante :

• lookup_value (requis) fait référence à la valeur à correspondre dans lookup_array.
• lookup_array (requis) fait référence à la plage de cellules où vous souhaitez que MATCH recherche.
• match_type (facultatif) : 1, 0 ou -1.
  • 1(par défaut), MATCH trouvera la plus grande valeur qui est inférieure ou égale à lookup_value. Les valeurs dans lookup_array doivent être placées en ordre croissant.
  • 0, MATCH trouvera la première valeur qui est exactement égale à lookup_value. Les valeurs dans lookup_array peuvent être dans n'importe quel ordre. (Pour les cas où le type de correspondance est défini à0, vous pouvez utiliser des caractères génériques.)
  • -1, MATCH trouvera la plus petite valeur qui est supérieure ou égale à lookup_value. Les valeurs dans lookup_array doivent être placées en ordre décroissant.

Par exemple, pour connaître la position de Vera dans la liste des noms, vous pouvez utiliser la formule MATCH comme ceci :

=MATCH("Vera",A2:A11,0)

√ Remarque : Le résultat «4 » indique que le nom « Vera » est à la4ème position de la liste.

Maintenant que nous connaissons les utilisations de base des fonctions INDEX et MATCH dans Excel, retroussons nos manches et préparons-nous à combiner les deux fonctions.

Comment combiner INDEX et MATCH dans Excel

Veuillez voir l'exemple ci-dessous pour comprendre comment nous pouvons combiner les fonctions INDEX et MATCH :

Pour trouver le score d'Evelyn, sachant que les scores d'examen sont dans la3ème colonne, nous pouvons utiliser la fonction MATCH pour déterminer automatiquement la position de la ligne sans avoir besoin de la compter manuellement. Ensuite, nous pouvons utiliser la fonction INDEX pour récupérer la valeur à l'intersection de la ligne identifiée et de la3ème colonne :

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Eh bien, puisque la formule peut sembler un peu compliquée, passons en revue chaque partie de celle-ci.

La formule INDEX contient trois arguments :

• row_num: MATCH("Evelyn",A2:A11,0)fournit à INDEX la position de la ligne de la valeur "Evelyn" dans la plage A2:A11, qui est5.
• column_num :3 spécifie la3ème colonne pour qu'INDEX localise le score dans l'array.
• array : A2:C11 demande à INDEX de renvoyer la valeur correspondante à l'intersection de la ligne et de la colonne spécifiées, dans la plage allant de A2 à C11. Enfin, nous obtenons le résultat90.

Dans la formule ci-dessus, nous avons utilisé une valeur codée en dur, "Evelyn". Cependant, en pratique, les valeurs codées en dur sont peu pratiques, car elles nécessiteraient une modification chaque fois que nous cherchons à rechercher des données différentes, comme le score d'un autre étudiant. Dans de tels scénarios, nous pouvons utiliser des références de cellule pour créer des formules dynamiques. Par exemple, dans ce cas, je vais changer "Evelyn" en F2 :

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Exemples de formules INDEX et MATCH

Dans cette partie, nous parlerons des différentes circonstances pour utiliser les fonctions INDEX et MATCH afin de répondre à différents besoins.

INDEX et MATCH pour appliquer une recherche bidirectionnelle

Dans l'exemple précédent, nous connaissions le numéro de colonne et avons utilisé une formule MATCH pour trouver le numéro de ligne. Mais que faire si nous ne sommes pas sûrs du numéro de colonne également ?

Dans de tels cas, nous pouvons effectuer une recherche bidirectionnelle, également connue sous le nom de recherche matricielle, en utilisant deux fonctions MATCH : une pour trouver le numéro de ligne et l'autre pour déterminer le numéro de colonne. Par exemple, pour connaître le score d'Evelyn, nous devrions utiliser la formule :

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX et MATCH pour appliquer une recherche à gauche

Considérons maintenant un scénario où vous devez déterminer la classe d'Evelyn. Vous avez peut-être remarqué que la colonne de classe est positionnée à gauche de la colonne de nom, une situation qui dépasse les capacités d'une autre fonction de recherche puissante d'Excel, RECHERCHEV.

En fait, la capacité à effectuer des recherches du côté gauche est l'un des aspects où la combinaison d'INDEX et MATCH surpasse RECHERCHEV.

Pour trouver la classe d'Evelyn, utilisez la formule suivante pour rechercher Evelyn dans B2:B11 et récupérer la valeur correspondante de A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX et MATCH pour appliquer une recherche sensible à la casse

La fonction MATCH est intrinsèquement insensible à la casse. Pourtant, lorsque vous avez besoin que votre formule différencie les caractères majuscules et minuscules, vous pouvez l'améliorer en incorporant la fonction EXACT. En combinant la fonction MATCH avec EXACT dans une formule INDEX, vous pouvez alors effectuer efficacement une recherche sensible à la casse, comme indiqué ci-dessous :

• array fait référence à la plage d'où vous souhaitez renvoyer la valeur.
• lookup_value fait référence à la valeur à correspondre, en tenant compte de la casse des caractères, dans lookup_array.
• lookup_array fait référence à la plage de cellules où vous souhaitez que MATCH compare avec lookup_value.

Par exemple, pour connaître le score d'examen de JIMMY, utilisez la formule suivante :

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être entrée avec Ctrl + Shift + Enter, sauf dans Excel365, Excel2021 et les versions plus récentes.

INDEX et MATCH pour trouver une correspondance la plus proche

Dans Excel, vous pouvez rencontrer des situations où vous devez trouver la correspondance la plus proche ou la plus proche d'une valeur spécifique dans un ensemble de données. Dans de tels scénarios, utiliser une combinaison des fonctions INDEX et MATCH, avec les fonctions ABS et MIN, peut être incroyablement utile.

• array fait référence à la plage d'où vous souhaitez renvoyer la valeur.
• lookup_array fait référence à la plage de valeurs où vous souhaitez trouver la correspondance la plus proche de lookup_value.
• lookup_value fait référence à la valeur pour trouver sa correspondance la plus proche.

Par exemple, pour savoir dont le score est le plus proche de85, utilisez la formule suivante pour rechercher le score le plus proche de85 dans C2:C11 et récupérer la valeur correspondante de A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être entrée avec Ctrl + Shift + Enter, sauf dans Excel365, Excel2021 et les versions plus récentes.

INDEX et MATCH pour appliquer une recherche avec plusieurs critères

Pour trouver une valeur qui répond à plusieurs conditions, nécessitant de rechercher sur deux colonnes ou plus, utilisez la formule suivante. La formule vous permet d'effectuer une recherche multicritères en spécifiant diverses conditions sur différentes colonnes, vous aidant à trouver la valeur souhaitée qui répond à tous les critères spécifiés.

√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être entrée avec Ctrl + Shift + Enter. Une paire de crochets apparaîtra alors dans la barre de formule.

• array fait référence à la plage d'où vous souhaitez renvoyer la valeur.
• (lookup_value=lookup_array) représente une seule condition. Cette condition vérifie si un lookup_value particulier correspond aux valeurs dans lookup_array.

Par exemple, pour trouver le score de Coco de la classe A, dont la date de naissance est le7/2/2008, vous pouvez utiliser la formule suivante :

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX et MATCH pour appliquer une recherche sur plusieurs colonnes

Imaginez un scénario où vous traitez plusieurs colonnes de données. La première colonne agit comme une clé pour classer les données dans les autres colonnes. Pour déterminer la catégorie ou la classification d'une entrée spécifique, vous devrez effectuer une recherche dans les colonnes de données et l'associer à la clé pertinente dans la colonne de référence.

Par exemple, dans le tableau ci-dessous, comment pouvons-nous associer l'étudiant Shawn à sa classe correspondante en utilisant INDEX et MATCH ? Eh bien, vous pouvez y parvenir avec une formule, mais la formule est assez longue et peut être difficile à comprendre, sans parler de la mémoriser et de la taper.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

C'est là que la fonctionnalité Index et correspondance de plusieurs colonnes de Kutools pour Excel est utile. Elle simplifie le processus, rendant rapide et facile l'association d'entrées spécifiques à leurs catégories correspondantes. Pour débloquer cet outil puissant et associer facilement Shawn à sa classe, téléchargez et installez simplement le module complémentaire Kutools pour Excel, puis procédez comme suit :

1. Sélectionnez la cellule de destination où vous souhaitez afficher la classe correspondante.
2. Sur l'onglet Kutools, cliquez sur Assistant de formule > Recherche & Référence > Index et correspondance de plusieurs colonnes.
3. 
3. Dans la boîte de dialogue pop-up, procédez comme suit :
   1. Cliquez sur le1er bouton à côté de Colonne de recherche pour sélectionner la colonne contenant les informations clés que vous souhaitez renvoyer, c'est-à-dire les noms de classe. (Vous pouvez uniquement sélectionner une seule colonne ici.)
   2. Cliquez sur le2ème bouton à côté de Plage de table pour sélectionner les cellules pour correspondre aux valeurs dans la Colonne de recherche, c'est-à-dire les noms des étudiants.
   3. Cliquez sur le3ème bouton à côté de Valeur de recherche pour sélectionner la cellule contenant le nom de l'étudiant que vous souhaitez associer à sa classe, dans ce cas, Shawn.
   4. Cliquez sur OK.
   5. 

Résultat

Kutools a automatiquement généré la formule, et vous verrez le nom de la classe de Shawn affiché dans la cellule de destination immédiatement.

INDEX et MATCH pour rechercher la première valeur non vide

Pour récupérer la première valeur non vide, en ignorant les erreurs, d'une colonne ou d'une ligne, vous pouvez utiliser une formule basée sur les fonctions INDEX et MATCH. Cependant, si vous ne souhaitez pas ignorer les erreurs de votre plage, ajoutez la fonction ISBLANK.

• Obtenez la première valeur non vide dans une colonne ou une ligne en ignorant les erreurs :

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Obtenez la première valeur non vide dans une colonne ou une ligne en incluant les erreurs :

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX et MATCH pour rechercher la première valeur numérique

Pour récupérer la première valeur numérique d'une colonne ou d'une ligne, utilisez la formule basée sur les fonctions INDEX, MATCH et ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX et MATCH pour rechercher les associations MAX ou MIN

Si vous devez récupérer une valeur associée à la valeur maximale ou minimale dans une plage, vous pouvez utiliser la fonction MAX ou MIN avec les fonctions INDEX et MATCH.

• INDEX et MATCH pour récupérer une valeur associée à la valeur maximale :
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array,0))
• INDEX et MATCH pour récupérer une valeur associée à la valeur minimale :
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array,0))
• Il y a deux arguments dans les formules ci-dessus :
• • array fait référence à la plage d'où vous souhaitez renvoyer les informations associées.
  • lookup_array représente l'ensemble des valeurs à examiner ou à rechercher pour des critères spécifiques, c'est-à-dire des valeurs maximales ou minimales.

Par exemple, si vous souhaitez déterminer qui a le score le plus élevé, utilisez la formule suivante :

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Astuce : Personnalisez vos propres messages d'erreur #N/A

Lorsque vous travaillez avec les fonctions INDEX et MATCH d'Excel, vous pouvez rencontrer l'erreur #N/A lorsqu'il n'y a pas de résultat correspondant. Par exemple, dans le tableau ci-dessous, en essayant de trouver le score d'une étudiante nommée Samantha, une erreur #N/A apparaît car elle n'est pas présente dans l'ensemble de données.

Pour rendre vos feuilles de calcul plus conviviales, vous pouvez personnaliser ce message d'erreur en enveloppant votre formule INDEX MATCH dans la fonction IFNA :

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Ci-dessus se trouve tout le contenu pertinent lié aux fonctions INDEX et MATCH dans Excel. J'espère que vous trouverez le tutoriel utile. Si vous cherchez à explorer plus de conseils et astuces Excel, veuillez cliquer ici pour accéder à notre vaste collection de milliers de tutoriels.