Rechercher une valeur dans un tableau Excel et afficher automatiquement l’information associée est l’un des besoins les plus fréquents dans un fichier de gestion. Trouver le prix d’un produit, le service d’un salarié, le stock disponible ou encore la note d’un étudiant : Excel peut s’en charger en quelques secondes.
La bonne nouvelle, c’est qu’il existe plusieurs méthodes. La plus moderne repose sur RECHERCHEX, tandis que RECHERCHEV, INDEX et EQUIV restent très utiles, notamment pour travailler avec d’anciennes versions d’Excel.
Dans cet article, nous allons partir d’exemples concrets pour comprendre quelle formule utiliser, comment éviter les erreurs et comment afficher un message personnalisé lorsque la valeur recherchée n’existe pas.
Le principe d’une recherche dans Excel
Imaginons un tableau contenant une liste de produits :
- Une colonne A avec la référence du produit ;
- Une colonne B avec son nom ;
- Une colonne C avec son prix ;
- Une colonne D avec la quantité disponible.
Vous saisissez une référence dans une cellule, par exemple F2, et vous souhaitez qu’Excel affiche automatiquement le nom du produit correspondant dans G2.
La formule doit donc effectuer deux opérations :
- chercher la valeur saisie dans la colonne des références ;
- afficher l’information située sur la même ligne.
Si la référence « P-104 » correspond au produit « Clavier sans fil », Excel doit retrouver cette ligne et afficher le nom associé. C’est exactement le rôle des fonctions de recherche.
La méthode recommandée avec RECHERCHEX
Si vous utilisez Microsoft 365 ou une version récente d’Excel, RECHERCHEX est généralement la meilleure solution. Elle est plus souple et plus lisible que RECHERCHEV.
La structure de la fonction est la suivante :
=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé)
Dans notre exemple, si les références se trouvent en A2:A100, les noms des produits en B2:B100 et la référence à rechercher en F2, la formule sera :
=RECHERCHEX(F2;A2:A100;B2:B100)
Excel recherche le contenu de F2 dans la plage A2:A100, puis renvoie la valeur située sur la même ligne dans B2:B100.
Cette formule présente un avantage important : la colonne de recherche n’a pas besoin d’être située à gauche de la colonne à afficher. Vous pouvez rechercher une valeur dans la colonne C et afficher une information située dans la colonne A. RECHERCHEX ne vous impose pas l’organisation rigide de RECHERCHEV.
Afficher un message si la valeur est introuvable
Lorsqu’Excel ne trouve pas la valeur recherchée, RECHERCHEX renvoie une erreur. Pour éviter d’afficher un message peu accueillant comme #N/A, vous pouvez préciser un texte à afficher en cas d’absence.
La formule devient :
=RECHERCHEX(F2;A2:A100;B2:B100; »Produit introuvable »)
Le quatrième argument correspond au message à afficher si aucune correspondance n’est trouvée.
Vous pouvez bien sûr adapter ce message :
- « Référence inconnue » pour une recherche de produit ;
- « Client non trouvé » pour une base commerciale ;
- « Aucun résultat » pour un tableau de suivi ;
- « » pour laisser la cellule vide.
Par exemple :
=RECHERCHEX(F2;A2:A100;B2:B100; » »)
Cette version évite qu’une erreur visuelle apparaisse dans votre tableau de bord lorsque la cellule de recherche est encore vide.
Éviter une recherche lorsque la cellule est vide
Si F2 ne contient aucune valeur, la formule peut afficher « Produit introuvable ». Ce n’est pas toujours très élégant. Pour ne rien afficher tant que l’utilisateur n’a pas saisi de référence, utilisez la fonction SI :
=SI(F2= » »; » »;RECHERCHEX(F2;A2:A100;B2:B100; »Produit introuvable »))
Excel vérifie d’abord si F2 est vide. Si c’est le cas, la cellule reste vide. Sinon, la recherche est exécutée.
Cette technique est particulièrement pratique dans les formulaires, les fiches produits ou les tableaux de suivi. Elle évite de remplir votre feuille d’erreurs ou de messages inutiles avant même que l’utilisateur ait commencé à travailler.
Rechercher et afficher un prix
Supposons que le prix se trouve dans la colonne C. Pour afficher le prix correspondant à la référence saisie dans F2, utilisez :
=RECHERCHEX(F2;A2:A100;C2:C100; »Prix introuvable »)
Si la cellule renvoie un nombre comme 29,9, appliquez ensuite un format monétaire à la cellule. Pour cela :
- sélectionnez la cellule contenant la formule ;
- ouvrez l’onglet Accueil ;
- choisissez le format Monétaire ou Comptabilité.
La formule reste une formule de recherche : le format monétaire ne modifie pas la valeur, il change seulement son apparence.
Rechercher plusieurs informations
Vous n’êtes pas limité à une seule information. Vous pouvez utiliser une formule dans chaque cellule du formulaire :
=RECHERCHEX(F2;A2:A100;B2:B100; » »)
Cette formule affiche le nom du produit.
=RECHERCHEX(F2;A2:A100;C2:C100; » »)
Celle-ci affiche son prix.
=RECHERCHEX(F2;A2:A100;D2:D100; » »)
Et celle-ci renvoie le stock disponible.
Vous obtenez ainsi une fiche dynamique : l’utilisateur change la référence dans F2 et toutes les informations se mettent à jour instantanément. C’est simple, efficace et bien plus fiable que de recopier manuellement les données.
Utiliser RECHERCHEX avec un tableau Excel
Transformer votre plage en véritable tableau Excel est une excellente habitude. Sélectionnez vos données, puis utilisez le raccourci Ctrl + T. Cochez l’option indiquant que votre tableau comporte des en-têtes.
Imaginons que le tableau s’appelle Produits et qu’il contient les colonnes Référence, Nom, Prix et Stock.
La formule peut alors s’écrire ainsi :
=RECHERCHEX(F2;Produits[Référence];Produits[Nom]; »Produit introuvable »)
Cette écriture utilise des références structurées. Elle est souvent plus facile à comprendre qu’une série de plages comme A2:A100. Surtout, le tableau s’agrandit automatiquement lorsque vous ajoutez une nouvelle ligne.
Avec une plage classique, vous pourriez oublier d’étendre la formule jusqu’à la nouvelle ligne. Avec un tableau Excel, les références s’adaptent automatiquement. Un petit détail qui évite de grandes recherches dans un fichier devenu volumineux.
La méthode classique avec RECHERCHEV
Si vous utilisez une ancienne version d’Excel qui ne propose pas RECHERCHEX, vous pouvez utiliser RECHERCHEV.
Sa structure est la suivante :
=RECHERCHEV(valeur_cherchée; table; numéro_colonne; FAUX)
Pour rechercher la référence saisie en F2 et afficher le nom situé dans la deuxième colonne de la plage A2:D100, écrivez :
=RECHERCHEV(F2;A2:D100;2;FAUX)
Le dernier argument, FAUX, est essentiel. Il demande à Excel de rechercher une correspondance exacte. Si vous l’omettez, Excel peut effectuer une recherche approximative et renvoyer un résultat incorrect lorsque les données ne sont pas triées.
Le chiffre 2 signifie que la valeur à renvoyer se trouve dans la deuxième colonne de la plage A2:D100. Pour afficher le prix situé dans la troisième colonne, utilisez :
=RECHERCHEV(F2;A2:D100;3;FAUX)
Pour le stock situé dans la quatrième colonne :
=RECHERCHEV(F2;A2:D100;4;FAUX)
Gérer les erreurs avec SIERREUR
RECHERCHEV renvoie une erreur si la valeur n’est pas trouvée. Pour afficher un message plus propre, placez la formule dans SIERREUR :
=SIERREUR(RECHERCHEV(F2;A2:D100;2;FAUX); »Produit introuvable »)
Si la recherche fonctionne, Excel affiche le nom du produit. Dans le cas contraire, le message personnalisé apparaît.
Vous pouvez également combiner SIERREUR et une vérification de cellule vide :
=SI(F2= » »; » »;SIERREUR(RECHERCHEV(F2;A2:D100;2;FAUX); »Produit introuvable »))
Cette formule est un peu plus longue, mais elle donne un résultat propre dans toutes les situations courantes.
Rechercher avec INDEX et EQUIV
Avant l’arrivée de RECHERCHEX, le duo INDEX + EQUIV était très apprécié pour sa flexibilité. Il reste d’ailleurs parfaitement valable aujourd’hui.
Pour afficher le nom du produit correspondant à F2 :
=INDEX(B2:B100;EQUIV(F2;A2:A100;0))
La fonction EQUIV cherche la position de F2 dans la plage A2:A100. Le dernier argument, 0, impose une correspondance exacte.
La fonction INDEX utilise ensuite cette position pour renvoyer la valeur correspondante dans B2:B100.
Pour éviter l’erreur lorsque la référence n’existe pas :
=SIERREUR(INDEX(B2:B100;EQUIV(F2;A2:A100;0)); »Produit introuvable »)
L’intérêt d’INDEX et EQUIV est de pouvoir rechercher dans une colonne et renvoyer une donnée située à gauche ou à droite, sans contrainte particulière sur la structure du tableau.
Rechercher avec plusieurs critères
Parfois, une seule valeur ne suffit pas. Vous souhaitez par exemple rechercher un prix en fonction d’un produit et d’une couleur, ou identifier une commande à partir d’un client et d’une date.
Avec RECHERCHEX, vous pouvez combiner plusieurs critères en multipliant des tests logiques :
=RECHERCHEX(1;(A2:A100=F2)*(B2:B100=G2);C2:C100; »Aucun résultat »)
Dans cet exemple, Excel recherche la ligne où la colonne A correspond à F2 et où la colonne B correspond à G2. La valeur renvoyée provient de la colonne C.
Cette méthode peut sembler mystérieuse au premier regard. En réalité, chaque comparaison renvoie VRAI ou FAUX. Excel transforme ensuite ces résultats en 1 et 0. La multiplication permet d’identifier la ligne où les deux critères sont vrais en même temps.
Les erreurs fréquentes à vérifier
Une formule de recherche peut être correcte et pourtant ne rien renvoyer à cause de la qualité des données. Voici les contrôles les plus utiles :
- Vérifiez les espaces invisibles avant ou après la valeur recherchée ;
- Assurez-vous que les nombres ne sont pas enregistrés comme du texte ;
- Contrôlez l’orthographe et les caractères spéciaux ;
- Utilisez une correspondance exacte avec FAUX ou 0 ;
- Vérifiez que les plages de recherche et de résultat ont la même taille ;
- Évitez les doublons dans la colonne contenant les références.
Un espace ajouté à la fin d’une référence suffit parfois à empêcher Excel de trouver une correspondance. La fonction SUPPRESPACE peut vous aider à nettoyer les données :
=SUPPRESPACE(A2)
Pour nettoyer une colonne entière, copiez la formule vers le bas, puis copiez les résultats et utilisez Collage spécial > Valeurs.
Quelle fonction choisir ?
Pour retenir l’essentiel :
- RECHERCHEX : le choix recommandé dans les versions récentes d’Excel ;
- RECHERCHEV : pratique pour les fichiers compatibles avec les anciennes versions ;
- INDEX + EQUIV : une méthode robuste et flexible ;
- SIERREUR : indispensable pour remplacer les messages d’erreur par un texte clair ;
- SI : utile pour ne rien afficher lorsque la cellule de recherche est vide.
Dans la plupart des nouveaux fichiers, commencez par RECHERCHEX. Sa syntaxe est claire, elle fonctionne dans les deux sens et elle s’adapte mieux aux tableaux modernes.
Une fois la formule installée, votre tableau devient beaucoup plus interactif : vous saisissez une référence, un nom ou un identifiant, et Excel affiche automatiquement les informations correspondantes. Pas besoin de parcourir manuellement des centaines de lignes. Votre fichier travaille pour vous, ce qui est précisément l’un des petits plaisirs d’Excel.