Comment résoudre les erreurs de la fonction RECHERCHEV sur Excel

Dans ce tutoriel, je vais vous montrer comment maîtriser la fonction RECHERCHEV dans Excel, un outil indispensable pour la recherche et l'analyse de données.

Nous aborderons les pièges les plus courants et leurs solutions pour vous permettre d'utiliser cette fonction efficacement dans vos projets professionnels.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous gérons l'inventaire d'une librairie. Ce tableau contient des informations essentielles sur chaque livre : son ISBN (numéro d'identification unique), son titre, son auteur, son prix et son stock actuel.

Nous disposons également d’un second tableau qui nous permettra d'afficher automatiquement le prix d'un livre en utilisant uniquement son ISBN.

2. Pourquoi les prix affichés sont faux

Dans l’exemple que nous avons à l’écran, la fonction RECHERCHEV est utilisée pour retrouver le prix de vente de l’ISBN :

=RECHERCHEV(B9;B23:F47;3)

Ici, nous souhaitons :

• Rechercher la valeur de la cellule B9 dans laquelle l’ISBN est saisie,
• À l’intérieur de la plage des cellules B23 jusqu’à F47, c’est-à-dire toutes les cellules de la base
• Et nous souhaitons obtenir la valeur située dans la troisième colonne (prix de vente)

Mais cette formule nous retourne une erreur #N/A. Pourquoi ?

Il faut en effet savoir que la fonction RECHERCHEV peut utiliser un quatrième argument qui permet de spécifier le type de recherche que nous souhaitons utiliser :

• VRAI permet d’effectuer une recherche approximative dans la base, afin de retourner le résultat le plus proche, à condition de la base soit classée par ordre alphabétique.
• FAUX nécessite une recherche exacte. Si la valeur n’est pas retrouvée, alors la fonction RECEHRCHEV renverra une erreur #N/A.

Ici, cet argument est omis, sa valeur est donc égale à VRAI et demande à Excel de faire une recherche approximative.

Sauf, que la base n’est pas classée, donc même si la valeur existe bien dans la base, la fonction retourne une erreur.

Maintenant, nous lui donnons explicitement la valeur FAUX :

=RECHERCHEV(D2; A2:E6; 3; FAUX)

En utilisant FAUX (ou 0), nous indiquons à Excel que nous voulons une correspondance exacte. Cette modification garantit que nous obtiendrons uniquement le prix correspondant précisément à l'ISBN recherché.

3. Le décalage de plage

Maintenant, si nous souhaitons obtenir le prix de vente des deux références suivantes, nous pouvons étendre la formule sur les cellules situées en dessous en utilisant la poignée de recopie :

Nous avons ici encore une erreur.

En effet, en étendant la formule vers le bas, la formule devient alors automatiquement :

=RECHERCHEV(B10;B25:F48;4;FAUX)

La plage de recherche se décale alors d'une ligne à chaque copie, excluant progressivement les premières lignes de notre base de données. La plage finit par inclure des lignes vides ou non pertinentes.

Pour régler ce problème, nous allons donc devoir figer la plage des cellules correspondantes en ajoutant des dollars.

Pour ajouter automatiquement les symboles « $« sur une plage de cellules, nous pouvons utiliser la touche [F4]. Attention toutefois, sur les claviers réduits, comme ceux des PC portables, la touche [F4] peut être affectée aux fonctions multimédia. Dans ce cas, il faudra appuyer en même temps sur la touche [Fn], située la plupart du temps en bas à gauche du clavier.

Avec cette correction, même copiée vers le bas, la formule conservera la bonne plage de recherche. Pour rendre la formule encore plus robuste, nous pouvons ajouter une colonne pour le titre :

=RECHERCHEV(B10;$B$23:$F$46;4;FAUX)

4. La recherche n'est pas dans la première colonne

Une des contraintes de la fonction RECHERCHEV, c’est que celle-ci suppose que la colonne de recherche est située en première position dans le tableau de recherche.

Or, supposons maintenant que nous voulions chercher un livre par son titre plutôt que par son ISBN. Notre premier réflexe serait d'écrire :

=RECHERCHEV(C16;$B$23:$F$47;4;FAUX)

Cette formule renvoie alors une erreur #N/A.

Pour résoudre ce problème, nous avons plusieurs options :

Solution A - Réorganiser les données :

• Déplacer la colonne "Titre du livre" en première position
• Adapter les indices dans la formule RECHERCHEV

Mais cette solution n'est pas toujours pratique ou possible dans un environnement professionnel. Voici une meilleure approche :

Solution B - Utiliser INDEX/EQUIV ou une autre fonction de recherche avancée comme RECHERCHEX.

La fonction INDEX/EQUIV, même si elle est un peu moins intuitive à utiliser est très puissante, et elle a l’avantage d’être compatible avec toutes les versions d’Excel :

=INDEX($D$23:$D$47;EQUIV(B16;$F$23:$F$47;0))

Ici, nous faisons appel à deux fonctions Excel.

La première « INDEX($D$23:$D$47;...) » permet de sélectionner la plage des prix (colonne D) dans laquelle nous voulons récupérer une valeur. Les références sont absolues ($) pour permettre la copie de la formule.

La seconde, « EQUIV(B16;$F$23:$F$47;0) » permet d’obtenir la position exacte du titre "Le Secret des Étoiles" dans la colonne des titres (colonne B).

Ici :

• Le premier argument est la valeur recherchée
• Le deuxième argument est la plage où chercher (colonne des titres)
• Le "0" final indique que nous voulons une correspondance exacte (comme FAUX dans RECHERCHEV)

La combinaison des deux fonctions : INDEX utilise le numéro de ligne renvoyé par EQUIV pour extraire le prix correspondant dans la colonne D.

Cette méthode est plus puissante que RECHERCHEV car :

• Elle permet de chercher dans n'importe quelle colonne
• Elle offre plus de flexibilité pour les recherches complexes
• Elle peut également être adaptée pour des recherches bidirectionnelles