INDEX et MATCH sur plusieurs colonnes

Pour rechercher une valeur en effectuant une correspondance sur plusieurs colonnes, une formule matricielle basée sur les fonctions INDEX et MATCH qui incorpore MMULT, TRANSPOSE, et COLONNE vous sera utile.

Comment rechercher une valeur en effectuant une correspondance sur plusieurs colonnes ?

Pour remplir la classe correspondante de chaque étudiant comme indiqué dans le tableau ci-dessus, où les informations sont réparties sur plusieurs colonnes, vous pouvez d'abord utiliser l'astuce des fonctions MMULT, TRANSPOSE et COLONNE 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(plage_retour,(MATCH(1,MMULT(--(tableau_recherche=valeur_recherche),TRANSPOSE(COLONNE(tableau_recherche)^0)),0)))

√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être validée avec Ctrl + Maj + Entrée.

Pour trouver la classe de Jimmy, veuillez copier ou saisir la formule ci-dessous dans la cellule H5, puis appuyez sur Ctrl + Maj + Entrée pour obtenir le résultat :

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLONNE($C$5:$E$7)^0)),0)))

√ Remarque : Les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que les plages de noms et de classes dans la formule ne changeront pas lorsque vous déplacez ou copiez la formule vers d'autres cellules. Notez que vous ne devez pas ajouter de signes dollar à la référence de cellule représentant la valeur de recherche, car vous voulez qu'elle soit relative lorsque vous la copiez dans d'autres cellules. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour appliquer la formule aux cellules situées en dessous.

Explication de la formule

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLONNE($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5) : Ce segment vérifie chaque valeur dans la plage $C$5:$E$7 si elles sont égales à la valeur dans la cellule G5, et génère un tableau VRAI et FAUX comme ceci :
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  Le double négatif convertira ensuite les VRAI et FAUX en 1 et 0 pour produire un tableau comme ceci :
  {1,0,0;0,0,0;0,0,0}.
• COLONNE($C$5:$E$7): La fonction COLONNE renvoie les numéros de colonne pour la plage $C$5:$E$7 dans un tableau comme ceci : {3,4,5}.
• TRANSPOSE(TRANSPOSE(COLONNE($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): Après avoir élevé à 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 ceci : {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(COLONNE($C$5:$E$7)^0))) = MMULT(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}.
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLONNE($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): Le type_correspondance 0 force la fonction MATCH à retourner la position de la première correspondance de 1 dans le tableau {1;0;0}, qui est 1.
• INDEX($B$5:$B$7$B$5:$B$7,,,(MATCH(1,MMULT(--($C$5:$E$7=G5),,,TRANSPOSE(COLONNE($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7$B$5:$B$7,,,1) : La fonction INDEX retourne la première valeur dans la plage de classes $B$5:$B$7, qui est A.

Pour rechercher facilement une valeur en effectuant une correspondance sur plusieurs colonnes, vous pouvez également utiliser notre add-in Excel professionnel Kutools pour Excel. Consultez les instructions ici pour accomplir cette mission.

Fonctions connexes

Formules connexes