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 the INDEX et MATCH fonctions.

Comment effectuer une recherche avec plusieurs critères ?

Pour découvrir le Les produits qui est blanc et moyenne-taille avec un prix de $18 comme le montre l'image ci-dessus, vous pouvez profiter de la logique booléenne pour générer un tableau de 1 et de 0 pour afficher les lignes qui répondent aux critères. La fonction MATCH trouvera alors la position de la première ligne répondant à tous les critères. Après cela, INDEX trouvera l'ID de produit correspondant sur la même ligne.

Syntaxe générique

=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ Remarque : Il s'agit d'une formule matricielle qui vous oblige à entrer avec Ctrl + Shift + Entrer.

Pour trouver le produit qui blanc et moyenne-taille avec un prix de $18, veuillez copier ou saisir la formule ci-dessous dans la cellule H8, et appuyez sur Ctrl + Shift + Entrer pour obtenir le résultat :

= INDEX (B5: B10, CORRESPONDANCE(1,("Blanc"=C5: C10) * ("Moyen"=D5: D10) * (18=E5: E10),0))

Ou utilisez une référence de cellule pour rendre la formule dynamique :

= INDEX (B5: B10, CORRESPONDANCE(1,(H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10),0))

Explication de la formule

=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): La formule compare la couleur dans la cellule H4 contre toutes les couleurs de la gamme C5: C10; compare la taille dans H5 contre toutes les tailles dans D5: D10; compare le prix dans H6 contre tous les prix en E5: E10. Le résultat initial est comme ceci :
  {VRAI;FAUX;VRAI;FAUX;VRAI;FAUX}*{FAUX;FAUX;VRAI;VRAI;VRAI;FAUX}*{FAUX;FAUX;FAUX;VRAI;VRAI;FAUX}.
  La multiplication transformera les VRAI et FAUX en 1 et 0 :
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  Après multiplication, nous aurons un seul tableau comme celui-ci :
  {0;0;0;0;1;0}.
• CORRESPONDANCE(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = CORRESPONDANCE(1,{0;0;0;0;1;0}, 0): Le match_type 0 demande à la fonction MATCH de trouver la correspondance exacte. La fonction renverra alors la position de 1 dans le tableau {0;0;0;0;1;0}, lequel est 5.
• INDICE(B5: B10,CORRESPONDANCE(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = INDICE(B5: B10,5): La fonction INDEX renvoie le 5ème valeur dans la plage d'ID de produit B5: B10, lequel est 30005.

Fonctions connexes

Formules associées