Cet article explique comment utiliser ensemble les fonctions INDEX et MATCH dans toutes les versions d’Excel, y compris Excel 2019 et Microsoft 365.
Sommaire
Que sont les fonctions INDEX et MATCH ?
INDEX et MATCH sont des fonctions de recherche Excel. Bien qu’il s’agisse de deux fonctions entièrement distinctes qui peuvent être utilisées seules, elles peuvent également être combinées pour créer des formules avancées.
La fonction INDEX renvoie une valeur ou la référence à une valeur au sein d’une sélection particulière. Par exemple, elle peut être utilisée pour trouver la valeur de la deuxième ligne d’un ensemble de données, ou de la cinquième ligne et de la troisième colonne.
Si INDEX peut très bien être utilisé seul, l’imbrication de MATCH dans la formule le rend un peu plus utile. La fonction MATCH recherche un élément spécifié dans une plage de cellules, puis renvoie la position relative de cet élément dans la plage. Par exemple, elle peut être utilisée pour déterminer qu’un nom spécifique est le troisième élément d’une liste de noms.
Exemple de fonction Excel INDEX MATCH
INDEX-MATCH VS. VLOOKUP DANS EXCEL
Syntaxe et arguments de INDEX et MATCH
Voici comment les deux fonctions doivent être écrites pour qu’Excel les comprenne :
=INDEX(tableau, numéro_ligne, [numéro_colonne])
Le tableau est la plage de cellules que la formule va utiliser. Il peut s'agir d'une ou plusieurs lignes et colonnes, par exemple A1:D5. Il est obligatoire.
row_num est la ligne du tableau à partir de laquelle il faut retourner une valeur, comme 2 ou 18. Il est obligatoire à moins que column_num soit présent.
column_num est la colonne du tableau à partir de laquelle on peut retourner une valeur, comme 1 ou 9. Elle est facultative.=MATCH(lookup_value, lookup_array, [match_type])
lookup_value est la valeur que vous voulez faire correspondre dans lookup_array. Il peut s'agir d'un nombre, d'un texte ou d'une valeur logique qui a été saisie manuellement ou à laquelle il est fait référence via une référence de cellule. Cette valeur est obligatoire.
lookup_array est la plage de cellules à consulter. Il peut s'agir d'une seule ligne ou d'une seule colonne, par exemple A2:D2 ou G1:G45. Ce champ est obligatoire.
match_type peut être -1, 0 ou 1. Il spécifie comment lookup_value est mis en correspondance avec les valeurs de lookup_array (voir ci-dessous). 1 est la valeur par défaut si cet argument est omis.Exemples de formules INDEX et MATCH
Avant de voir comment combiner INDEX et MATCH en une seule formule, nous devons comprendre comment ces fonctions fonctionnent seules.
Exemples d’INDEX
=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)
Exemples de formules Excel INDEX
Dans ce premier exemple, il existe quatre formules INDEX que nous pouvons utiliser pour obtenir différentes valeurs :
=INDEX(A1:B2,2,2) parcourt A1:B2 pour trouver la valeur de la deuxième colonne et de la deuxième ligne, qui est Stacy.
=INDEX(A1:B1,1) parcourt A1:B1 pour trouver la valeur de la première colonne, qui est Jon.
=INDEX(2:2,1) parcourt tout ce qui se trouve dans la deuxième ligne pour trouver la valeur dans la première colonne, qui est Tim.
=INDEX(B1:B2,1) parcourt B1:B2 pour trouver la valeur de la première ligne, qui est Amy.
Exemples de MATCH
=MATCH(« Stacy »,A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)
Exemples de la fonction MATCH dans Excel
Voici quatre exemples simples de la fonction MATCH :
=MATCH(« Stacy »,A2:D2,0) recherche Stacy dans la plage A2:D2 et renvoie 3 comme résultat.
=MATCH(14,D1:D2) cherche 14 dans la plage D1:D2, mais comme il n’est pas trouvé dans la table, MATCH trouve la prochaine plus grande valeur inférieure ou égale à 14, qui dans ce cas est 13, qui est en position 1 de lookup_array.
=MATCH(14,D1:D2,-1) est identique à la formule ci-dessus, mais comme le tableau n’est pas en ordre décroissant comme l’exige -1, nous obtenons une erreur.
=MATCH(13,A1:D1,0) cherche 13 dans la première ligne de la feuille, ce qui renvoie 4 puisque c’est le quatrième élément de ce tableau.
Exemples d’INDEX-MATCH
Voici deux exemples dans lesquels nous pouvons combiner INDEX et MATCH dans une formule :
Trouver la référence d’une cellule dans un tableau
=INDEX(B2:B5,MATCH(F1,A2:A5))
Fonctions Excel MATCH et INDEX imbriquées dans une formule
Dans cet exemple, la formule MATCH est imbriquée dans la formule INDEX. L’objectif est d’identifier la couleur de l’article à l’aide du numéro d’article.
Si vous regardez l’image, vous pouvez voir dans les lignes « séparées » comment les formules seraient écrites séparément, mais comme nous les imbriquons, voici ce qui se passe :
MATCH(F1,A2:A5) recherche la valeur F1 (8795) dans l’ensemble de données A2:A5. Si nous comptons vers le bas de la colonne, nous pouvons voir que c’est 2, donc c’est ce que la fonction MATCH vient de comprendre.
Le tableau INDEX est B2:B5 puisque nous recherchons finalement la valeur dans cette colonne.
La fonction INDEX pourrait maintenant être réécrite comme ceci puisque 2 est ce que MATCH a trouvé : INDEX(B2:B5, 2, [numéro_colonne]).
Puisque le numéro de colonne est facultatif, nous pouvons le supprimer pour obtenir ceci : INDEX(B2:B5,2).
Maintenant, c’est comme une formule INDEX normale où nous trouvons la valeur du deuxième élément de B2:B5, qui est rouge.
Recherche par titres de ligne et de colonne
=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0))
Exemple d’imbrication INDEX et MATCH dans Excel
Dans cet exemple de MATCH et INDEX, nous effectuons une recherche à double sens. L’idée est de voir combien d’argent nous avons gagné avec les articles verts en mai. Cet exemple est très similaire à l’exemple précédent, mais une formule MATCH supplémentaire est imbriquée dans INDEX.
MATCH(G1,A2:A13,0) est le premier élément résolu dans cette formule. Elle recherche G1 (le mot "May") dans A2:A13 pour obtenir une valeur particulière. Nous ne la voyons pas ici, mais elle est de 5.
MATCH(G2,B1:E1,0) est la deuxième formule MATCH. Elle est très similaire à la première, mais elle recherche G2 (le mot " Green ") dans les en-têtes de colonne de B1:E1. Cette formule se résout en 3.
Nous pouvons maintenant réécrire la formule INDEX comme suit pour visualiser ce qui se passe : =INDEX(B2:E13,5,3). Cette formule recherche dans l'ensemble du tableau B2:E13 la cinquième ligne et la troisième colonne, ce qui donne 180 $.Règles MATCH et INDEX
Il y a plusieurs choses à garder à l’esprit lorsque vous écrivez des formules avec ces fonctions :
MATCH n’est pas sensible à la casse, les majuscules et les minuscules sont donc traitées de la même manière lors de la correspondance des valeurs de texte.
MATCH renvoie #N/A pour plusieurs raisons : si match_type est égal à 0 et si lookup_value n’est pas trouvé ; si match_type est égal à -1 et si lookup_array n’est pas dans l’ordre décroissant ; si match_type est égal à 1 et si lookup_array n’est pas dans l’ordre croissant ; et si lookup_array n’est pas une ligne ou une colonne unique.
Vous pouvez utiliser un caractère générique dans l’argument lookup_value si match_type est égal à 0 et si lookup_value est une chaîne de texte. Un point d’interrogation correspond à un caractère unique et un astérisque à une séquence de caractères (par exemple, =MATCH(« Jo* »,1:1,0)). Pour utiliser MATCH afin de trouver un point d’interrogation ou un astérisque, tapez d’abord ~.
INDEX renvoie #REF ! si row_num et column_num ne pointent pas vers une cellule du tableau.
Fonctions Excel connexes
La fonction MATCH est similaire à LOOKUP, mais MATCH renvoie la position de l’élément au lieu de l’élément lui-même.
VLOOKUP est une autre fonction de recherche que vous pouvez utiliser dans Excel, mais contrairement à MATCH qui nécessite INDEX pour les recherches avancées, les formules VLOOKUP ne nécessitent que cette seule fonction.