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 atouts, leur combinaison débloque un ensemble d'outils puissants pour la recherche de données. Ensemble, ils facilitent une gamme de capacités de recherche, depuis les recherches horizontales et verticales de base jusqu'à des fonctionnalités plus avancées telles que les recherches bidirectionnelles, sensibles à la casse et multicritères. Offrant des capacités améliorées par rapport à VLOOKUP, l'association d'INDEX et de MATCH permet une plus large gamme d'options de recherche de données. Dans ce didacticiel, explorons l'étendue des possibilités qu'ils 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 de la première valeur non vide
• Recherche de la première valeur numérique
• Recherche d'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 en premier.

Comment utiliser la fonction INDEX dans Excel

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

• tableau (obligatoire) fait référence à la plage à partir de laquelle vous souhaitez renvoyer la valeur.
• row_num (obligatoire, sauf si num_colonne est présent) fait référence au numéro de ligne du tableau.
• num_colonne (facultatif, mais obligatoire si row_num est omis) fait référence au numéro de colonne du tableau.

Par exemple, pour savoir la partition de Jeff, 6ème élève de la liste, vous pouvez utiliser la fonction INDEX comme ceci :

=INDEX(C2:C11,6)

Remarque : la gamme C2: C11 est l'endroit où les scores sont répertoriés, tandis que le nombre 6 trouve la note d'examen du 6e étudiant.

Ici, faisons un petit test. Pour la formule =INDEX(A1:C1,2), quelle valeur renverra-t-il ? --- Oui, ça reviendra Date de naissance, 2ème valeur dans la ligne donnée.

Il faut maintenant savoir que la fonction INDEX peut parfaitement fonctionner avec des plages horizontales ou verticales. Mais que se passe-t-il si nous en avons besoin pour renvoyer une valeur dans une plage plus large 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 savoir La note de Jeff dans la plage du tableau au lieu d'une seule colonne, nous pouvons localiser son score avec un numéro de ligne de 6 et numéro de colonne de 3 dans l' cellules passant par A2 à C11 comme ça:

=INDEX(A2:C11,6,3)

Cependant, pour une très grande base de données 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 devons combiner l'utilisation de la fonction MATCH.

Incroyable! Utiliser des onglets efficaces dans Excel comme Chrome, Edge, Firefox et Safari !
Économisez 50% de votre temps et réduisez chaque jour des milliers de clics de souris!

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 :

• valeur de recherche (obligatoire) fait référence à la valeur à correspondre dans le tableau_recherche.
• tableau_recherche (obligatoire) fait référence à la plage de cellules dans laquelle vous souhaitez rechercher MATCH.
• Type de match (optionnel): 1, 0 or -1.
  • 1 (par défaut), MATCH trouvera la plus grande valeur inférieure ou égale à la valeur de recherche. Les valeurs dans le tableau_recherche doivent être placés dans l'ordre croissant.
  • 0, MATCH trouvera la première valeur exactement égale à la valeur de recherche. Les valeurs dans le tableau_recherche peut être dans n'importe quel ordre. (Pour les cas où le type de correspondance est défini sur 0, vous pouvez utiliser des caractères génériques.)
  • -1, MATCH trouvera la plus petite valeur supérieure ou égale à la valeur de recherche. Les valeurs dans le tableau_recherche doivent être placés par ordre décroissant.

Par exemple, pour savoir 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 en 4è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 consulter l'exemple ci-dessous pour comprendre comment combiner les fonctions INDEX et MATCH :

Trouver La partition d'Evelyn, sachant que les résultats des examens sont dans la 3ème colonne, on peut utilisez la fonction MATCH pour déterminer automatiquement la position de la ligne sans avoir besoin de le compter manuellement. Par la suite, nous pouvons utiliser la fonction INDEX pour récupérer le valeur à l'intersection de la ligne identifiée et de la 3ème colonne:

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

Eh bien, puisque la formule peut paraître un peu compliquée, passons en revue chaque partie.

La INDEX la formule contient trois arguments :

• row_num: CORRESPONDRE("Évelyn",A2:A11,0) fournit à INDEX la position de ligne de la valeur "Evelyn" dans le périmètre A2: A11, lequel est 5.
• num_colonne: 3 précise le 3ème colonne pour INDEX pour localiser le score dans le tableau.
• tableau: 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. Finalement, nous obtenons le résultat 90.

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

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

Exemples de formules INDEX et MATCH

Dans cette partie, nous parlerons de différentes circonstances pour utiliser les fonctions INDEX et MATCH pour 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 se passe-t-il si nous ne sommes pas sûrs du numéro de colonne également ?

Dans de tels cas, nous pouvons effectuer une recherche bidirectionnelle, également appelée 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 savoir La partition d'Evelyn, il faut 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 dans lequel 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 puissante fonction de recherche d'Excel, RECHERCHEV.

En fait, la possibilité d'effectuer des recherches sur le côté gauche est l'un des aspects dans lesquels la combinaison d'INDEX et de MATCH surpasse VLOOKUP.

Trouver La classe d'Evelyne, 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

Les fonctions MATCH sont intrinsèquement insensibles à la casse. Cependant, lorsque vous avez besoin que votre formule fasse la différence entre les caractères majuscules et minuscules, vous pouvez l'améliorer en incorporant le EXACT fonction. 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 :

• tableau fait référence à la plage à partir de laquelle vous souhaitez renvoyer la valeur.
• valeur de recherche fait référence à la valeur à laquelle correspondre, compte tenu de la casse des caractères, dans le tableau_recherche.
• tableau_recherche fait référence à la plage de cellules avec laquelle vous souhaitez que MATCH soit comparé valeur de recherche.

Par exemple, pour savoir La note 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 vous oblige à entrer avec Ctrl + Shift + Entrer, sauf dans Excel 365 et Excel 2021.

INDEX et MATCH pour trouver la correspondance la plus proche

Dans Excel, vous pouvez rencontrer des situations dans lesquelles 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, l’utilisation d’une combinaison des fonctions INDEX et MATCH, ainsi que des fonctions ABS et MIN, peut s’avérer extrêmement utile.

• tableau fait référence à la plage à partir de laquelle vous souhaitez renvoyer la valeur.
• tableau_recherche fait référence à la plage de valeurs à laquelle vous souhaitez trouver la correspondance la plus proche valeur de recherche.
• valeur de recherche fait référence à la valeur pour trouver sa correspondance la plus proche.

Par exemple, pour savoir dont le score est le plus proche de 85, utilisez la formule suivante pour rechercher le score le plus proche de 85 en 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 vous oblige à entrer avec Ctrl + Shift + Entrer, sauf dans Excel 365 et Excel 2021.

Incroyable! Utiliser des onglets efficaces dans Excel comme Chrome, Edge, Firefox et Safari !
Économisez 50% de votre temps et réduisez chaque jour des milliers de clics de souris!

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

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

√ Remarque : Il s'agit d'une formule matricielle qui vous oblige à entrer avec Ctrl + Shift + Entrer. Une paire d'accolades apparaîtra alors dans la barre de formule.

• tableau fait référence à la plage à partir de laquelle vous souhaitez renvoyer la valeur.
• (lookup_value=lookup_array) représente une seule condition. Cette condition vérifie si un valeur de recherche correspond aux valeurs dans le tableau_recherche.

Par exemple, pour trouver le score de Coco de classe A, dont la date de naissance est le 7/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 dans lequel vous avez affaire à plusieurs colonnes de données. La première colonne fait office de 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é appropriée dans la colonne de référence.

Par exemple, dans le tableau ci-dessous, comment pouvons-nous faire correspondre l'élève Shawn avec sa classe correspondante en utilisant INDEX et MATCH ? Eh bien, vous pouvez y parvenir avec une formule, mais la formule est assez complète et peut être difficile à comprendre, sans parler de la mémorisation et de la saisie.

=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 Kutools for Excel's Indexer et faire correspondre sur plusieurs colonnes la fonctionnalité est pratique. Cela simplifie le processus, permettant de faire correspondre rapidement et facilement des entrées spécifiques avec leurs catégories correspondantes. Pour débloquer ce puissant outil et faire correspondre sans effort Shawn avec sa classe, il suffit téléchargez et installez le complément Kutools pour Excel, puis procédez comme suit :

1. Sélectionnez la cellule de destination dans laquelle vous souhaitez afficher la classe correspondante.
2. Sur le Kutools onglet, cliquez sur Aide à la formule > Recherche et référence > Indexer et faire correspondre sur plusieurs colonnes.
3. 
3. Dans la boîte de dialogue contextuelle, procédez comme suit :
   1. Cliquez sur le 1er bouton à côté de Recherche_col pour sélectionner la colonne contenant les informations clés que vous souhaitez renvoyer, c'est-à-dire les noms de classe. (Vous ne pouvez sélectionner qu'une seule colonne ici.)
   2. Cliquez sur le 2ème bouton à côté de Table_rng pour sélectionner les cellules pour correspondre aux valeurs dans le sélectionné Recherche_col, c'est-à-dire les noms des étudiants.
   3. Cliquez sur le 3ème bouton à côté de Valeur_de recherche pour sélectionner la cellule contenant le nom de l'élève que vous souhaitez associer à sa classe, dans ce cas, Shawn.
   4. Cliquez OK.
   5. 

Résultat

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

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 d'une colonne ou d'une ligne en ignorant les erreurs :

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

• Obtenez la première valeur non vide d'une colonne ou d'une ligne 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 des 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 :
• • tableau fait référence à la plage à partir de laquelle vous souhaitez renvoyer les informations associées.
  • tableau_recherche représente l'ensemble des valeurs à examiner ou à rechercher selon 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é, employez 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 aucun résultat correspondant. Par exemple, dans le tableau ci-dessous, lorsque vous tentez de trouver le score d'une élève 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 encapsulant 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 souhaitez découvrir davantage de trucs et astuces Excel, cliquez ici s'il vous plait pour accéder à notre vaste collection de plus de milliers de tutoriels.