Rechercher une valeur dans un tableau Excel est l’une des tâches les plus courantes… et l’une des plus faciles à compliquer. Vous cherchez un prix à partir d’une référence, le nom d’un client à partir d’un identifiant ou encore le stock disponible pour un article ? Excel possède plusieurs outils pour y parvenir, des formules historiques aux fonctions les plus récentes.

Dans cet article, nous allons passer en revue les méthodes les plus efficaces pour rechercher une valeur dans un tableau Excel. L’objectif : choisir la bonne formule selon votre situation, éviter les erreurs classiques et construire des fichiers plus fiables.

Préparer correctement le tableau de recherche

Avant même de parler de formule, un bon tableau facilite énormément les recherches. Imaginons un tableau contenant les colonnes suivantes :

  • Référence
  • Désignation
  • Catégorie
  • Prix unitaire
  • Stock

La première colonne contient généralement la valeur recherchée, par exemple une référence produit. Les colonnes suivantes contiennent les informations à renvoyer.

Pour transformer cette plage en véritable tableau Excel, cliquez dans vos données puis utilisez le raccourci Ctrl + T. Vérifiez ensuite que l’option « Mon tableau comporte des en-têtes » est cochée.

Cette étape présente plusieurs avantages :

  • Le tableau s’agrandit automatiquement lorsque vous ajoutez des lignes.
  • Les formules deviennent plus lisibles grâce aux références structurées.
  • Les plages de recherche restent dynamiques.
  • Les filtres et la mise en forme sont appliqués automatiquement.

Un tableau nommé Produits pourra ainsi être utilisé dans une formule avec des références comme Produits[Référence] ou Produits[Prix unitaire]. C’est bien plus clair qu’une formule remplie de références comme $A$2:$E$5000.

La méthode moderne : utiliser RECHERCHEX

Si vous utilisez Microsoft 365 ou une version récente d’Excel, RECHERCHEX est généralement le meilleur choix. Cette fonction remplace avantageusement RECHERCHEV dans la plupart des cas.

Sa structure est la suivante :

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

Supposons que la référence à rechercher soit saisie en G2. Pour récupérer le prix correspondant dans le tableau Produits, utilisez :

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

Excel cherche la valeur de G2 dans la colonne Référence, puis renvoie le prix situé sur la même ligne. Si aucune correspondance n’est trouvée, le texte « Référence inconnue » s’affiche à la place de l’erreur #N/A.

Cette possibilité de personnaliser le résultat en cas d’absence est particulièrement pratique. Vous pouvez afficher un message plus explicite :

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

RECHERCHEX accepte également les recherches approximatives, les recherches inversées et les caractères génériques. Dans la majorité des recherches classiques, cependant, le fonctionnement exact par défaut est celui qu’il vous faut.

Pourquoi préférer RECHERCHEX à RECHERCHEV ?

RECHERCHEV a longtemps été la formule incontournable. Elle reste utile, mais elle impose plusieurs contraintes qui peuvent devenir gênantes.

Avec RECHERCHEV, la colonne contenant la valeur recherchée doit être située à gauche de la colonne à renvoyer. Si votre tableau contient le prix avant la référence, la formule ne pourra pas effectuer directement une recherche vers la gauche.

RECHERCHEX, elle, sépare clairement les deux plages :

=RECHERCHEX(G2;Produits[Référence];Produits[Prix unitaire])

La colonne de recherche et la colonne de résultat peuvent donc être placées où vous le souhaitez. Vous pouvez aussi insérer une colonne dans le tableau sans devoir modifier un numéro d’index dans la formule.

Autre avantage : RECHERCHEX renvoie facilement une valeur située à gauche de la colonne de recherche. Si vous recherchez une désignation à partir d’un prix, la formule reste tout aussi simple.

En résumé, RECHERCHEX est généralement préférable lorsque :

  • Votre version d’Excel prend en charge cette fonction.
  • Vous voulez rechercher vers la gauche ou vers la droite.
  • Vous souhaitez gérer proprement les valeurs introuvables.
  • Vous voulez éviter les numéros de colonnes difficiles à maintenir.

La méthode classique avec RECHERCHEV

Sur les anciennes versions d’Excel, RECHERCHEV reste une solution efficace. Sa syntaxe est la suivante :

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

Pour rechercher le prix correspondant à la référence située en G2 dans une plage allant de A2 à E500, utilisez :

=RECHERCHEV(G2;$A$2:$E$500;4;FAUX)

Le chiffre 4 indique qu’Excel doit renvoyer la valeur de la quatrième colonne de la plage. Le dernier argument, FAUX, demande une correspondance exacte.

C’est un point essentiel. Si vous omettez FAUX ou utilisez VRAI, Excel peut effectuer une recherche approximative. Le résultat peut alors être incorrect si la première colonne n’est pas triée correctement.

Pour afficher un message personnalisé lorsque la référence n’existe pas, combinez RECHERCHEV avec SIERREUR :

=SIERREUR(RECHERCHEV(G2;$A$2:$E$500;4;FAUX); »Référence inconnue »)

Cette formule fonctionne, mais elle est moins souple que RECHERCHEX. Le numéro de colonne peut notamment devenir faux si vous ajoutez ou déplacez des colonnes dans la plage.

Associer INDEX et EQUIV pour une formule robuste

Avant l’arrivée de RECHERCHEX, le duo INDEX + EQUIV était la solution de référence pour effectuer des recherches flexibles.

La formule suivante recherche la référence de G2 dans la colonne A et renvoie le prix de la colonne D :

=INDEX($D$2:$D$500;EQUIV(G2;$A$2:$A$500;0))

La fonction EQUIV trouve la position de la référence dans la colonne A. La fonction INDEX renvoie ensuite la valeur située à cette position dans la colonne D.

Le dernier argument de EQUIV est 0. Il correspond à une recherche exacte. Sans lui, Excel peut rechercher une correspondance approximative et produire un résultat inattendu.

Cette méthode reste intéressante lorsque :

  • Vous travaillez avec une ancienne version d’Excel.
  • Vous devez effectuer une recherche vers la gauche.
  • Vous souhaitez maîtriser précisément les plages utilisées.
  • Vous utilisez déjà des modèles construits autour d’INDEX et EQUIV.

Pour gérer l’erreur lorsque la valeur n’existe pas, vous pouvez écrire :

=SIERREUR(INDEX($D$2:$D$500;EQUIV(G2;$A$2:$A$500;0)); »Produit introuvable »)

Rechercher plusieurs informations en une seule formule

L’un des atouts de RECHERCHEX est sa capacité à renvoyer plusieurs colonnes simultanément. Si vous souhaitez obtenir la désignation, la catégorie, le prix et le stock associés à une référence, utilisez :

=RECHERCHEX(G2;Produits[Référence];Produits[[Désignation]:[Stock]]; »Référence inconnue »)

Dans Excel Microsoft 365, le résultat se déverse automatiquement dans les cellules voisines. Il n’est donc plus nécessaire de créer une formule différente pour chaque colonne.

Cette fonctionnalité, appelée déversement dynamique, permet de simplifier considérablement les tableaux de bord. Attention toutefois à laisser suffisamment de cellules libres à droite de la formule. Si une cellule est déjà occupée, Excel affiche une erreur de déversement.

Afficher toutes les lignes correspondantes avec FILTRE

Les fonctions de recherche classiques renvoient généralement une seule valeur. Mais que faire si plusieurs lignes correspondent au même critère ? C’est là que la fonction FILTRE devient très utile.

Supposons que vous souhaitiez afficher tous les produits appartenant à la catégorie indiquée en G2 :

=FILTRE(Produits;Produits[Catégorie]=G2; »Aucun produit trouvé »)

Excel renvoie alors toutes les lignes correspondant à la catégorie. La liste s’adapte automatiquement si les données changent.

Vous pouvez également filtrer selon plusieurs critères. Pour rechercher les produits d’une catégorie dont le stock est inférieur à 10 :

=FILTRE(Produits;(Produits[Catégorie]=G2)*(Produits[Stock]<10); »Aucun produit correspondant »)

Dans cette formule, l’opérateur astérisque représente un « ET ». Les deux conditions doivent être vraies pour qu’une ligne soit affichée.

Pour représenter un « OU », utilisez le signe plus :

=FILTRE(Produits;(Produits[Catégorie]=G2)+(Produits[Catégorie]=G3); »Aucun produit correspondant »)

Rechercher une valeur avec plusieurs critères

Une recherche basée sur une seule référence ne suffit pas toujours. Imaginez une liste de tarifs qui dépend à la fois du produit et de la région. Il faut alors prendre en compte deux critères.

Avec RECHERCHEX, vous pouvez combiner les critères directement :

=RECHERCHEX(G2&H2;Tarifs[Produit]&Tarifs[Région];Tarifs[Prix]; »Tarif introuvable »)

Excel compare ici la combinaison du produit et de la région recherchés avec la combinaison présente dans le tableau. Pour éviter les ambiguïtés, vous pouvez ajouter un séparateur :

=RECHERCHEX(G2& »| »&H2;Tarifs[Produit]& »| »&Tarifs[Région];Tarifs[Prix]; »Tarif introuvable »)

Une autre méthode consiste à utiliser FILTRE :

=FILTRE(Tarifs[Prix];(Tarifs[Produit]=G2)*(Tarifs[Région]=H2); »Tarif introuvable »)

Cette seconde solution peut renvoyer plusieurs résultats si plusieurs tarifs répondent aux mêmes critères. Elle est donc adaptée lorsque les doublons sont possibles.

Rechercher une valeur partielle avec les caractères génériques

Vous ne connaissez pas la référence complète ? Les caractères génériques peuvent vous aider.

  • * remplace un nombre quelconque de caractères.
  • ? remplace un seul caractère.

Par exemple, pour retrouver le premier produit dont le nom contient le texte saisi en G2 :

=RECHERCHEX(« * »&G2& »* »;Produits[Désignation];Produits[Référence]; »Aucun résultat »;2)

Le dernier argument 2 active l’utilisation des caractères génériques. Les astérisques permettent de rechercher le texte n’importe où dans la désignation.

Pour afficher tous les résultats contenant un mot, FILTRE est souvent plus adapté :

=FILTRE(Produits;ESTNUM(CHERCHE(G2;Produits[Désignation])); »Aucun résultat »)

La fonction CHERCHE n’est pas sensible à la casse. Ainsi, « clavier », « Clavier » et « CLAVIER » seront traités de la même manière.

Éviter les erreurs fréquentes

Une formule correcte peut tout de même produire un résultat inattendu si les données ne sont pas homogènes. Voici les contrôles à effectuer en priorité :

  • Vérifiez les espaces invisibles avec la fonction SUPPRESPACE.
  • Contrôlez que les nombres ne sont pas stockés comme du texte.
  • Utilisez FAUX ou 0 pour une recherche exacte.
  • Évitez les références de plage trop larges lorsque le fichier est volumineux.
  • Gérez les valeurs introuvables avec SIERREUR ou l’argument prévu par RECHERCHEX.
  • Assurez-vous qu’aucune cellule ne bloque le déversement d’une formule dynamique.

Un problème courant concerne les références qui semblent identiques mais contiennent un espace à la fin. « AB123 » et « AB123 » suivi d’un espace ne sont pas toujours considérés comme la même valeur. Un nettoyage préalable des données peut donc faire disparaître bien des mystères — et quelques cheveux blancs.

Quelle formule choisir selon votre besoin ?

Pour vous repérer rapidement :

  • RECHERCHEX : le choix recommandé dans les versions récentes d’Excel pour rechercher une valeur unique.
  • RECHERCHEV : une solution compatible avec les anciennes versions, à condition de respecter l’organisation des colonnes.
  • INDEX + EQUIV : une méthode robuste et flexible, très utile dans les anciens fichiers.
  • FILTRE : la meilleure option pour afficher plusieurs résultats ou combiner des critères.
  • SIERREUR : à utiliser pour remplacer les messages d’erreur par une information claire.

Dans un fichier professionnel, prenez également le temps de nommer vos tableaux et vos colonnes. Une formule comme =RECHERCHEX(G2;Produits[Référence];Produits[Prix unitaire]) est immédiatement compréhensible par une autre personne. C’est un détail qui compte lorsque le classeur évolue ou doit être transmis.

La recherche de valeurs dans Excel n’est donc pas limitée à une seule formule. En choisissant l’outil adapté à votre tableau, vous gagnez en lisibilité, en fiabilité et en temps. Et une fois que RECHERCHEX et FILTRE sont bien maîtrisées, les tableaux de bord Excel deviennent nettement plus simples à construire.