INDEX et EQUIV sur plusieurs colonnes

Pour rechercher une valeur en effectuant une correspondance sur plusieurs colonnes, une formule matricielle reposant sur les fonctions INDEX et EQUIV, combinée aux fonctions PRODUITMAT, TRANSPOSE et COLONNE, vous sera particulièrement utile.

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

Pour remplir la classe correspondante de chaque élève, comme indiqué dans le tableau ci-dessus, où les informations sont réparties sur plusieurs colonnes, commencez par utiliser une combinaison astucieuse des fonctions PRODUITMAT, TRANSPOSE et COLONNE afin de générer un tableau matriciel. La fonction EQUIV vous indiquera ensuite la position de la valeur recherchée, que vous transmettrez à la fonction INDEX pour récupérer la valeur souhaitée 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 doit être validée en appuyant sur Ctrl+Maj+Entrée.

Pour trouver la classe de Jimmy, copiez ou saisissez la formule ci-dessous dans la cellule H5, puis appuyez sur Ctrl+Maj+Entréepour obtenir le résultat :

=INDEX()$B$5:$B$7,(EQUIV(1,PRODUITMAT(--())))$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éplacerez ou copierez 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 recherchée, car vous souhaitez qu’elle reste relative lorsque vous la copiez vers d’autres cellules. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour l’appliquer aux cellules situées en 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 compare chaque valeur de la plage $C$5:$E$7 à celle de la cellule G5 et renvoie un tableau de VRAI et FAUX comme suit :
  {VRAI;FAUX;FAUX\FAUX;FAUX;FAUX\FAUX;FAUX;FAUX}.
  Les doubles signes moins transforment ensuite ces valeurs VRAI/FAUX en 1 et 0, ce qui donne un tableau tel que :
  {1,0,0;0,0,0;0,0,0}.
• COLONNE($C$5:$E$7):La fonction COLONNE renvoie les numéros de colonne de la plage $C$5:$E$7 sous forme d’un tableau comme suit : {3,4,5}.
• TRANSPOSE()COLONNE($C$5:$E$7)^0)=TRANSPOSE(){3,4,5}^0):Après avoir élevé chaque élément du tableau {3,4,5} à la puissance 0, tous les nombres deviennent 1, ce qui donne {1,1,1}. La fonction TRANSPOSE transforme ensuite ce tableau-ligne en tableau-colonne comme suit :{1;1;1}.
• PRODUITMAT()--($C$5:$E$7=G5),TRANSPOSE()COLONNE($C$5:$E$7)^0))=PRODUITMAT(){1,0,0;0,0,0;0,0,0},{1;1;1}):La fonction PRODUITMAT renvoie le produit matriciel des deux tableaux suivants : {1;0;0}.
• EQUIV(1,)PRODUITMAT()--($C$5:$E$7=G5),TRANSPOSE()COLONNE($C$5:$E$7)^0)),0)=EQUIV(1,){1;0;0},0): Le type_correspondance 0 oblige la fonction EQUIV à renvoyer la position de la première occurrence de 1 dans le tableau {1;0;0}, soit 1.
• INDEX()$B$5:$B$7,(EQUIV(1,))PRODUITMAT()--($C$5:$E$7=G5),TRANSPOSE()COLONNE($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7La fonction INDEX renvoie la 1re valeur de la plage des classes $B$5:$B$7, soit A.

Pour rechercher facilement une valeur en effectuant une correspondance sur plusieurs colonnes, vous pouvez également utiliser notre complément professionnel pour Excel Kutools pour Excel.Consultez ici les instructions pour accomplir cette tâche.

Fonctions associées

Formules associées