RECHERCHEV n’est pas jouer

Je vous propose aujourd’hui de découvrir la fonction RECHERCHEV, que je n’utilise plus depuis de nombreuses années 😊. C’est la raison pour laquelle je ne l’avais pas mentionnée dans l’article 8 fonctions essentielles pour tout faire avec Excel.

Alors pourquoi en parler ? Tout simplement parce qu’il s’agit de la fonction de recherche la plus utilisée dans Excel. Elle est tellement utilisée qu’elle apparaît directement dans les recherches d’emplois, en compétence demandée en Excel !

Cette fonction donne les résultats escomptés (trouver une donnée dans un tableau) mais son utilisation n’est pas toujours intuitive, ni optimisée pour vous permettre d’être efficace. Je reviendrai dessus un peu plus bas dans l’article (lire Les inconvénients).

Une fonction très utilisée en général sera également très utilisée par vos collègues et collaborateurs. En cas d’erreur dans l’exécution de la formule, ils viendront vous voir pour vous demander de l’aide. Or, si vous ne connaissez pas cette fonction RECHERCHEV, vous serez bien en peine de les aider !

Quand on reprend l’historique d’Excel, elle était déjà présente sous Excel 5 dès 1993 (je n’ai pas vérifié les versions précédentes 🤣) :

Principe de la fonction

RECHERCHEV effectue une recherche pour trouver une donnée à partir d’une valeur initiale connue de l’utilisateur. La recherche est exclusivement verticale (d’où le ‘V’ dans son nom), donc en colonnes. La fonction créé une matrice (un tableau, quoi !) virtuelle de recherche, invisible pour l’utilisateur, à partir de laquelle elle effectue ses recherches.

Le résultat remonté par RECHERCHEV est celui de la première occurrence de la valeur initiale. Les autres occurrences (si elles existent) ne sont pas prises en compte.

Je ne suis pas clair ? Rien ne vaut un exemple ! Il sera repris tout au long de cet article.

Plus le nombre de données sera important, plus l’utilisation d’une fonction de recherche prendra de sens, mais plus les risques d’erreurs de résultat seront importants. C’est la raison pour laquelle il est important de bien comprendre comment la fonction utilisée agit sur son environnement, et de s’assurer que les résultats sont corrects avant de la déployer.

Résumé : avantages, inconvénients, limites

Avantages

• Rapidité de mise en œuvre
• Elle est tellement utilisée partout que n’importe qui pourra vous aider en cas de problème 😊 (l’inverse est vrai également !)

Inconvénients

• Gestion des colonnes de la matrice virtuelle créée par Excel
• Les données initiales doivent obligatoirement être positionnées en première colonne de la matrice virtuelle
• Risque d’erreurs d’interprétation si Excel ne trouve pas la valeur initiale cherchée
• Pas de fonctionnement multicritères
• (fortement recommandé) : La colonne contenant les valeurs initiales doit être triée dans l’ordre croissant des données.

Limites

• La fonction arrête ses recherches dès que la première occurrence de la valeur initiale est trouvée

Données d’entrée : les arguments

La formule permettant de lancer RECHERCHEV s’écrit de la manière suivante :

=RECHERCHEV(Argument 1;Argument 2;Argument 3;Argument 4)

dans laquelle il faut remplacer les Arguments 1, 2, 3, 4 par les valeurs qui vont nous intéresser, et qui sont détaillées ci-dessous. Dans la même feuille que celle où apparaît le tableau contenant les données, il vous suffit donc de double-cliquer dans n’importe quelle cellule (mais hors du tableau de données !) et entrer la formule en suivant les précisions ci-dessous.

Pour information : la formule commence par un ‘=‘ suivi du nom de la fonction (‘RECHERCHEV’), puis des parenthèses à l’intérieure desquelles seront inscrits les arguments de la fonction. Chaque argument est séparé par un point-virgule ‘;‘ à l’intérieur des parenthèses.

RECHERCHEV utilise quatre arguments, dont le dernier est facultatif (mais fortement recommandé !). Les noms en italique entre parenthèses correspondent aux appellations officielles d’Excel.

• Argument 1 (Valeur_cherchée) : valeur initiale.
• Argument 2 (Table_matrice) : matrice virtuelle dans laquelle chercher la correspondance avec l’argument 1
• Argument 3 (No_index_col) : numéro de colonne au sein de la matrice virtuelle, dans laquelle trouver la correspondance avec l’argument 1
• Argument 4 facultatif (Valeur_proche) : précision du résultat remonté

Argument 1 : valeur initiale

Cette valeur est la donnée connue de l’utilisateur, et le point de départ indispensable de la fonction. Elle peut être entrée directement dans la formule ou désigner une cellule portant la valeur à chercher dans la matrice virtuelle. Il peut s’agir d’un nombre ou d’une suite alphanumérique, comme dans l’exemple des menus.

Argument 2 : matrice dans laquelle chercher la correspondance avec l’argument 1

L’argument 2 définit la plage de cellules (également appelée matrice ou tableau) au sein de laquelle Excel va chercher la valeur initiale (argument 1) ainsi que la donnée correspondante à renvoyer comme résultat. Cette plage de cellules peut se trouver à n’importe quel emplacement de la feuille Excel : rien n’oblige à la démarrer en colonne A 😊. Il peut parfaitement y avoir des données à gauche de la matrice, elles ne seront simplement pas prises en compte par la fonction.

La plage de cellule sélectionnée va devenir pour Excel une matrice virtuelle dans laquelle le logiciel va extraire le résultat souhaité. Cette matrice est dite virtuelle car elle n’existe que dans la mémoire d’Excel et n’apparaît pas directement pour l’utilisateur. Elle possède cependant sa propre numérotation de colonnes, de 1 pour la première (la plus à gauche), 2 pour la deuxième… et ainsi de suite jusqu’à la dernière colonne de la matrice virtuelle (la plus à droite). Cette numérotation est totalement indépendante de celle du tableau Excel (numérotée habituellement de A pour la première à XFD pour les versions les plus récentes du logiciel, ou IV pour les versions plus anciennes jusqu’à 2003).

Prérequis :

1. La matrice virtuelle doit impérativement contenir des colonnes toutes adjacentes entre elles, même si certaines colonnes sont vides de donnée.
2. La matrice virtuelle doit impérativement commencer par la colonne dans laquelle se situe l’argument 1 (la valeur initiale). Elle peut contenir autant de colonnes que souhaité.
3. La matrice doit également contenir la colonne dans laquelle trouver la correspondance (ça paraît logique mais ça va mieux en le disant !)
4. Idéalement, la première colonne de la matrice (celle de l’argument 1) doit être triée dans l’ordre croissant. Ce tri va permettre d’obtenir des résultats plus précis.

Argument 3 : numéro de colonne dans laquelle trouver la correspondance

L’argument 3 correspond au numéro de colonne de la matrice virtuelle dans laquelle extraire le résultat souhaité.

Exemple : en reprenant le tableau précédent, si ma matrice commence en colonne B dans Excel, alors la colonne B contenant les jours sera la colonne n°1 de la matrice virtuelle, et la colonne C dans Excel sera la colonne n°2 de la matrice virtuelle. Et ainsi de suite si le tableau contenait un plus grand nombre de colonnes.

Argument 4 (facultatif) : précision du résultat remonté

Cet argument est facultatif en théorie, mais indispensable pour Excel 🤔

En fait, si l’argument est laissé vide, RECHERCHEV utilise une valeur par défaut. Si cette valeur ne convient pas, il faut la modifier en renseignant manuellement l’argument 4. Et c’est à partir de là que la formule prend parfois son envol et remonte des résultats qui paraissent délirants. Pas de panique, je vais y revenir juste un peu plus loin.

L’argument 4 est une valeur logique avec deux possibilités uniquement : VRAI ou FAUX. La différence entre les deux va apparaître quand la valeur initiale exacte (en colonne n°1) n’existe pas ou que les données initiales (toute la colonne n°1) ne sont pas (ou ne peuvent pas être) triées dans l’ordre numérique ou alphabétique. Toutes ces différences sont visualisées en exemple dans la section suivante.

VRAI (valeur par défaut)

RECHERCHEV va chercher la valeur la plus proche de celle recherchée. Elle peut donc correspondre exactement à la valeur à trouver… ou pas !

Si l’argument 4 n’est pas renseigné, il prend par défaut la valeur VRAI.

FAUX

La fonction va chercher la correspondance exacte, et uniquement celle-ci. Si elle n’existe pas, la fonction remonte une erreur (#N/A).

Les erreurs à ne pas commettre (les erreurs d’interprétation)

Dans cette section, je pars de l’exemple mentionné en début d’article. Seules les formules (et les résultats !) vont varier pour comprendre la logique de fonctionnement de la fonction RECHERCHEV. Pour chaque cas, nous allons comparer les formules avec VRAI et FAUX pour l’argument 4 (précision du résultat recherché).

Le fichier duquel les copies d’écran sont tirées est disponible en téléchargement ici : Il vous permettra de tester les différentes possibilités et comparer les résultats obtenus en fonction des valeurs cherchées.

Tri des données initiales : ordre chronologique

De manière très logique, les jours sont positionnés par ordre chronologique du lundi au dimanche. Si votre semaine commence un dimanche pour finir le samedi (typiquement sur le modèle anglosaxon), les résultats seront identiques.

Argument 4 : FAUX

RECHERCHEV trouve la valeur initiale (ex : « vendredi ») et renvoie le menu correspondant à ce jour (« raviolis »). C’est ce que nous voulions : tout va bien ! Si la valeur initiale cherchée n’existe pas, la fonction renvoie une erreur (#N/A).

Argument 4 : VRAI

RECHERCHEV remonte le bon résultat tant que les jours sont dans l’ordre alphabétique (lundi, mardi, mercredi). En cherchant le menu du jeudi, la fonction perd l’ordre alphabétique dont elle a besoin et ne sait plus remonter de résultat correct car il n’existe rien d’alphabétiquement inférieur à « jeudi ». RECHERCHEV renvoie donc une erreur (#N/A).

En cherchant le « vendredi », RECHERCHEV retrouve son ordre chronologique à partir du jeudi. Le bon menu est renvoyé par la fonction.

La recherche pour le « samedi » fait à nouveau perdre l’ordre chronologique à la fonction. Le résultat renvoyé correspond donc à la dernière valeur initiale inférieure à « samedi » trouvée précédemment (« jeudi »). Le menu remonté est donc celui du jeudi (« poisson »).

Pour le « dimanche », on revient au même cas que le « jeudi » : la fonction perd son ordre alphabétique sans trouver de valeur alphabétiquement inférieure à « dimanche » et renvoie une erreur #N/A.

Pour l’anecdote, Excel 5 renvoyait le résultat de la première valeur alphabétique initiale lorsque les données n’étaient pas triées (voir l’image ci-dessus).

Tri des données initiales : ordre numérique

Les jours sont toujours classés par ordre chronologique mais chaque jour de la semaine est remplacé par un chiffre, ici de 1 à 9. Oui, il n’y a que 7 jours dans la semaine mais j’ai volontairement omis les chiffres 3 et 6 permettre de mieux comprendre le fonctionnement de RECHERCHEV. Le tri par ordre chronologique des noms de jours devient un tri par ordre numérique avec les chiffres.

À noter, dans tous les cas (VRAI ou FAUX en argument 4) : si la valeur initiale demandée est inférieure à la plus petite valeur initiale du tableau (par exemple 0), la fonction renvoie une erreur #N/A.

Argument 4 : FAUX

RECHERCHEV remonte le menu du jour à chaque fois qu’il trouve la valeur initiale (le jour de la semaine écrit en chiffre). Si nous lui demandons de trouver le menu correspondant au jour 3, 6, ou un chiffre supérieur à 9, la fonction remonte une erreur (#N/A).

Argument 4 : VRAI

RECHERCHEV remonte toujours un résultat. Si la valeur initiale est connue dans le tableau, le menu du jour renvoyé correspond bien au jour de la semaine sélectionné.

En revanche, si le chiffre demandé en valeur initiale n’est pas connu dans le tableau, la fonction va remonter le dernier menu du jour connu (le résultat de la valeur initiale inférieure dans le tableau) :

• En demandant la valeur initiale 3, RECHERCHEV renvoie le menu correspondant à la valeur initiale 2 « soupe » (le résultat de la dernière valeur inférieure connue).
• En demandant la valeur initiale 6, RECHERCHEV renvoie le menu correspondant à la valeur initiale 5 « poisson » (le résultat de la dernière valeur inférieure connue)
• En demandant la valeur initiale 12, RECHERCHEV renvoie le menu correspondant à la valeur initiale 9 « patates au beurre » (le résultat de la dernière valeur inférieure connue)

Tri des données dans l’ordre alphabétique

Cette fois-ci, les jours sont triés par ordre alphabétique. Ce tri est informatiquement plus logique mais psychologiquement plus compliqué à assimiler car les jours apparaissent dans ce qui nous paraît être le désordre.

Argument 4 : FAUX

Tant que la valeur initiale recherchée est retrouvée dans le tableau, RECHERCHEV renvoie le menu correspondant à ce jour (« patates au beurre »). Si le jour cherché est mal orthographié, la fonction renvoie une erreur (#N/A).

Argument 4 : VRAI

Tant que la valeur initiale recherchée est retrouvée dans le tableau, RECHERCHEV remonte la valeur exacte du menu. Si le jour est mal orthographié (par exemple « vend » au lieu de « vendredi »), la fonction remonte le dernier résultat inférieur connu, comme pour le tri numérique.

Tri des données dans l’ordre alphanumérique

Les deux tris alphabétiques et numériques peuvent être mélangés, mais on va cumuler les écarts de l’un et l’autre tri, ce qui va augmenter les risques d’erreurs.

Argument 4 : FAUX

Tant que la valeur initiale recherchée est trouvée dans le tableau, RECHERCHEV renvoie le menu correspondant à ce jour (pour « vendredi » : « raviolis »). Si le jour cherché est mal orthographié, la fonction renvoie une erreur (#N/A), comme précédemment.

Argument 4 : VRAI

Tant que la valeur initiale recherchée est trouvée dans le tableau, RECHERCHEV remonte la valeur exacte du menu. Si la valeur cherchée est mal orthographiée (par exemple « vendr » au lieu de « vendredi » ou un chiffre ne figurant pas dans le tableau), la fonction remonte le dernier résultat inférieur connu, comme pour le tri numérique.

Tri des données dans l’ordre chrono-numérique

Dans la même idée que le tri alphanumérique, les jours sont classés par ordre chronologique, et pour une partie d’entre eux par une valeur numérique.

Argument 4 : FAUX

RECHERCHEV fonctionne toujours de la même manière : un résultat est affiché uniquement si la valeur cherchée exacte est trouvée dans le tableau.

Argument 4 : VRAI

Ici tout se complique. Tant que la valeur suit l’ordre alphabétique (lundi puis vendredi), RECHERCHEV renvoie le bon résultat, le bon menu du jour.

Quand on cherche le menu pour le « samedi », l’ordre alphabétique n’étant plus respecté, la fonction va chercher la dernière valeur connue inférieure à « samedi », à savoir « lundi » (ordre alphabétique : lundi, samedi, vendredi) et renvoie le menu du lundi (« burger »).

Quand on cherche le menu pour le « dimanche », RECHERCHEV remonte une erreur (#N/A) car il ne trouve aucune valeur connue inférieure à « dimanche » (il n’existe pas de jour commençant par A, B ou C !).

Analyse des codes d’erreurs

Lors de la mise en œuvre d’une formule, il est préférable de toujours vérifier les résultats obtenus sur quelques cas précis (les premiers de la liste ou ciblés sur des cas particuliers) avant de la dupliquer sur de nombreuses cellules. Cette habitude permet de décoder plus facilement les erreurs d’interprétation, et de les corriger rapidement.

RECHERCHEV peut afficher principalement deux erreurs de résultat, qui ont été vues en détail dans le paragraphe 4 Les erreurs à ne pas commettre (les erreurs d’interprétation).

Erreur #N/A

L’erreur #N/A peut apparaître dans plusieurs cas :

• La valeur initiale n’est pas trouvée (FAUX en argument 4)
• RECHERCHEV ne trouve ni valeur initiale exacte ni élément inférieur auquel se raccrocher (VRAI en argument 4)
• La table de données ne contient pas les valeurs initiales en première colonne : la fonction ne trouve pas les valeurs initiales demandées

Résultat incohérent

Si un résultat apparaît comme incohérent par rapport à ce que vous cherchez à remonter, il faut tout de suite regarder du côté de l’argument 4 concernant la précision du résultat remonté. Cet argument peut être :

• Non renseigné (avec la valeur VRAI par défaut),
• Avoir la valeur VRAI. Il suffit de le basculer sur FAUX pour que les résultats retrouvent de la cohérence.

Les fonctions similaires

Deux fonctions sont similaires à RECHERCHEV dans leur fonctionnement :

RECHERCHEH

RECHERCHEH fonctionne exactement de la même manière que RECHERCHEV, avec la même syntaxe, mais il est psychologiquement plus difficile à visualiser car cette fonction cherche dans des lignes (en horizontal) quand nous avons l’habitude de chercher dans des colonnes (en vertical) avec RECHERCHEV.

RECHERCHEX

RECHERCHEX est une nouvelle fonction apparue sous Excel 2019 pour remplacer RECHERCHEV et RECHERCHEH. Elle est encore très peu utilisée mais peut s’avérer utile pour compenser une partie des lacunes des deux fonctions qu’elle remplacent.

La structure de RECHERCHEX prend désormais en compte les colonnes Excel en lieu et place des colonnes de la matrice virtuelle : il n’y a plus besoin de compter !

Je reviendrai en détail sur cette fonction dans prochain article.

Les faux-amis

La fonction TROUVE paraît proche de RECHERCHEV mais son principe et son fonctionnement sont totalement différents. TROUVE renvoie la position du premier caractère d’une chaîne (pour une chaîne de 1 caractère, la fonction va remonter la position de ce caractère 😜) définie par l’utilisateur.

Bien que trouver et rechercher soient proches dans leur signification, nous sommes bien en présence ici de faux amis.

Ma recommandation

Sans remonter d’erreur systématique, RECHERCHEV peut facilement afficher des résultats erronés si les paramètres sont mal renseignés.

À ce jour, je n’ai pas rencontré de cas où je devais afficher une valeur approchante du résultat exact cherché. Pour moi, cet argument 4 facultatif est l’un des plus gros risques dans l’obtention de résultats corrects.

Si la fonction est facilement mise en œuvre, une autre source d’erreur consiste à mal compter les numéros de colonnes dans la matrice virtuelle créée par Excel. Mes exemples n’ont que deux colonnes, mais plus la matrice sera imposante, plus le risque de se tromper sera important. Et recompter systématiquement pour savoir si nos résultats sont dans la 20^e ou la 31^e colonne de la matrice virtuelle fait perdre un temps considérable dans l’utilisation de la fonction RECHERCHEV.

Ma recommandation est d’utiliser une autre fonction de recherche pour obtenir les mêmes résultats en combinant les fonctions INDEX et EQUIV. Si sa mise en œuvre est un peu moins simple, elle permet une plus grande optimisation du temps une fois sa structure acquise. J’y reviendrai dans un prochain article.

De votre côté, quelle fonction utilisez-vous pour effectuer vos recherches dans un tableau Excel ?