Comment utiliser la fonction INDEX et MATCH dans Excel

2024 Auteur: Abigail Brown | [email protected]. Dernière modifié: 2023-12-17 06:47

Ce qu'il faut savoir

La fonction INDEX peut être utilisée seule, mais l'imbrication de la fonction MATCH à l'intérieur crée une recherche avancée.
Cette fonction imbriquée est plus flexible que VLOOKUP et peut produire des résultats plus rapidement.

Cet article explique comment utiliser les fonctions INDEX et MATCH ensemble dans toutes les versions d'Excel, y compris Excel 2019 et Microsoft 365.

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 pouvant ê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 à partir d'une sélection particulière. Par exemple, il peut être utilisé pour trouver la valeur dans la deuxième ligne d'un ensemble de données, ou dans la cinquième ligne et la troisième colonne.

Bien que INDEX puisse 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 l'élément dans la plage. Par exemple, il peut être utilisé pour déterminer qu'un nom spécifique est le troisième élément d'une liste de noms.

INDEX et MATCH Syntaxe & Arguments

Voici comment les deux fonctions doivent être écrites pour qu'Excel les comprenne:

=INDEX(array, row_num, [column_num])

array est la plage de cellules que la formule utilisera. Il peut s'agir d'une ou plusieurs lignes et colonnes, telles que A1:D5. C'est obligatoire.
row_num est la ligne du tableau à partir de laquelle renvoyer une valeur, telle que 2 ou 18. Elle est obligatoire sauf si column_num est présent.
column_num est la colonne du tableau à partir de laquelle renvoyer une valeur, telle que 1 ou 9. C'est facultatif.

=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 saisie manuellement ou référencée via une référence de cellule. Ceci est obligatoire.
lookup_array est la plage de cellules à parcourir. Il peut s'agir d'une seule ligne ou d'une seule colonne, comme A2:D2 ou G1:G45. Ceci 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.

Quel type de correspondance utiliser
Type de correspondance	Ce qu'il fait	Règle	Exemple
1	Recherche la plus grande valeur inférieure ou égale à lookup_value.	Les valeurs de lookup_array doivent être placées dans l'ordre croissant (par exemple, -2, -1, 0, 1, 2; ou A-Z;, ou FALSE, TRUE.	lookup_value est 25 mais il manque dans lookup_array, donc la position du plus petit nombre suivant, comme 22, est renvoyée à la place.
0	Trouve la première valeur exactement égale à lookup_value.	Les valeurs de lookup_array peuvent être dans n'importe quel ordre.	lookup_value est 25, il renvoie donc la position de 25.
-1	Trouve la plus petite valeur supérieure ou égale à lookup_value.	Les valeurs de lookup_array doivent être placées dans l'ordre décroissant (par exemple, 2, 1, 0, -1, -2).	lookup_value est 25 mais il manque dans lookup_array, donc la position du prochain plus grand nombre, comme 34, est renvoyée à la place.

Utilisez 1 ou -1 lorsque vous avez besoin d'effectuer une recherche approximative le long d'une échelle, comme lorsque vous traitez avec des nombres et lorsque les approximations sont acceptables. Mais rappelez-vous que si vous ne spécifiez pas match_type, 1 sera la valeur par défaut, ce qui peut fausser les résultats si vous voulez vraiment une correspondance exacte.

Exemple de formules INDEX et MATCH

Avant de voir comment combiner INDEX et MATCH en une seule formule, nous devons comprendre comment ces fonctions fonctionnent par elles-mêmes.

INDEX Exemples

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2, 1)

Dans ce premier exemple, il y a quatre formules INDEX que nous pouvons utiliser pour obtenir différentes valeurs:

=INDEX(A1:B2, 2, 2) parcourt A1:B2 pour trouver la valeur dans la deuxième colonne et la deuxième ligne, qui est Stacy.
=INDEX(A1:B1, 1) parcourt A1:B1 pour trouver la valeur dans la première colonne, qui est Jon.
=INDEX(2:2, 1) regarde tout dans la deuxième rangée pour localiser la valeur dans la première colonne, qui est Tim.
=INDEX(B1:B2, 1) parcourt B1:B2 pour localiser la valeur dans la première ligne, qui est Amy.

MATCH Exemples

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

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) recherche 14 dans la plage D1:D2, mais comme il n'est pas trouvé dans le tableau, MATCH trouve la prochaine valeur la plus grande c'est inférieur ou égal à 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 puisque le tableau n'est pas dans l'ordre décroissant comme -1 l'exige, nous obtenons une erreur.
=MATCH(13, A1:D1, 0) recherche 13 dans la première ligne de la feuille, qui renvoie 4 puisqu'il s'agit du quatrième élément de ce tableau.

INDEX-MATCH Exemples

Voici deux exemples où nous pouvons combiner INDEX et MATCH dans une seule formule:

Trouver la référence de cellule dans le tableau

=INDEX(B2:B5, MATCH(F1, A2:A5))

Cet exemple imbrique la formule MATCH 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 par elles-mêmes, mais puisque nous les imbriquons, voici ce qui se passe:

MATCH(F1, A2:A5) recherche la valeur F1 (8795) dans le jeu de données A2:A5. Si nous décomptons la colonne, nous pouvons voir que c'est 2, c'est donc 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, [column_num]).
Étant donné que column_num est facultatif, nous pouvons le supprimer pour qu'il reste ceci: INDEX(B2:B5, 2).
Alors maintenant, c'est comme une formule INDEX normale où nous trouvons la valeur du deuxième élément de B2:B5, qui est rouge.

Rechercher par en-têtes de ligne et de colonne

=INDEX(B2:E13, CORRESPONDANCE(G1, A2:A13, 0), CORRESPONDANCE(G2, B1:E1, 0))

Dans cet exemple de MATCH et INDEX, nous effectuons une recherche bidirectionnelle. L'idée est de voir combien d'argent nous avons gagné avec les articles verts en mai. Ceci est vraiment similaire à l'exemple ci-dessus, 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. Il recherche G1 (le mot "May") dans A2:A13 pour obtenir une valeur particulière. Nous ne le voyons pas ici, mais c'est 5.
MATCH(G2, B1:E1, 0) est la deuxième formule MATCH, et elle est vraiment similaire à la première mais recherche plutôt G2 (le mot "vert") dans les en-têtes de colonne à B1:E1. Celui-ci se résout en 3.
Nous pouvons maintenant réécrire la formule INDEX comme ceci pour visualiser ce qui se passe: =INDEX(B2:E13, 5, 3). Il s'agit de rechercher dans l'ensemble du tableau, B2:E13, la cinquième ligne et la troisième colonne, qui renvoie $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, donc les lettres majuscules et minuscules sont 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 0 et lookup_value n'est pas trouvé si match_type est -1 et lookup_array n'est pas dans l'ordre décroissant, si match_type est 1 et lookup_array n'est pas dans l'ordre croissant order, et si lookup_array n'est pas une seule ligne ou colonne.
Vous pouvez utiliser un caractère générique dans l'argument lookup_value si match_type est 0 et lookup_value est une chaîne de texte. Un point d'interrogation correspond à n'importe quel caractère unique et un astérisque correspond à n'importe quelle séquence de caractères (par ex.par exemple, =MATCH("Jo", 1:1, 0)). Pour utiliser MATCH pour trouver un vrai 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 associées

La fonction MATCH est similaire à RECHERCHE, 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 n'ont besoin que de cette fonction.