INDEX EQUIV avec plusieurs critères

Résumé

Pour rechercher des valeurs avec INDEX + EQUIV tout en appliquant plusieurs critères de recherche, il faudra utiliser une formule matricielle. Pour valider cette formule si les versions d’Excel antérieures à Office 365 il faut utiliser Ctrl+Shift+Entrée.

= INDEX( C3:C7 ; 
    EQUIV( 1 ; (I3<D3:D7)*(I2=B3:B7) ; 0 )
  )

Cette formule permet de récupérer la valeur du stock de la première chaise qui a une quantité supérieure à 155 unités.

Formule Excel – INDEX EQUIV avec plusieurs critères

Avec 2 critères
= INDEX( plage_résultat ; 
    EQUIV( 1 ; 
      (critère_1=plage_critère_1)*
      (critère_2=plage_critère_2) ; 
    0 )
  )

Avec 3 critères
= INDEX( plage_résultat ; 
    EQUIV( 1 ; 
      (critère_1=plage_critère_1)*
      (critère_2=plage_critère_2)*
      (critère_3=plage_critère_3) ; 
    0 )
  )

Explications

La solution INDEX-EQUIV permet de rechercher une valeur dans un tableau en fonction d’une autre valeur connue.

La fonction EQUIV permet de récupérer la position d’une valeur dans une matrice donnée. La fonction INDEX renvoie la valeur située dans un tableau grâce à sa position à l’intérieur de celui-ci.

= EQUIV( valeur_cherchée ; matrice ; type_recherche )
= EQUIV( "Bureau 1" ; B3:B7 ; 0 )
--> On récupère la position de "bureau 1" dans B3:B7
    Résultat : 2

= INDEX( matrice ; no_ligne ; [no_colonne] )
= INDEX( E3:E7 ; 2 )
--> On récupère la valeur unitaire dans la matrice E3:E7
    grâce à la position identifiée grâce à la fonction EQUIV
    Résultat : 220,16

Normalement, la solution INDEX + EQUIV ne permet pas l’utilisation de plusieurs critères. Il est possible de contourner le problème en créer une colonne qui concatène les différentes « colonnes-critères ». Dans notre cas, ça ne résoudrait pas notre problème puisque nous voulons indiquer des égalités :

I2=B3:B7

Et des inégalités :

I3<D3:D7

La solution vient de l’utilisation d’une formule matricielle. On va créer un tableau composé de 0 et 1 basé sur les critères souhaités. Si la valeur 1 est indiquée, c’est que tous les critères ont été remplis.

= (155<D3:D7)*("chaise"=B3:B7)

La fonction EQUIV viendra alors récupérer la position du premier « 1 » et le tout sera intégré dans la fonction INDEX.

= EQUIV( 1 ; (155<D3:D7)*("chaise"=B3:B7) ; 0 )

= INDEX( C3:C7 ;
    EQUIV( 1 ; (155<D3:D7)*("chaise"=B3:B7) ; 0 )
  )

Limitations

Comme dans notre exemple, plusieurs enregistrements peuvent correspondre aux différents critères saisis. La formule suivante vous permettra de compter le nombre de cellules valident.

= SOMME( (I4<D3:D7)*(I3=B3:B7) )