Fonction Recherche RECHERCHEX

Après plus de 30 ans d’existence, deux formules les plus utilisées dans Excel viennent de se voir mise à la retraite par Microsoft.

Depuis quelques temps, les seuls utilisateurs de la version Microsoft 365 d’Excel peuvent désormais utiliser la fonction RECHERCHEX (XLOOKUP dans la version anglaise) en lieu et place des habituels RECHERCHEV et RECHERCHEH (respectivement VLOOKUP et HLOOKUP dans la version anglaise).

Une petite révolution pas si anodine que ça qui simplifie beaucoup les choses dès que vous souhaitez extraire des données d’une table de correspondance.

RECHERCHEVouH : à quoi ça sert ?

Pour ceux qui ne les connaissent pas, RECHERCHEV et RECHERCHEH permettent de retourner une valeur dans une table de manière horizontale (RECHERCHEH) ou verticale (RECHERCHEV) en fonction d’un critère de recherche.

Dans l’exemple ci-dessous, RECHERCHEV permet de retourner le nom de l’employé sur la base de son numéro de référence dans l’organisation.

Si on demande à Excel de rechercher la valeur qui se trouve dans la cellule G1 (A005) dans une plage de données (A1:D10) et de retourner ce qui se trouve dans la colonne 2 (ici la colonne Employé ou B) de cette plage de donnée il retourne le nom de “Charles Dupont”.

C’est assez puissant et suffisamment simple lorsqu’on a compris le principe. RECHERCHEH fonctionne sur le même principe mais horizontalement.

Ce qui ne va pas avec RECHERCHEV et RECHERCHEH

Au fil des années ces fonctions simples on fait apparaître leurs limites dont voici quelques unes :

L’ajout d’une colonne peut rendre la formule caduque

Une des bases de RECHERCHEV et de RECHERCHEH est d’indiquer le numéro de la colonne de la plage de donnée dans laquelle la formule va retourner la valeur.

Imaginons qu’on ajoute une colonne « civilité » (Monsieur/Madame) dans l’exemple entre les colonnes “ID” et “Employé”. Que va-t-il se passer ?

RECHERCHEV va toujours rechercher l’employé qui porte l’ID A005 et retourner ce qu’il trouve dans la colonne 2. Le problème est que cette colonne ne contient plus le nom de l’employé mais sa civilité.

Si RECHERCHEVouH ne trouve rien, l’erreur #NA apparait

Tant redoutée par les utilisateurs d’Excel, l’erreur #NA a bien des raisons mais dans le cas de RECHERCHEV le coupable est souvent l’élément recherché. S’il n’est pas dans la zone de recherche, la formule ne fonctionne pas.

Une solution est d’inclure RECHERCHEV dans une formule SIERREUR et de répéter à deux reprise la même formulation de RECHERCHEV.

RECHERCHEX permet de contourner le problème en spécifiant le résultat retournée dans ce cas.

RECHERCHEV recherche la donnée exacte

RECHERCHEV se limite à retrouver exactement la donnée recherchée. Tant pis s’il manque un point, une lettre ou que le copier-coller a apporté un espace avec lui dans la cellule. Dans tout ces cas, RECHERCHEV vous abandonne à votre triste sort.

Il existe des moyens de traiter les espaces passagers clandestins en les traitant avec d’autre formules mais RECHECHEX peut retrouver une donnée même si le critère de recherche est partiel et s’affranchit donc de ce problème.

Les données recherchées doivent être dans l’ordre

Pour que l’exemple fonctionne, on a ordonné les ID des employés par ordre croissant (A001, A002, A003, A004…). Si on classe les employés par ordre alphabétique, RECHERCHEV est perdu et retournera le nom de l’employé portant l’ID précédent celui recherché.

Dans l’exemple, si on recherche l’employé ayant la référence A008 dans une liste triée par nom, RECHERCHEV va retourner le nom de l’employé ayant la référence A007.

RECHERCHEX, lui n’a aucun problème pour retrouver le bon employé. Vous pouvez a nouveau faire confiance à Excel.

Vous êtes convaincus ? Regardons maintenant comment RECHERCHEX fonctionne :

RECHERCHEX

Comme vous avez pu le remarquer dans la dernière capture d’écran, RECHERCHEX ne fait plus référence à une zone de donnée ou encore à des numéro de colonnes. Vous pouvez arrêter de compter vos colonnes.

Le principe de RECHECHEX est d’indiquer dans quelle colonne ou ligne chaque étape de la recherche se passe. C’est quelque chose à avoir en tête avant de commencer. La formule est frugale. Si vous garder vos habitudes de grandes plages comme pour RECHERCHEV ou RECHERCHEX, cela ne va pas fonctionner du tout.

Valeur_cherchée

Le premier élément de la formule ne change pas de RECHERCHEV. Il faut toujours indiquer l’élément recherché.

Tableau_recherche

La suite est plus différente. Si l’élément intitule tableau_recherche, il faut le comprendre comme la zone de recherche ou l’élément recherché se trouve. Dans mon exemple ma zone de recherche est A2:A10 qui coïncide avec le contenu de la colonne ID.

Tableau_renvoyé

De la même manière dont on a indiqué où trouver la valeur recherchée, on indique dans quelle zone on souhaite que RECHERCHEX retourne une valeur, ici le nom de l’employé. On indique donc B2:10 qui correspond à la zone du tableau où se trouve les noms des employés.

[Si_non_trouvé]

Ce quatrième élément est la variable par laquelle RECHERCHEX se défait de beaucoup d’erreur #NA. Il suffit d’indiquer à Excel ce que l’on souhaite voir apparaître. Dans l’exemple on peut indiquer « Inconnu » dans le cas où l’on recherche un nom qui ne se trouve pas dans la liste. Dans tout les autres cas, il retourne bien le nom du département où travaille l’employé. Daniel Riccardo n’est pas un employé de notre entreprise et la formule retourne donc la valeur « Inconnu ».

Mode de correspondance

Par défaut, RECHERCHEX recherche la valeur exacte. Mais alors pourquoi va-t-on indiqué qu’elle peut rechercher des valeurs partielles ? Tout simplement car le cinquième élément permet d’affiner ce comportement. On peut choisir entre une recherche exacte, égale ou supérieure, égale ou inférieure ou générique (partielle).

Mode de recherche

Dernier élément mais non des moindres, l’ordre dans lequel RECHERCHEX va chercher. En effet, doit-il commencer depuis le début du tableau ou par la fin des données ? RECHERCHEX offre plusieurs possibilité de recherches. 1 et -1 recherche du début ou de la fin de la liste de donnée.

Les deux autres sont plus spécifiques au type de données que l’on a et ne fonctionnent que si la colonne de données où se trouve l’élément recherché est triée de manière croissante ou décroissante.

Imaginons que l’on souhaite connaitre le dernier engagé du département comptabilité. La zone de recherche est maintenant C1:C10 où se trouvent les départements et la zone où se trouve les données à retourner est maintenant B1:B10 dans laquelle se trouve le nom des employés.

Si on choisit 1 comme valeur pour le Mode de recherche, RECHERCHEX retourne le premier de la liste en partant du haut, soit “Jean Brulé”

Si on choisit -1 comme valeur, la formule va retourner le premier de la liste en partant par le bas, soit “Jaques Chose”

A vous de voir…

Changer ses habitudes n’est pas facile et ayant fonctionné avec RECHERCHEV pendant des années, on peut comprendre pourquoi certains seraient freinés de changer pour RECHERCHEX.

Une autre considération à avoir est le fait que cette formule n’est disponible que pour la dernière version publiée d’Excel. Si vous travaillez en entreprise avec des licences Office 365, cela n’est pas un problème car tout le monde travaille sur la même plateforme.

Par contre, dans le cas de collaboration entre deux organisations dont une n’aurait pas la version 365 d’Excel ce ne sera pas aussi simple.