RECHERCHEX vous réconcilie avec Excel

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 semaines, les utilisateurs de la version office 365 d’Excel peuvent désormais utiliser la fonction RECHERCHEX en lieu et place des habituels RECHERCHEV et RECHERCHEH.

Pourquoi Microsoft a-t-il créé une nouvelle fonction et pour quelles raisons devrions-nous nous y intéresser ? C’est dans la suite de l’article.

RECHERCHEV: A quoi ça sert ?

Pour ceux qui ne les connaissent pas, RECHERCHEV et RECHERCHEH (ou respectivement VLOOKUP et HLOOKUP dans la version anglaise) 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 me permet de retourner le nom de l’employé sur base de son numéro de référence dans l’organisation.

Si je 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 ma colonne Employé ou B) de cette plage de donnée il me 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 apparaitre leurs limites aux nombreux utilisateurs. Je ne vais pas les énumérer toute ici mais voici les principales raisons pour lesquelles vous devriez changer vos habitudes de RECHERCHEV vers RECHERCHEX.

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 que j’ajoute une colonne « civilité » (Monsieur/Madame) dans mon 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 trouver dans la colonne 2. Le problème est que cette colonne ne contient plus le nom de l’employé mais sa civilité.

Si RECHERCHEV 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. Je reviendrais sur cela dans un prochain article.

RECHECHEX peut retrouver une donnée même si le critère de recherche est partiel et s’affranchit de se problème.

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

Pour que mon exemple fonctionne, j’ai ordonné les ID des employés par ordre croissant (A001, A002, A003, A004…). Si je 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 mon exemple, si je recherche l’employé ayant la référence A008 dans une liste triée par nom, RECHERCHEV va me 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.

Etes-vous convaincus ? Regardons maintenant comment RECHERCHEX fonctionne

RECHERCHEX 101

Comme vous avez pus 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 nous avons indiqué ou trouver la valeur recherchée, nous indiquons dans quelle zone nous souhaitons que RECHERCHEX retourne une valeur, ici le nom de l’employé. J’indique B2:10 qui correspond à la zone du tableau ou se trouver 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 apparaitre. Dans le cas de mon exemple J’ai indiqué “Inconnu” dans le cas ou l’on recherche un nom qui ne se trouver pas dans la liste. Dans tout les autres cas, il retourne bien le nom du département ou travaille l’employé. Daniel Riccardo n’est pas un employé de notre entreprise et la formule retourne la valeur Inconnu.

Mode de correspondance

Par défaut, RECHERCHEX recherche la valeur exacte. Mais alors pourquoi vous ais-je dis qu’elle peut rechercher des valeur 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 laquelle RECHERCHEX va chercher. Doit il commencer du 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 vous avez et ne fonctionnent que si la colonne de donnés ou se trouver l’élément recherché est triée de manière croissante ou décroissante.

Imaginons que je 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 ou se trouvent les données à retourner est maintenant B1:B10 ou se trouve le nom des employés.

Si je choisis 1 comme valeur pour le Mode de recherche, RECHERCHEX retourne le premier de la liste en partant du haut. Jean Brulé.

Si je choisi -1 comme valeur, la formule va retourner le premier de la liste en partant par le bas: Jaques Chose

A vous de voir…

Changer ses habitudes n’est pas facile est, ayant fonctionné avec RECHERCHEV pendant des années, je peux comprendre pouquoi certains seraient freiné 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 office365, 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 organisation ou d’un fichier pour une association ce n’est pas aussi simple. Une autre entreprise se contente peut-être d’office 2016 et ne vois pas l’intérêt de mettre à jour son installation.

Les membres d’une association, eux, utilisent leur ordinateur privé, et tout le monde n’est pas sur la même ligne. Je connais des gens qui utilisent toujours Office 2010 dont le support de Microsoft prend fin dans quelques mois.

Dites moi ce que vous pense de cette nouvelle formule RECHECHEX dans les commentaires.