INDEX et MATCH sur plusieurs colonnes
Pour rechercher une valeur en faisant correspondre plusieurs colonnes, une formule matricielle basée sur le INDEX et des tours MATCH fonctions qui intègrent MMULT, TRANSPOSER et des tours COLONNE vous fera une faveur.
Comment rechercher une valeur en faisant correspondre plusieurs colonnes ?
Pour remplir le classe correspondante de chaque élève comme le montre le tableau ci-dessus, où le les informations sont répertoriées sur plusieurs colonnes, vous pouvez d'abord utiliser l'astuce des fonctions MMULT, TRANSPOSE et COLUMN pour produire un tableau matriciel. Ensuite, la fonction MATCH vous donnera la position de votre valeur de recherche, qui sera transmise à INDEX pour récupérer la valeur que vous recherchez dans le tableau.
Syntaxe générique
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Remarque : Il s'agit d'une formule matricielle qui vous oblige à entrer avec Ctrl + Mission + Entrer.
- plage_retour : La plage à partir de laquelle vous souhaitez que la formule renvoie les informations de classe. Ici se réfère à la plage de classe.
- valeur_recherche : La valeur que la formule a utilisée pour trouver les informations de classe correspondantes. Ici se réfère au prénom.
- tableau_recherche : La plage de cellules où le valeur de recherche est listé; La plage avec les valeurs à comparer avec le valeur de recherche. Ici se réfère à la plage de noms.
- match_type 0 : Force MATCH à trouver la première valeur exactement égale à la valeur de recherche.
Pour trouver le classe de Jimmy, veuillez copier ou saisir la formule ci-dessous dans la cellule H5, et appuyez sur Ctrl + Mission + Entrer pour obtenir le résultat :
= INDEX (5 B $: 7 B $,(EQUIV(1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(COLONNE($C$5:$E$7)^0)),0))))
√ Remarque : Les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que le nom et les plages de classe dans la formule ne changeront pas lorsque vous déplacerez ou copierez la formule dans d'autres cellules. Notez que vous ne devez pas ajouter de signe dollar à la référence de cellule qui représente la valeur de recherche, car vous souhaitez qu'elle soit relative lorsque vous la copiez dans d'autres cellules. Après avoir entré la formule, faites glisser la poignée de remplissage vers le bas pour appliquer la formule aux cellules ci-dessous.
Explication de la formule
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --($C$5:$E$7=G5): Ce segment vérifie chaque valeur de la plage $C$5:$E$7 s'ils sont égaux à la valeur de la cellule G5, et génère un tableau VRAI et FAUX comme celui-ci :
{VRAI,FAUX,FAUX;FAUX,FAUX,FAUX;FAUX,FAUX,FAUX}.
Le double négatif convertira ensuite les VRAI et FAUX en 1 et 0 pour produire un tableau comme celui-ci :
{1,0,0; 0,0,0; 0,0,0}. - COLONNE($C$5:$E$7): La fonction COLUMN renvoie les numéros de colonne pour la plage $C$5:$E$7 dans un tableau comme celui-ci : 3,4,5 {}.
- TRANSPOSER(COLONNE($C$5:$E$7)^0) = TRANSPOSER(3,4,5 {}^0): Après avoir augmenté la puissance à 0, tous les nombres du tableau {3,4,5} seront convertis en 1 : {1,1,1}. La fonction TRANSPOSE convertit ensuite le tableau de colonnes en un tableau de lignes comme celui-ci : {1; 1; 1}.
- MMULT(--($C$5:$E$7=G5),TRANSPOSER(COLONNE($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): La fonction MMULT renvoie le produit matriciel des deux tableaux comme ceci : {1; 0; 0}.
- CORRESPONDANCE(1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(COLONNE($C$5:$E$7)^0)), 0) = CORRESPONDANCE(1,{1; 0; 0}, 0): Le match_type 0 force la fonction MATCH à renvoyer la position de la première correspondance de 1 dans le tableau {1; 0; 0}, lequel est 1.
- INDICE(5 B $: 7 B $,(CONCORDANCE(1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(COLONNE($C$5:$E$7)^0)), 0))) = INDEX(5 B $: 7 B $,1): La fonction INDEX renvoie le 1valeur st dans la plage de classe 5 B $: 7 B $, lequel est A.
Pour rechercher facilement une valeur en faisant correspondre plusieurs colonnes, vous pouvez également utiliser notre complément professionnel Excel Kutools pour Excel. Voir les instructions ici pour accomplir la mission.
Fonctions connexes
La fonction Excel INDEX renvoie la valeur affichée en fonction d'une position donnée dans une plage ou un tableau.
La fonction Excel MATCH recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de la valeur.
La fonction Excel MMULT renvoie le produit matriciel de deux tableaux. Le résultat du tableau a le même nombre de lignes que array1 et le même nombre de colonnes que array2.
La fonction Excel TRANSPOSE fait pivoter l'orientation d'une plage ou d'un tableau. Par exemple, il peut faire pivoter un tableau disposé horizontalement en lignes vers verticalement en colonnes ou vice versa.
La fonction COLONNE renvoie le numéro de colonne dont la formule apparaît ou renvoie le numéro de colonne d'une référence donnée. Par exemple, la formule =COLONNE(BD) renvoie 56.
Formules associées
Recherche multicritères avec INDEX et MATCH
Lorsqu'il s'agit d'une grande base de données dans une feuille de calcul Excel avec plusieurs colonnes et légendes de lignes, il est toujours difficile de trouver quelque chose qui répond à plusieurs critères. Dans ce cas, vous pouvez utiliser une formule matricielle avec les fonctions INDEX et MATCH.
Recherche bidirectionnelle avec INDEX et MATCH
Pour rechercher quelque chose dans les lignes et les colonnes dans Excel, ou nous disons pour rechercher une valeur à l'intersection de la ligne et de la colonne spécifiques, nous pouvons utiliser l'aide des fonctions INDEX et MATCH.
Rechercher la valeur de correspondance la plus proche avec plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la valeur de correspondance la plus proche ou approximative en fonction de plusieurs critères. Avec la combinaison des fonctions INDEX, MATCH et IF, vous pouvez le faire rapidement dans Excel.
Les meilleurs outils de productivité de bureau
Kutools for Excel - vous aide à vous démarquer de la foule
Souhaitez-vous terminer votre travail quotidien rapidement et parfaitement? Kutools for Excel apporte 300 fonctionnalités avancées puissantes (Combiner les classeurs, somme par couleur, diviser le contenu des cellules, convertir la date, etc.) et gagner 80% de temps pour vous.
- Conçu pour 1500 scénarios de travail, vous aide à résoudre 80% des problèmes Excel.
- Réduisez les milliers de clics de clavier et de souris chaque jour, soulagez vos yeux et vos mains fatigués.
- Devenez un expert Excel en 3 minutes. Plus besoin de se souvenir des formules douloureuses et des codes VBA.
- Essai gratuit illimité de 30 jours. Garantie de remboursement de 60 jours. Mise à jour et assistance gratuites pendant 2 ans.
Onglet Office - Activer la lecture et la modification par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts!
- Réduisez chaque jour des centaines de clics de souris, dites adieu à la souris.
- Augmente votre productivité de 50% lors de l'affichage et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Firefox et le nouvel Internet Explorer.
