Rechercher une valeur dans un tableau Excel est l’une des tâches les plus courantes… et l’une des plus faciles à compliquer. Trouver le prix d’un produit, le service d’un collaborateur ou la note associée à un étudiant peut sembler simple, jusqu’au moment où le tableau évolue, où une colonne est déplacée ou où Excel affiche un mystérieux #N/A.

Heureusement, Excel propose plusieurs fonctions adaptées à chaque situation. Dans cet article, nous allons voir quand utiliser RECHERCHEX, RECHERCHEV, INDEX et EQUIV, avec des exemples concrets et des astuces pour éviter les erreurs classiques.

Préparer correctement le tableau avant la recherche

Avant de choisir une fonction, il est important de partir sur une base propre. Un tableau bien organisé rend les formules plus fiables et plus faciles à maintenir.

Prenons l’exemple d’un catalogue de produits :

  • La colonne A contient la référence du produit.
  • La colonne B contient le nom du produit.
  • La colonne C contient la catégorie.
  • La colonne D contient le prix.
  • La colonne E contient le stock disponible.

Imaginons que la cellule G2 contienne une référence saisie par l’utilisateur. L’objectif est de retrouver automatiquement le nom, le prix ou le stock correspondant.

Pour faciliter les recherches, transformez la plage en véritable tableau Excel avec le raccourci Ctrl + T. Vous pourrez ensuite lui attribuer un nom, comme Produits. Les formules utiliseront alors des références structurées, plus lisibles que des coordonnées classiques telles que A2:E500.

RECHERCHEX : la fonction à privilégier

Si vous utilisez une version récente d’Excel, RECHERCHEX est généralement le meilleur choix. Elle remplace avantageusement RECHERCHEV dans la plupart des situations.

Sa structure est la suivante :

=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_retour; [si_non_trouvé])

Pour retrouver le nom du produit correspondant à la référence placée en G2, vous pouvez écrire :

=RECHERCHEX(G2;A2:A100;B2:B100; »Référence inconnue »)

La formule recherche le contenu de G2 dans la colonne A, puis renvoie la valeur située sur la même ligne dans la colonne B. Si la référence n’existe pas, le texte Référence inconnue s’affiche à la place d’une erreur.

Avec un tableau nommé Produits, la formule devient encore plus claire :

=RECHERCHEX(G2;Produits[Référence];Produits[Produit]; »Référence inconnue »)

Cette écriture présente plusieurs avantages :

  • La colonne de recherche peut se trouver à gauche ou à droite de la colonne à retourner.
  • Il n’est pas nécessaire de compter le numéro de colonne.
  • La recherche exacte est utilisée par défaut.
  • Un message personnalisé peut remplacer l’erreur #N/A.
  • La formule reste lisible lorsque le tableau évolue.

Vous souhaitez récupérer le prix ? Il suffit de modifier la colonne de retour :

=RECHERCHEX(G2;Produits[Référence];Produits[Prix]; »Produit introuvable »)

Et pour le stock :

=RECHERCHEX(G2;Produits[Référence];Produits[Stock];0)

Dans ce dernier cas, Excel renverra 0 si aucune référence ne correspond.

Rechercher selon plusieurs critères

Une recherche basée sur une seule valeur ne suffit pas toujours. Par exemple, une entreprise peut vendre le même produit dans plusieurs magasins. La référence seule n’est alors pas forcément unique.

Avec RECHERCHEX, vous pouvez créer une recherche sur plusieurs critères en combinant les conditions. Supposons que :

  • La colonne A contienne le magasin.
  • La colonne B contienne la référence.
  • La colonne C contienne le prix.
  • La cellule G2 contienne le magasin recherché.
  • La cellule H2 contienne la référence recherchée.

La formule peut être écrite ainsi :

=RECHERCHEX(1;(A2:A100=G2)*(B2:B100=H2);C2:C100; »Aucun résultat »)

Chaque condition renvoie VRAI ou FAUX. La multiplication transforme les deux conditions en un résultat logique : seule la ligne qui correspond au magasin et à la référence obtient la valeur 1.

Cette technique est très pratique pour les tarifs par agence, les stocks par entrepôt ou les suivis de commandes. Elle demande toutefois une version récente d’Excel pour fonctionner correctement avec les tableaux dynamiques.

RECHERCHEV : l’ancienne méthode toujours utile

RECHERCHEV est probablement la fonction de recherche la plus connue d’Excel. Elle reste compatible avec de nombreuses anciennes versions et demeure présente dans beaucoup de fichiers professionnels.

Sa syntaxe est la suivante :

=RECHERCHEV(valeur_cherchée; table_matrice; no_index_col; [valeur_proche])

Pour rechercher le nom d’un produit à partir de sa référence :

=RECHERCHEV(G2;A2:E100;2;FAUX)

Cette formule signifie :

  • G2 est la valeur recherchée.
  • A2:E100 est la plage contenant les données.
  • 2 indique que le résultat se trouve dans la deuxième colonne de la plage.
  • FAUX impose une correspondance exacte.

Le dernier argument est essentiel. Si vous l’omettez, Excel peut effectuer une recherche approximative et renvoyer un résultat inattendu. Dans la majorité des recherches de références, de noms ou d’identifiants, utilisez donc FAUX.

Pour récupérer le prix, la formule devient :

=RECHERCHEV(G2;A2:E100;4;FAUX)

Le principal défaut de RECHERCHEV est sa rigidité. La valeur recherchée doit se trouver dans la première colonne de la plage. Impossible, par exemple, de chercher une référence située en colonne D pour renvoyer une valeur située en colonne A.

Autre limite : si vous insérez une colonne dans le tableau, le numéro d’index peut ne plus correspondre à la bonne donnée. Excel ne devine pas toujours vos intentions. Il compte, tout simplement.

Éviter les erreurs avec SIERREUR

Lorsque la valeur recherchée n’existe pas, RECHERCHEV et certaines autres fonctions affichent souvent l’erreur #N/A. Cette information est techniquement correcte, mais elle n’est pas toujours très agréable dans un tableau destiné à être lu ou imprimé.

La fonction SIERREUR permet de remplacer l’erreur par un message plus compréhensible :

=SIERREUR(RECHERCHEV(G2;A2:E100;2;FAUX); »Produit introuvable »)

Vous pouvez également renvoyer une cellule vide :

=SIERREUR(RECHERCHEV(G2;A2:E100;2;FAUX); » »)

Avec RECHERCHEX, cette gestion est directement intégrée dans la formule grâce au quatrième argument :

=RECHERCHEX(G2;A2:A100;B2:B100; »Produit introuvable »)

Cette approche est plus courte et plus lisible. Elle permet aussi d’éviter les tableaux remplis de messages d’erreur dès qu’une cellule de recherche est vide.

INDEX et EQUIV : le duo flexible

Avant l’arrivée de RECHERCHEX, le duo INDEX + EQUIV était la méthode favorite des utilisateurs avancés. Il reste particulièrement intéressant dans les fichiers compatibles avec d’anciennes versions d’Excel.

La fonction EQUIV renvoie la position d’une valeur dans une plage. Par exemple :

=EQUIV(G2;A2:A100;0)

Si la référence située en G2 se trouve à la sixième ligne de la plage A2:A100, EQUIV renverra 6. Le dernier argument, 0, demande une correspondance exacte.

La fonction INDEX, elle, renvoie une valeur située à une position donnée dans une plage :

=INDEX(B2:B100;6)

Elle renverra la sixième valeur de la plage B2:B100.

En combinant les deux fonctions, vous obtenez une recherche complète :

=INDEX(B2:B100;EQUIV(G2;A2:A100;0))

Excel commence par chercher la position de G2 dans la colonne A, puis utilise cette position pour renvoyer le nom correspondant dans la colonne B.

Cette méthode est plus robuste que RECHERCHEV sur un point important : la colonne de recherche et la colonne de résultat peuvent être placées n’importe où. Vous pouvez rechercher dans la colonne D et renvoyer une valeur de la colonne A sans modifier la structure du tableau.

Pour gérer les erreurs :

=SIERREUR(INDEX(B2:B100;EQUIV(G2;A2:A100;0)); »Référence inconnue »)

Rechercher une ligne et une colonne avec INDEX et EQUIV

Les recherches à deux dimensions sont très utiles pour consulter une grille tarifaire, un planning ou un tableau de résultats.

Imaginons un tableau dans lequel :

  • Les noms des produits sont en colonne A.
  • Les mois sont sur la ligne 1.
  • Les montants se trouvent dans la zone B2:M100.
  • La cellule G2 contient le produit recherché.
  • La cellule H2 contient le mois recherché.

La formule suivante renvoie le montant correspondant aux deux critères :

=INDEX(B2:M100;EQUIV(G2;A2:A100;0);EQUIV(H2;B1:M1;0))

Le premier EQUIV identifie la ligne du produit. Le second identifie la colonne du mois. INDEX récupère ensuite la valeur située à l’intersection des deux.

Cette technique peut sembler un peu plus longue, mais elle est redoutablement efficace pour les tableaux à double entrée.

Retourner plusieurs résultats avec FILTRE

Et si plusieurs lignes correspondent à votre recherche ? RECHERCHEX renvoie généralement un seul résultat. Pour afficher toutes les correspondances, utilisez la fonction FILTRE, disponible dans les versions récentes d’Excel.

Supposons que la colonne C contienne une catégorie et que la cellule G2 contienne la catégorie à filtrer :

=FILTRE(A2:E100;C2:C100=G2; »Aucun produit trouvé »)

Excel renverra automatiquement toutes les lignes correspondant à la catégorie choisie. La zone de résultat s’étendra automatiquement : c’est le principe des tableaux dynamiques.

Vous pouvez aussi ne retourner que certaines colonnes. Par exemple, pour afficher uniquement le nom et le prix :

=FILTRE(B2:D100;C2:C100=G2; »Aucun résultat »)

Attention à laisser suffisamment d’espace sous et à droite de la cellule contenant la formule. Si une autre donnée bloque le déploiement du résultat, Excel affichera l’erreur #PROPAGATION!.

Les pièges fréquents à éviter

Une formule de recherche peut être parfaitement écrite et malgré tout renvoyer un mauvais résultat. Voici les vérifications les plus utiles :

  • Les espaces invisibles : une référence contenant un espace à la fin est différente de la même référence sans espace. La fonction SUPPRESPACE peut aider à nettoyer les données.
  • Les nombres stockés comme texte : le nombre 123 et le texte « 123 » ne sont pas toujours considérés comme identiques. Utilisez éventuellement CNUM ou convertissez la colonne au bon format.
  • La correspondance approximative : avec RECHERCHEV, indiquez FAUX pour rechercher une correspondance exacte.
  • Les plages incohérentes : les plages de recherche et de retour doivent avoir le même nombre de lignes avec RECHERCHEX.
  • Les doublons : une fonction comme RECHERCHEX renvoie la première correspondance trouvée. Si les doublons sont possibles, vérifiez l’unicité de vos identifiants.
  • Les cellules vides : prévoyez un résultat spécifique lorsque la cellule de recherche n’est pas renseignée.

Quelle fonction choisir selon votre situation ?

Pour vous aider à faire le bon choix, voici un résumé rapide :

  • RECHERCHEX : le choix recommandé dans les versions récentes d’Excel pour une recherche exacte, claire et flexible.
  • RECHERCHEV : pratique pour maintenir un ancien fichier ou travailler avec une version d’Excel qui ne propose pas RECHERCHEX.
  • INDEX + EQUIV : idéal pour les recherches flexibles, notamment lorsque la colonne de recherche n’est pas la première.
  • FILTRE : à utiliser lorsque plusieurs résultats doivent être affichés.
  • SIERREUR : indispensable pour remplacer les erreurs par un message compréhensible ou une cellule vide.

Dans un nouveau fichier, commencez par transformer vos données en tableau avec Ctrl + T, donnez-lui un nom explicite et privilégiez les références structurées. Votre fichier sera plus lisible, plus solide et bien plus simple à faire évoluer.

Une recherche Excel efficace ne dépend donc pas uniquement de la formule choisie. La qualité des données, le type de correspondance et la structure du tableau comptent tout autant. Une fois ces principes maîtrisés, retrouver une information dans plusieurs milliers de lignes devient presque aussi simple que de demander son chemin à Excel.