Comment utiliser les fonctions INDEX et MATCH dans Excel
ce qu’il faut savoir
- La fonction INDEX peut être utilisée seule, mais l’imbrication de la fonction MATCH dans celle-ci 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 complètement distinctes pouvant être utilisées individuellement, elles peuvent également être combinées pour créer des formules avancées.
La fonction INDEX renvoie une valeur ou une référence à une valeur d’une sélection spécifique. Par exemple, il peut être utilisé pour trouver une valeur dans la deuxième ligne ou la cinquième ligne et la troisième colonne d’un ensemble de données.
Bien que INDEX fonctionne bien seul, l’imbrication de MATCH dans une formule le rend plus utile. La fonction MATCH recherche un élément spécifié dans une plage de cellules et renvoie la position relative de l’élément dans la plage. Par exemple, il peut être utilisé pour déterminer qu’un nom particulier est le troisième élément d’une liste de noms.
Syntaxe et paramètres INDEX et MATCH
Pour qu’Excel les comprenne, les deux fonctions doivent être écrites comme ceci :
= indice(en grand nombre, Lignes, [column_num])
- en grand nombre est la plage de cellules que la formule utilisera. Il peut s’agir d’une ou plusieurs lignes et colonnes, telles que A1:D5. Ceci est nécessaire.
- Lignes est la ligne du tableau à partir de laquelle renvoyer la valeur, telle que 2 ou 18.c’est obligatoire sauf si num_colonne exister.
- num_colonne est la colonne du tableau à partir de laquelle renvoyer la valeur, telle que 1 ou 9. C’est facultatif.
= correspondre(valeur de recherche, trouver un tableau, [match_type])
- valeur de recherche est la valeur que vous voulez faire correspondre trouver un tableau. 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 nécessaire.
- trouver un tableau est la plage de cellules à afficher. Il peut s’agir d’une seule ligne ou d’une seule colonne, comme A2:D2 ou G1:G45. Ceci est nécessaire.
- type de jeu Boîte -1, 0ou 1Il précise comment valeur de recherche correspond à la valeur dans trouver un tableau (comme suit). 1 Si ce paramètre est omis, la valeur par défaut.
quel type de correspondance utiliser | |||
---|---|---|---|
type de jeu | ce qu’il peut faire | régner | Exemple |
1 | Trouver la plus grande valeur inférieure ou égale à valeur de recherche. | Cette trouver un tableau Les valeurs doivent être dans l’ordre croissant (par exemple, -2, -1, 0, 1, 2 ; ou AZ ; ou FALSE, TRUE. | valeur de recherche c’est 25 mais ça va de trouver un tableauil renvoie donc la position du plus petit nombre suivant (tel que 22). |
0 | Trouver la première valeur qui est exactement égale à valeur de recherche. | Cette trouver un tableau Les valeurs peuvent être dans n’importe quel ordre. | valeur de recherche est 25, il renvoie donc la position 25. |
-1 | Trouver la plus petite valeur supérieure ou égale à valeur de recherche. | Cette trouver un tableau Les valeurs doivent être dans l’ordre décroissant (par exemple, 2, 1, 0, -1, -2). | valeur de recherche c’est 25 mais ça va de trouver un tableauil renvoie donc la position du nombre le plus grand suivant, tel que 34. |
utilisation 1 ou -1 Il y a des moments où vous devez exécuter des recherches approximatives le long d’échelles, par exemple lorsque vous travaillez avec des nombres et lorsque les approximations sont correctes.Mais gardez à l’esprit que si vous ne spécifiez pas type de jeu, 1 sera la valeur par défaut, cela peut fausser le résultat 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 indépendamment.
Exemple d’index
=indice(A1:B2,2,2)
= indice(A1:B1,1)
=indice(2:2,1)
= indice(B1:B2,1)
Dans le premier exemple, nous pouvons utiliser quatre formules INDEX pour obtenir différentes valeurs :
- =indice(A1:B2,2,2) Trouvez la valeur dans la deuxième colonne et la deuxième ligne par A1: B2, c’est-à-dire Stacey.
- = indice(A1:B1,1) Trouvez la valeur dans la première colonne par A1:B1, c’est-à-dire Jon.
- =indice(2:2,1) Regardez tout dans la deuxième ligne pour trouver la valeur dans la première colonne, c’est-à-dire Tim.
- = indice(B1:B2,1) Trouvez la valeur dans la première ligne par B1: B2, c’est-à-dire Amy.
exemple de match
=EQUIV(« 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 :
- =EQUIV(« Stacy »,A2:D2,0) recherche Stacey dans le cadre A2:D2 et retour 3 par conséquent.
- =match(14,D1:D2) recherche 14 dans le cadre D1:D2mais comme il n’a pas été trouvé dans la table, MATCH trouve le prochain inférieur ou égal à la valeur maximale 14Dans ce cas c’est 13en position 1 de trouver un tableau.
- =match(14,D1:D2,-1) Même formule que ci-dessus, mais puisque le tableau n’est pas dans l’ordre décroissant -1 demande, nous obtenons une erreur.
- =match(13,A1:D1,0) à la recherche de 13 Sur la première ligne de la feuille de calcul, retournez 4 Parce que c’est le quatrième élément de ce tableau.
Exemple de correspondance d’index
Voici deux exemples où nous pouvons combiner INDEX et MATCH dans une seule formule :
Rechercher des références de cellule dans un tableau
=index(B2:B5,correspondance(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 la ligne « séparée » comment les formules sont écrites elles-mêmes, mais puisque nous les imbriquons, voici ce qui se passe :
- Course (F1, A2:A5) recherche F1 Valeurs dans le jeu de données (8795) A2: A5Si on décompte les colonnes, on voit qu’il est 2c’est donc ce que la fonction MATCH vient de découvrir.
- Le tableau indexé est B2:B5 Parce que nous recherchons finalement la valeur dans cette colonne.
- La fonction INDEX peut maintenant être réécrite comme ceci car 2 Il a été trouvé par MATCH : indice(B2:B5,2, [column_num]).
- puisque num_colonne est facultatif, nous pouvons le supprimer pour conserver les éléments suivants : indice(B2:B5,2).
- Alors maintenant, c’est comme une formule INDEX normale où nous trouvons la valeur du deuxième terme B2:B5,À l’heure actuelle 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 MATCH et INDEX, nous effectuons une recherche bidirectionnelle.L’idée est de voir combien d’argent nous gagnons vert articles dans possibleCeci est très similaire à l’exemple ci-dessus, mais avec une formule MATCH supplémentaire imbriquée dans l’INDEX.
- match(G1, A2:A13, 0) est le premier terme résolu dans cette formule.c’est à la recherche G1 (le mot « mai ») dans A2: A13 obtenir une valeur spécifique.On ne le voit pas ici, mais c’est 5.
- match(G2,B1:E1,0) est la deuxième formule MATCH, qui est très similaire à la première, mais qui recherche G2 (le mot « vert ») dans l’en-tête de colonne B1:E1. cela résout 3.
- Nous pouvons maintenant réécrire la formule INDEX comme ceci pour visualiser ce qui se passe : =indice(B2:E13,5,3)C’est regarder toute la table, B2:E13pour la cinquième ligne et la troisième colonne, renvoie 180 $.
Règles MATCH et INDEX
Il y a quelques points à garder à l’esprit lors de l’écriture de formules à l’aide de 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.
- retour de jeu #N’est pas applicable Il y a plusieurs raisons : si type de jeu Oui 0 et valeur de recherche si non trouvé type de jeu Oui -1 et trouver un tableau pas dans l’ordre décroissant, si type de jeu Oui 1 et trouver un tableau pas dans l’ordre croissant, si trouver un tableau Pas une seule ligne ou colonne.
- tu peux valeur de recherche argument si type de jeu Oui 0 et valeur de recherche 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 exemple, =EQUIV(« Joe* »,1:1,0)). Pour utiliser MATCH pour trouver le point d’interrogation ou l’astérisque, commencez par taper ~.
- Rendement de l’indice #faire référence à! si Lignes et num_colonne Ne pointez pas sur une cellule d’un tableau.
Fonctions Excel associées
La fonction MATCH est similaire à RECHERCHE, mais MATCH renvoie Emplacement article plutôt que l’article 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.
Merci de nous en informer!
Recevez les dernières nouvelles technologiques publiées quotidiennement
abonnement
Dites-nous pourquoi !
D’autres détails ne sont pas assez difficiles à comprendre