RECHERCHEV : comment elle fonctionne et pourquoi elle trompe

Si vous ouvrez dix classeurs de comptabilité au hasard, neuf contiennent une RECHERCHEV. C’est la fonction de recherche historique d’Excel : on lui donne une clé, elle va chercher une information dans un tableau. C’est aussi une des fonctions qui produit le plus de résultats faux sans aucun message d’erreur. Cet épisode explique son fonctionnement, ses cinq pièges et les contrôles qui permettent de s’en servir en sécurité.

Ce que vous saurez faire à la fin de l’épisode

• écrire une RECHERCHEV en comprenant chacun de ses quatre arguments ;
• choisir entre correspondance exacte et approchée ;
• diagnostiquer un #N/A et un résultat faux ;
• l’utiliser pour classer un retard dans une tranche ;
• savoir quand passer à une fonction plus moderne.

La syntaxe

=RECHERCHEV(valeur_cherchée; table; no_index_col; [valeur_proche])     (anglais : VLOOKUP)

Le « V » veut dire vertical : la fonction descend dans la première colonne. (RECHERCHEH fait la même chose en ligne.)

Le cas : habiller une balance avec le plan comptable

Une balance exportée ne porte que des numéros de compte. Atlas Négoce veut y ajouter l’intitulé tiré de son plan comptable (feuille Plan, clés en colonne A, intitulés en colonne B).

Figure 1 : la colonne B de la balance. Repère 1 : la formule ; repère 2 : le compte 6199 n’existe pas dans le plan.

Dans B2 :

=RECHERCHEV(A2; Plan!$A$2:$B$9; 2; FAUX)

« Cherche la valeur de A2 dans la première colonne de Plan!A2:B9, et renvoie la valeur de la 2e colonne de cette table, avec une correspondance exacte. » Les dollars (épisode 4) figent la table pour la recopie. Résultats : 6111 → Achats de marchandises, 7111 → Ventes de marchandises, 3421 → Clients, 4411 → Fournisseurs, 5141 → Banques, et 6199 → #N/A.

Ce #N/A n’est pas un défaut : le compte 6199 n’est pas dans le plan, et la fonction le dit. C’est une information de contrôle : le compte est à créer ou à corriger. Pour l’afficher proprement, on l’enveloppe : =SI.NON.DISP(RECHERCHEV(…);"Compte inconnu") (épisode 14).

Les cinq pièges

1. Oublier FAUX

Si l’on omet le quatrième argument, la fonction est en mode approché : elle suppose que la première colonne est triée par ordre croissant et renvoie la valeur la plus proche par défaut (inférieure ou égale). Sur une liste non triée, le résultat peut être faux sans aucun signal. Sur notre plan comptable trié, une clé absente comme 6199 renverrait l’intitulé de 6131 (« Locations et charges locatives ») au lieu de #N/A : une erreur d’affectation silencieuse. Écrivez toujours le quatrième argument. Pour une correspondance exacte, FAUX (ou 0).

2. La clé n’est pas à gauche

RECHERCHEV ne regarde que la première colonne de la table. Si vous voulez retrouver le code à partir de la désignation et que le code est à gauche de la désignation, c’est impossible directement. Solutions : réorganiser la table, ou utiliser INDEX/EQUIV (épisode 21) ou RECHERCHEX (épisode 20).

3. Le numéro de colonne figé

Le 2 est un chiffre en dur. Si quelqu’un insère une colonne dans la table, le numéro ne bouge pas et la fonction renvoie la mauvaise colonne, sans erreur. De même si l’on décide de renvoyer une autre colonne : il faut compter à la main. Pour limiter le risque : COLONNE(Plan!B1) à la place du 2, ou, mieux, passer à une fonction qui désigne la colonne de résultat directement.

4. Les types : nombre contre texte

Le piège le plus fréquent en comptabilité. Notre plan stocke les numéros de compte en texte. Si la balance contient 5141 saisi comme nombre, la recherche échoue.

Figure 2 : à gauche, la clé nombre 5141 donne #N/A ; à droite, A2&"" la convertit en texte et la recherche réussit.

Les deux remèdes : convertir la clé cherchée dans le type de la table (A2&"" pour du texte, CNUM(A2) pour du nombre), ou harmoniser une fois pour toutes les types dans les deux tables. Les espaces invisibles produisent le même symptôme : "5141 " n’est pas "5141". Contrôlez avec ESTNUM et NBCAR (épisodes 2 et 15), et nettoyez avec SUPPRESPACE.

5. La table non figée

Sans les $, la plage glisse à chaque recopie : la première ligne fonctionne, les suivantes cherchent dans des plages décalées, qui ne contiennent plus la clé. Symptôme : des #N/A en cascade dans le bas du tableau. Réflexe : $A$2:$B$9, ou, mieux, un nom défini ou un tableau Excel.

La recherche approchée : classer dans une tranche

Quand on veut la correspondance approchée, par exemple pour ranger un retard dans une tranche, il faut une table triée par ordre croissant.

Figure 3 : la table des tranches (colonnes D et E, triée par ordre croissant) et les résultats dans la colonne B.

La table indique le début de chaque tranche : 0 → « 0-30 j », 31 → « 31-60 j », 61 → « 61-90 j », 91 → « > 90 j ». La formule =RECHERCHEV(A2;$D$2:$E$5;2;VRAI) cherche la plus grande valeur inférieure ou égale au retard :

Le retard négatif (facture non échue) renvoie #N/A parce qu’il est inférieur au plus petit seuil de la table : ajoutez une ligne « −9999 → Non échu » en tête de table pour le traiter. La même mécanique sert pour un barème d’impôt, des taux de commission ou des taux d’amortissement. Si la table n’est pas triée, le résultat est faux sans erreur : c’est le piège n° 1 sous une autre forme.

Quand s’en passer

RECHERCHEV est correcte quand on respecte les règles, mais elle cumule les contraintes : clé à gauche, numéro de colonne en dur, correspondance par défaut approchée. RECHERCHEX (Excel 2021 et Microsoft 365) les supprime : valeur exacte par défaut, colonnes de recherche et de résultat désignées séparément, valeur de repli intégrée. INDEX/EQUIV joue ce rôle dans toutes les versions. Vous devez néanmoins savoir lire une RECHERCHEV : elle est présente dans la plupart des classeurs hérités, et la comprendre évite d’être piégé par un fichier qui « marche » mais qui est faux.

À vous de jouer

Dans le classeur d’exercice :

1. Onglet Balance : complétez B2:B7 avec =RECHERCHEV(A2;Plan!$A$2:$B$9;2;FAUX). Quel compte renvoie #N/A ?
2. Remplacez FAUX par VRAI dans B7 (le compte 6199) : que renvoie la fonction ? Pourquoi est-ce dangereux ?
3. Onglet Types : constatez le #N/A de la clé numérique ; corrigez avec A2&"" comme dans la colonne D.
4. Onglet Tranches : testez un retard de 91, de 90 et de 0. Ajoutez une ligne « −9999 / Non échu » en tête de la table et testez −5.

Correction. Le compte 6199 renvoie #N/A. Avec VRAI dans la même cellule, la fonction renvoie « Locations et charges locatives » (compte 6131, la plus grande clé inférieure ou égale à 6199 dans le plan trié) : une erreur silencieuse, qui affecterait un compte inexistant à une charge de loyer. Sur l’onglet Types, A2&"" convertit 5141 en texte et renvoie « Banques ». Pour les tranches, 91 donne « > 90 j », 90 donne « 61-90 j » et 0 donne « 0-30 j » ; une fois la ligne « −9999 / Non échu » ajoutée (et la plage de la formule étendue à cette ligne), −5 donne « Non échu ».

À retenir

• RECHERCHEV(valeur; table; n°; FAUX) : clé dans la première colonne, FAUX pour l’exact, toujours.
• VRAI (ou rien) = recherche approchée sur une table triée : seulement pour des tranches et des barèmes.
• Un #N/A signale souvent un problème de type (texte/nombre) ou d’espaces, rarement une clé réellement absente.
• Plage figée avec $ ; numéro de colonne à surveiller.
• Pour tout nouveau fichier, préférez RECHERCHEX ou INDEX/EQUIV.

Épisode 20 : RECHERCHEX, la fonction qui règle presque tous ces problèmes, dans Excel 2021 et Microsoft 365.