Recherche dans le tableau 2d en utilisant la fonction INDEX et MATCH
Dans cet article, nous allons apprendre à rechercher des valeurs dans une table 2D à l’aide d’une fonction INDEX-MATCH-MATCH dans Excel.
Scénario:
Supposons que vous deviez effectuer plusieurs recherches à partir d’une table comportant des centaines de colonnes. Dans de tels cas, l’utilisation de formules différentes pour chaque recherche prendra trop de temps. Que diriez-vous de créer une formule de recherche dynamique que vous pouvez rechercher par l’en-tête fourni. Oui, nous pouvons le faire.
Cette formule est appelée formule INDEX MATCH MATCH, ou disons une formule de recherche 2D.
Comment résoudre le problème?
Pour que la formule comprenne d’abord, nous devons réviser un peu les fonctions suivantes. lien: / lookup-formulas-excel-index-function [fonction INDEX]
-
lien: / lookup-formulas-excel-match-function [fonction MATCH]
link: / lookup-formulas-excel-index-function [La fonction INDEX] renvoie la valeur à un index donné dans un tableau.
link: / lookup-formulas-excel-match-function [fonction MATCH] retourne l’index de la première apparition de la valeur dans un tableau (tableau à une seule dimension).
Nous allons maintenant créer une formule en utilisant les fonctions ci-dessus. La fonction de correspondance renverra l’index de la valeur de recherche1 dans le champ d’en-tête de ligne. Et une autre fonction MATCH renvoie l’index de la valeur de recherche2 dans le champ d’en-tête de colonne. Les numéros d’index seront maintenant introduits dans la fonction INDEX pour obtenir les valeurs sous la valeur de recherche à partir des données de la table 2D.
Formule générique:
= INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) )
Data: tableau de valeurs à l’intérieur de la table sans en-têtes lookup_value1: valeur à rechercher dans le row_header.
row_headers: tableau d’index de ligne à rechercher.
lookup_value1: valeur à rechercher dans l’en-tête de colonne.
column_headers: tableau d’index de colonne à rechercher.
Exemple:
Les déclarations ci-dessus peuvent être compliquées à comprendre. Alors, comprenons cela en utilisant la formule dans un exemple. Nous avons ici une liste des scores obtenus par les élèves avec leur liste de matières. Nous devons trouver le score d’un étudiant (Gary) et d’un sujet (sciences sociales) spécifiques, comme indiqué dans l’instantané ci-dessous.
La valeur Student1 doit correspondre au tableau Row_header et Subject value2 doit correspondre au tableau Column_header.
Utilisez la formule dans la cellule J6:
= INDEX ( table , MATCH ( J5, row, 0 , MATCH ( J4, column, 0 ) ) )
Explication:
La fonction MATCH correspond à la valeur Student dans la cellule J4 avec le tableau d’en-tête de ligne et renvoie sa position 3 * sous forme de nombre.
La fonction MATCH correspond à la valeur Subject de la cellule J5 avec le tableau d’en-tête de colonne et renvoie sa position 4 * sous forme de nombre.
-
La fonction INDEX prend le numéro d’index de ligne et de colonne et recherche dans les données de la table et renvoie la valeur correspondante.
-
L’argument de type MATCH est fixé à 0. Comme la formule extraira la correspondance exacte.
Ici, les valeurs de la formule sont données sous forme de références de cellule et row_header, table et column_header sont donnés sous forme de plages nommées.
Comme vous pouvez le voir dans l’instantané ci-dessus, nous avons obtenu le score obtenu par l’étudiant Gary en études sociales par sujet à 36.
Cela prouve que la formule fonctionne bien et en cas de doute, consultez les notes ci-dessous pour la compréhension.
Nous allons maintenant utiliser la correspondance approximative avec les en-têtes de ligne et les en-têtes de colonne sous forme de nombres. La correspondance approximative ne prend que les valeurs numériques car il n’y a aucun moyen qu’elle s’applique aux valeurs textuelles. Ici, nous avons un prix des valeurs selon la hauteur et la largeur du produit.
Nous devons trouver le prix pour une hauteur (34) et une largeur (21) spécifiques, comme indiqué dans l’instantané ci-dessous.
La valeur Height1 doit correspondre au tableau Row_header et la valeur Width2 doit correspondre au tableau Column_header.
Utilisez la formule dans la cellule K6:
= INDEX (data , MATCH (K4, Height, 1 , MATCH ( K5, Width, 1 ) ) )
Explication:
La fonction MATCH correspond à la valeur Hauteur dans la cellule K4 avec le tableau d’en-tête de ligne et renvoie sa position 3 * sous forme de nombre.
La fonction MATCH correspond à la valeur Width dans la cellule K5 avec le tableau d’en-tête de colonne et renvoie sa position 2 * sous forme de nombre.
-
La fonction INDEX prend le numéro d’index de ligne et de colonne et recherche dans les données de la table et renvoie la valeur correspondante.
-
L’argument de type MATCH est fixé à 1. Comme la formule extraira la correspondance approximative.
Ici, les valeurs de la formule sont données sous forme de références de cellule et row_header, data et column_header sont donnés sous forme de plages nommées comme indiqué dans l’instantané ci-dessus.
Comme vous pouvez le voir dans l’instantané ci-dessus, nous avons le prix obtenu par hauteur (34) et largeur (21) comme 53.10. Cela prouve que la formule fonctionne bien et en cas de doute, consultez les notes ci-dessous pour plus de compréhension.
Remarques:
-
La fonction renvoie l’erreur #NA si l’argument du tableau de recherche de la fonction MATCH est un tableau 2 D qui est le champ d’en-tête des données.
-
La fonction correspond à la valeur exacte car l’argument du type de correspondance à la fonction MATCH est 0.
-
Les valeurs de recherche peuvent être données comme référence de cellule ou directement en utilisant le symbole guillemet (« ) dans la formule comme arguments.
J’espère que vous avez compris comment utiliser la table de recherche en 2D à l’aide de la fonction INDEX & MATCH dans Excel. En savoir plus articles dans la valeur de recherche Excel ici. N’hésitez pas à indiquer vos questions ci-dessous dans la zone de commentaire.
Nous vous aiderons certainement.
Articles connexes
link: / lookup-formulas-use-index-and-match-to-lookup-value [Utiliser INDEX et MATCH pour rechercher la valeur]: Fonction INDEX & MATCH pour rechercher la valeur selon les besoins.
` link: / summing-sum-range-with-index-in-excel [SUM range with INDEX in Excel] `: Utilisez la fonction INDEX pour trouver la somme des valeurs selon les besoins.
` link: / counting- excel-sum-function [Comment utiliser la fonction SUM dans Excel] `: Trouvez la somme des nombres à l’aide de la fonction SUM expliquée avec l’exemple.
` link: / lookup-formulas-excel-index-function [Comment pour utiliser la fonction INDEX dans Excel] `: Trouvez l’INDEX du tableau en utilisant la fonction INDEX expliquée avec l’exemple.
link: / lookup-formulas-excel-match-function [Comment utiliser la fonction MATCH dans Excel]: Trouvez la MATCH dans le tableau en utilisant la valeur INDEX dans la fonction MATCH expliquée avec un exemple.
link: / lookup-formulas-excel-lookup-function [Comment utiliser la fonction LOOKUP dans Excel]: Trouvez la valeur de recherche dans le tableau en utilisant la fonction LOOKUP expliquée avec un exemple.
link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser la fonction RECHERCHEV dans Excel]: Trouvez la valeur de recherche dans le tableau en utilisant la fonction RECHERCHEV expliquée par l’exemple.
link: / lookup-formulas-hlookup-function-in-excel [Comment utiliser la fonction RECHERCHEH dans Excel]: Trouvez la valeur de recherche dans le tableau à l’aide de la fonction RECHERCHEH expliquée par l’exemple.
Articles populaires
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourci Excel pour augmenter votre productivité]
link: / excel-generals-how-to-edit-a-dropdown-list-in-microsoft-excel [Edit a dropdown list]
lien: / excel-range-name-absolu-reference-in-excel [Référence absolue dans Excel]
lien: / conseils-mise en forme-conditionnelle-avec-instruction-if [Si avec mise en forme conditionnelle]
lien: / formules-logiques-si-fonction-avec-jokers [Si avec jokers]
lien: / lookup-formulas-vlookup-by-date-in-excel [Vlookup by date]
lien: / excel-édition-de-texte-et-format-join-prénom-et-nom-dans-excel [Joindre le nom et le prénom dans Excel]