Recherche multicritères avec INDEX et MATCH

Lorsque vous travaillez avec une grande base de données dans une feuille de calcul Excel comportant plusieurs colonnes et légendes de lignes, il est toujours délicat 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.

Comment effectuer une recherche avec plusieurs critères ?

Pour trouver le produit qui est blanc, de taille moyenne et a un prix de 18 $ comme indiqué dans l'image ci-dessus, vous pouvez tirer parti de la logique booléenne pour générer un tableau de 1 et de 0 afin d'indiquer les lignes qui répondent aux critères. La fonction MATCH trouvera ensuite la position de la première ligne qui satisfait tous les critères. Après cela, INDEX trouvera l'ID du produit correspondant sur la même ligne.

Syntaxe générique

=INDEX(plage_retour,MATCH(1,(valeur_critère1=plage_critère1*valeur_critère2=plage_critère2*(…),0))

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

Pour trouver le produit qui est blanc, de taille moyenne et a un prix de 18 $, veuillez copier ou entrer la formule ci-dessous dans la cellule H8, puis appuyez sur Ctrl + Maj + Entrée pour obtenir le résultat :

=INDEX(B5:B10,MATCH(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,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

Explication de la formule

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

• (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): La formule compare la couleur dans la cellule h3 avec toutes les couleurs dans la plage C5:C10; compare la taille dans H5 avec toutes les tailles dans D5:D10; compare le prix dans H6 avec tous les prix dans E5:E10. Le résultat initial ressemble à 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 obtiendrons un seul tableau comme suit :
  {0;0;0;0;1;0}.
• MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): Le type_correspondance 0 demande à la fonction MATCH de trouver la correspondance exacte. La fonction retournera alors la position de 1 dans le tableau {0;0;0;0;1;0}, qui est 5.
• INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): La fonction INDEX retourne la 5ème valeur dans la plage d'ID de produit B5:B12, qui est 30005.

Fonctions connexes

Formules connexes