Recherche multicritères avec INDEX et EQUIV

Lorsque vous travaillez avec une grande base de données dans une feuille Excel comportant de nombreuses colonnes et lignes titrées, il est souvent difficile de trouver une entrée répondant à plusieurs critères. Dans ce cas, vous pouvez utiliser une formule matricielle combinant les fonctions INDEX et EQUIV.

Comment effectuer une recherche selon plusieurs critères ?

Pour identifier le produit qui est blanc, de taille moyenne et au prix de 18 $, comme illustré ci-dessus, vous pouvez utiliser la logique booléenne afin de générer un tableau composé de 1 et de 0 indiquant les lignes correspondant à tous les critères. La fonction EQUIV repère ensuite la position de la première ligne remplissant l’ensemble des conditions. Enfin, la fonction INDEX récupère l’identifiant du produit situé sur cette 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 nécessite d’être validée par Ctrl+Maj+Entrée.

Pour trouver le produit qui est blancet de taille moyenneau prix de 18 $, veuillez copier ou saisir la formule ci-dessous dans la cellule H8, puis appuyer sur Ctrl+Maj+Entréepour obtenir le résultat :

=INDEX()B5:B10;EQUIV(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;EQUIV(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 de la cellule H3 avec toutes les couleurs de la plage C5:C10, la taille de H5 avec toutes les tailles de D5:D10, et le prix de H6 avec tous les prix de 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 convertit les valeurs 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, on obtient un tableau unique comme suit :
  {0;0;0;0;1;0}.
• EQUIV(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=EQUIV(1,)Le type_correspondance 0 indique à la fonction EQUIV de rechercher une correspondance exacte. La fonction renvoie alors la position de 1 dans le tableau {0;0;0;0;1;0}, soit 5.
• INDEX()B5:B10,EQUIV(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10La fonction INDEX renvoie la 5^e valeur de la plage d’identifiants produits B5:B10, qui est 30005.

Fonctions associées

Formules associées