5.6 RECHERCHEX() pour rechercher une valeur correspondante

La fonction RECHERCHEX() est une fonction de recherche très utilisée dans Excel. Elle est une version améliorée de RECHERCHEV().

Elle permet de rechercher une valeur dans une plage, que ce soit une ligne, une colonne ou un tableau, et de retourner la ou les valeurs correspondantes dans autre plage.

Voici un exemple typique de recherche avec RECHERCHEX() : si on a une colonne avec des codes d'articles dont la description est dans la colonne suivante, on pourra retrouver une description d'article simplement en entrant son code.

RECHERCHEX() utilise cette syntaxe. Les arguments entre crochets carrés sont optionnels.

RECHERCHEX() vs RECHERCHEV()

Il existe plusieurs différences entre RECHERCHEX() et RECHERCHEV(). En voici quelques-unes :

• La fonction RECHERCHEV() reçoit en paramètre une plage qui contient à la fois la valeur recherchée et la valeur à retourner. Avec RECHERCHEX(), il faut spécifier une plage pour la valeur recherchée et une autre plage pour la valeur retournée, ce qui offre plus de souplesse.
• Contrairement à RECHERCHEV(), la valeur recherchée par RECHERCHEX() n'a pas besoin d'être située dans la première colonne à gauche d'une plage. Elle peut être à gauche de la plage de recherche, à droite, plus haute, plus basse, en autant que la plage de recherche et la plage de résultats aient la même taille.
• RECHERCHEX() permet de faire une recherche horizontale ou verticale donc de travailler avec des lignes ou avec des colonnes. Avec RECHERCHEV(), on ne pouvait travailler qu'avec des colonnes. Il fallait utiliser RECHERCHEH() pour travailler avec des lignes.
• Par défaut, RECHERCHEX() effectue une recherche exacte. Donc, si la valeur recherchée n'est pas trouvée dans la plage de recherche, la fonction indique qu'il n'y a pas de correspondance. Avec RECHERCHEV(), la recherche par défaut est approximative c'est-à-dire que si la valeur recherchée n'est pas trouvée, c'est la valeur plus petite qui est utilisée. Ce comportement de RECHERCHEV() peut porter à confusion.
• RECHERCHEX() permet de renvoyer la valeur exacte, la valeur plus grande ou la valeur plus petite que celle recherchée. Avec RECHERCHEV(), on ne peut retourner que la valeur exacte ou la valeur plus petite.
• RECHERCHEX() peut retourner la valeur de plusieurs cellules qui correspondent à la valeur recherchée. RECHERCHEV() ne peut retourner qu'une seule valeur.
• RECHERCHEX() permet d'effectuer une recherche avec caractères génériques, ce qui est impossible avec RECHERCHEV().

Arguments obligatoires

La fonction RECHERCHEX() demande au moins trois arguments :

• la valeur recherchée
• la plage dans laquelle la donnée doit être recherchée.
• la plage dans laquelle se trouvent la ou les données à retourner. Cette plage doit avoir la même taille que la plage des données à rechercher. Si une des plages est plus longue ou plus courte que l'autre, Excel affichera #VALEUR.

Exemple de base :

Pour obtenir la description de l'article dont le code correspond à la valeur entrée en B8, entrez la formule =RECHERCHEX(B8;A2:A6;B2:B6)

Résultat :

Voici un exemple qui retourne plusieurs données. La plage des cellules retournées contient donc plus d'une colonne : =RECHERCHEX(B8;A2:A6;B2:C6)

Remarquez que la formule en C9 se charge de remplir les cellules C9 et D9.

Résultat :

Grâce à la fonction TRANSPOSE(), il est possible de transposer les résultats pour que les données retournées soient affichées l'une sous l'autre lorsque la fonction retourne plusieurs données.

=TRANSPOSE(RECHERCHEX(B8;A2:A6;B2:C6))

Arguments optionnels

Il est possible d'entrer 3 arguments supplémentaires afin de préciser comment la recherche doit être effectuée.

Valeur à afficher si la valeur recherchée n'est pas trouvée

Le 4^e argument permet de préciser la valeur à afficher si la valeur recherchée n'est pas trouvée lors d'une recherche exacte.

Sans lui, si la valeur recherchée n'était pas trouvée, Excel afficherait #N/A.

=RECHERCHEX(B8;A2:A6;B2:B6;"Code invalide")

Type de correspondance

Par défaut, RECHERCHEX() effectue une correspondance exacte c'est-à-dire que la fonction indique que la valeur recherchée n'a pas été trouvée lorsqu'elle ne trouve pas exactement cette valeur dans la plage de recherche.

Le 5^e argument permet de modifier ce comportement.

Remarquez que si vous ne souhaitez pas entrer le 4^e argument, il vous suffira de faire suivre deux points-virgules d'affilée.

Exemple avec 4^e et 5^e arguments : =RECHERCHEX(B8;A2:A6;B2:B6;"Code invalide";-1). Ceci n'a pas de sens puisqu'avec une recherche approximative, la valeur du 4^e argument ne sera jamais utilisée.

Exemple sans 4^e argument et avec 5^e :=RECHERCHEX(B8;A2:A6;B2:B6;;-1)

Le type de correspondance peut prendre une des valeurs suivantes :

• 0 : Correspondance exacte. Si la valeur recherchée n'est pas trouvée, Excel affichera #N/A ou la valeur du 4^e argument s'il a été fourni. Il s’agit de la valeur par défaut.
• -1 : Si la valeur recherchée n'est pas trouvée, Excel affichera la valeur de la cellule qui correspond à la valeur inférieure à la valeur recherchée.

  Ici, j'ai laissé tomber l'exemple du code de produit puisqu'une recherche approximative donnerait des résultats erronés lorsque le code de produit n'est pas trouvé.

  Plutôt, cet exemple utilise une recherche approximative pour trouver le rabais à appliquer selon la quantité d'articles achetés. Dans un tel contexte, il n'est pas nécessaire d'entrer la quantité exacte pour connaître le rabais.

  =RECHERCHEX(C7;C2:C5;D2:D5;;-1)

  

  Dans cet exemple, puisque la valeur 145 n'est pas trouvée, RECHERCHEX() considère la cellule dont la valeur est plus petite que la valeur recherchée, soit 100.

• 1 : Si la valeur recherchée n'est pas trouvée, Excel affichera la valeur de la cellule qui correspond à la valeur supérieure à la valeur recherchée.

  Ici, on recherche la note en lettre à mettre au bulletin d'une étudiante ou d'un étudiant selon son résultat sur 100.

  =RECHERCHEX(D17;C4:C15;D4:D15;;1)

  

  Dans cet exemple, puisque la valeur 83 n'est pas trouvée, RECHERCHEX() considére la cellule dont la valeur est plus grande que la valeur recherchée, soit 84.

• 2 : Correspondance avec caractère générique. Ceci permet d'utiliser des caractères génériques comme valeur recherchée :
  • * : remplace un nombre quelconque de caractères

    Dans cet exemple, on recherche la description du premier article dont le code débute par B : =RECHERCHEX(B8;A2:A6;B2:B6;;2)

    

    Ici, la formule est la même mais dans la cellule B8, on indique qu'on recherche la description du premier article dont le code se termine par 24.

    

  • ? : remplace un seul caractère. Par exemple, avec PO?S, RECHERCHEX() trouvera un article dont le code est POIS ou POTS mais pas PORTS.
  • ~ : permet d'échapper un caractère générique, c'est-à-dire qu'il sera interprété comme un caractère ordinaire.

    Utile lorsque la valeur recherchée contient elle-même un astérisque, un point d'interrogation ou un tilde.

    Il faut le faire suivre le tilde par * ? ou ~. Par exemple, avec VI~*X, RECHERCHEX() pourra retrouver un article dont le code est VI*X.

Mode de recherche

Par défaut, RECHERCHEX() effectue toujours sa recherche à partir du premier élément de la liste et il retourne la première valeur rencontrée.

Le 6^e et dernier argument, le mode de recherche, permet de changer ce comportement en précisant dans quel sens la recherche doit être effectuée.

Le mode de recherche peut prendre les valeurs suivantes :

• 1 : recherche à partir de la première cellule de la plage. Il s’agit de la valeur par défaut.
• -1 : recherche à partir de la dernière cellule de la plage.
• 2 : recherche binaire en ordre croissant. Avec ce mode, les données de la plage doivent être triées en ordre croissant.

  La recherche binaire est beaucoup plus rapide. La différence du temps de traitement sera évidente si vous effectuez une recherche dans une plage qui contient des centaines de cellules.

• -2 : recherche binaire en ordre décroissant. Avec ce mode, les données de la plage doivent être triées en ordre décroissant.

Dans cet exemple, la recherche est effectuée à partir de la dernière cellule de la plage. On voit que RECHERCHEX() trouve la dernière occurence d'un code qui débute par POT.

=RECHERCHEX(B8;A2:A6;B2:B6;;2;-1)