RECHERCHEX plusieurs critères

Résumé

Pour effectuer une RECHERCHEX avec plusieurs critères, la meilleure solution est de cumuler les tests logiques en tant que second argument de la fonction RECHERCHEX comme dans cet exemple :

= RECHERCHEX( 1 ; (B3:B7=I2)*(D3:D7<I3)*(E3:E7>I4) ; C3:C7 )

Cette formule permet d’indiquer plusieurs critères. Dans notre cas il donnera la première occurrence répondant à l’ensemble des critères indiqués. Pour cela, on s’est servi de tests logiques qui ont permis de générer un tableau constitué de 1 et de 0 (voir explications).

Formule Excel – RECHERCHEX plusieurs critères

= RECHERCHEX( 
    1 ; 
    (test_logique_1)*(test_logique_2)*(test_logique_3) ; 
    plage_résultats 
  )

Explications

Dans notre exemple, on veut retrouver la valeur du stock de l’article qui répond à trois critères (valeur, quantité et nom de l’article). Pour cela, on utilise une fonction RECHERCHEX avec plusieurs critères.

Normalement, la fonction RECHERCHEX ne permet l’utilisation que d’un seul critère (la valeur cherchée). Dans cette formule, nous contournons le problème puisque nous ne faisons pas réellement une RECHERCHEX avec 2 critères ou même une RECHERCHEX avec 3 critères mais nous créons un tableau composé de 0 et 1. Le chiffre 1 indiquera que la ligne répond à l’ensemble des critères. La fonction RECHERCHEX recherchera la position du premier 1.

Création du tableau

La formule ci-après crée un tableau composé de 0 et de 1 sur la base des critères saisis.

= (B3:B7=N2) * (D3:D7<N3) * (E3:E7>N4)

Chaque test logique vient générer une suite de VRAI/FAUX indiquant si le test est correct pour la cellule testée.

= (B3:B7=N2) 

= {VRAI;FAUX;VRAI;VRAI;FAUX}

En cumulant les critères grâce aux astérisques (*), nous demandons à Excel d’afficher 1 si tous les critères se révèlent vrais et 0 si au moins un critère se révèle FAUX.

= (B3:B7=N2) * (D3:D7<N3) * (E3:E7>N4)

= {VRAI;FAUX;VRAI;VRAI;FAUX} *
  {VRAI;VRAI;VRAI;FAUX;FAUX} *
  {VRAI;VRAI;VRAI;VRAI;VRAI}

Excel va convertir automatiquement les VRAI/FAUX en 1/0 lors de l’opération visant à les multiplier.

= {1;0;1;1;1} *
  {1;1;1;0;0} *
  {1;1;1;1;1}

= {1;0;1;0;0}

La matrice générée indique que les valeurs situées à la première et troisième ligne remplissent l’ensemble des critères indiqués.

Il ne nous reste plus qu’à trouver la position du nombre 1 dans la matrice grâce à la fonction RECHERCHEX.

Fonction RECHERCHEX

La fonction RECHERCHEX a la syntaxe suivante :

= RECHERCHEX( valeur_cherchée ; tableau_recherche ; tableau_renvoyé ; [si_non_trouvé] ; [mode_correspondance] ; [mode_recherche] )

La valeur cherchée est la valeur à identifier dans la matrice. Dans notre exemple, il s’agira du chiffre 1. Le tableau de recherche sera la matrice de recherche. Ce sera la matrice composée de 1 et 0. Le tableau renvoyé est la colonne avec la valeur à récupérer. Dans notre cas, il s’agira de la colonne avec les valeurs des stocks.

En reprenant notre matrice 1/0 générée précédemment, nous obtenons la formule suivante :

= RECHERCHEX( 1 ; {1;0;1;0;0} ; C3:C7 )

La fonction RECHERCHEX récupérera la position de la première valeur 1 dans la matrice de recherche et renverra la valeur située à la même position dans la plage de cellules des résultats.

Autre solution – RECHERCHEX et concaténation

Dans certains cas, une autre solution est possible. Il s’agit de concaténer les critères de recherches ainsi que les matrices de recherche. Voici un exemple de formule :

= RECHERCHEX( B3&D3&E3 ; B3:B7&D3:D7&E3:E7 ; C3:C7 )

Pour Excel, cela revient à créer virtuellement une nouvelle colonne dans laquelle les valeurs des colonnes B, D et E sont concaténées et à rechercher une valeur à l’intérieur de celle-ci.

Cette solution, bien que plus simple, aura des limites importantes. En effet, elle ne fonctionne qu’avec des égalités (=) et sera inutile pour faire une RECHERCHEX plusieurs critères avec des inégalités (<, >, <=, >=, <>) comme dans notre exemple.

Formules en lien

• RECHERCHEV avec plusieurs critères
• SOMME de RECHERCHEV
• Combiner INDEX et EQUIV
• RECHERCHEV avec plusieurs résultats
• RECHERCHEV et CONCATENER
• Corriger l’erreur #N/A
• INDEX EQUIV avec plusieurs critères
• RECHERCHEV inversé
• Si RECHERCHEV est vrai alors
• Si erreur RECHERCHEV
• Inverser colonnes et lignes
• Rechercher une valeur dans un tableau