Vous cherchez une valeur dans un tableau Excel et vous avez entendu parler de la fonction « Recherche si » ? Petite précision utile : dans Excel, la fonction dédiée aux recherches modernes s’appelle RECHERCHEX. Elle permet de retrouver une information dans une colonne ou une ligne, puis de renvoyer la donnée associée.
Plus souple que RECHERCHEV, plus lisible que le duo INDEX + EQUIV, RECHERCHEX est rapidement devenue un incontournable pour construire des fichiers fiables. Dans cet article, nous allons voir sa syntaxe, ses principaux arguments, des exemples concrets et quelques astuces qui font vraiment la différence.
À quoi sert la fonction RECHERCHEX ?
Imaginez un tableau contenant une liste de produits, leur référence, leur catégorie et leur prix. Vous saisissez une référence dans une cellule et vous souhaitez qu’Excel affiche automatiquement le prix correspondant.
C’est exactement le rôle de RECHERCHEX : elle cherche une valeur dans une plage, puis renvoie la valeur située sur la même ligne ou la même colonne dans une autre plage.
Par exemple, si la cellule H2 contient une référence produit, la formule suivante peut récupérer son prix :
=RECHERCHEX(H2;A2:A100;D2:D100)Excel recherche le contenu de H2 dans la plage A2:A100, puis renvoie la valeur correspondante de D2:D100.
Pourquoi utiliser RECHERCHEX plutôt que RECHERCHEV ?
- Elle peut rechercher vers la gauche comme vers la droite.
- Elle utilise une correspondance exacte par défaut.
- Elle permet d’afficher un message personnalisé si la valeur n’est pas trouvée.
- Elle peut renvoyer plusieurs colonnes à la fois.
- Elle propose une recherche du premier ou du dernier résultat.
- Sa syntaxe est plus claire et plus facile à maintenir.
La syntaxe de RECHERCHEX
Voici la syntaxe complète de la fonction :
=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])Les trois premiers arguments sont essentiels. Les trois suivants sont facultatifs, mais ils permettent d’adapter la recherche à presque toutes les situations.
- valeur_cherchée : la valeur qu’Excel doit rechercher.
- tableau_recherche : la plage dans laquelle Excel doit effectuer la recherche.
- tableau_renvoyé : la plage contenant la donnée à retourner.
- si_non_trouvé : le texte ou la valeur à afficher si aucune correspondance n’est trouvée.
- mode_correspondance : le type de correspondance souhaité.
- mode_recherche : l’ordre dans lequel Excel doit parcourir les données.
Les arguments entre crochets sont optionnels. Vous pouvez donc commencer simplement avec :
=RECHERCHEX(A2;E2:E100;F2:F100)Cette formule cherche la valeur de A2 dans E2:E100 et renvoie la donnée correspondante dans F2:F100.
Un exemple simple avec une liste de produits
Supposons que votre tableau soit organisé ainsi :
- Colonne A : référence du produit
- Colonne B : nom du produit
- Colonne C : catégorie
- Colonne D : prix
Vous saisissez une référence en F2 et vous souhaitez afficher le nom du produit en G2.
=RECHERCHEX(F2;A2:A100;B2:B100)Pour afficher la catégorie :
=RECHERCHEX(F2;A2:A100;C2:C100)Et pour récupérer le prix :
=RECHERCHEX(F2;A2:A100;D2:D100)La valeur recherchée et les plages utilisées doivent être cohérentes. Si vous cherchez une référence dans A2:A100, la plage renvoyée doit couvrir le même nombre de lignes, comme B2:B100 ou D2:D100.
Une plage de recherche composée de A2:A100 avec une plage de résultat allant de D2:D80 risque de provoquer une erreur ou un résultat inattendu. Excel est puissant, mais il n’aime pas beaucoup les tableaux qui ne sont pas alignés.
Afficher un message au lieu de l’erreur #N/A
Par défaut, RECHERCHEX affiche l’erreur #N/A lorsque la valeur recherchée n’existe pas. Ce comportement est logique, mais pas toujours très agréable dans un tableau destiné à être partagé.
Pour afficher un message personnalisé, utilisez le quatrième argument :
=RECHERCHEX(F2;A2:A100;D2:D100;"Produit introuvable")Si la référence n’existe pas, Excel affichera « Produit introuvable » au lieu de #N/A.
Vous pouvez également renvoyer une cellule vide :
=RECHERCHEX(F2;A2:A100;D2:D100;"")Ou afficher une information plus explicite :
=RECHERCHEX(F2;A2:A100;D2:D100;"Vérifiez la référence saisie")Cette option est particulièrement utile dans les tableaux de bord, les modèles de devis ou les fichiers utilisés par plusieurs personnes. Un message compréhensible vaut mieux qu’un code d’erreur qui donne envie de fermer Excel et de partir faire du jardinage.
Comprendre les modes de correspondance
Le cinquième argument permet de choisir la façon dont Excel compare la valeur recherchée. Sa valeur par défaut est 0, c’est-à-dire une correspondance exacte.
=RECHERCHEX(F2;A2:A100;D2:D100; "Introuvable"; 0)Les différentes options sont les suivantes :
- 0 : correspondance exacte. C’est le mode le plus courant.
- -1 : correspondance exacte ou valeur immédiatement inférieure.
- 1 : correspondance exacte ou valeur immédiatement supérieure.
- 2 : utilisation de caractères génériques comme * et ?.
Dans la plupart des recherches de références, de noms ou d’identifiants, conservez le mode 0. Il évite notamment qu’Excel vous renvoie une valeur approximative alors que vous attendiez une correspondance précise.
Faire une recherche approximative avec RECHERCHEX
La recherche approximative est pratique pour associer une valeur à une tranche. Prenons un tableau de commissions :
- 0 € : 0 %
- 1 000 € : 2 %
- 5 000 € : 5 %
- 10 000 € : 8 %
Si le chiffre d’affaires se trouve en H2, vous pouvez rechercher le taux correspondant avec :
=RECHERCHEX(H2;A2:A5;B2:B5;"";-1)Avec le mode -1, Excel cherche une correspondance exacte. Si elle n’existe pas, il retient la valeur immédiatement inférieure.
Pour que cette recherche fonctionne correctement, la colonne contenant les seuils doit être triée dans l’ordre croissant. Dans le cas contraire, les résultats peuvent être incohérents.
Cette méthode peut servir pour les remises, les barèmes de salaire, les frais de livraison, les niveaux de risque ou encore les scores associés à une note.
Rechercher avec des caractères génériques
Le mode de correspondance 2 permet d’utiliser des caractères génériques :
- * remplace un nombre quelconque de caractères.
- ? remplace un seul caractère.
- ~ permet de rechercher littéralement un astérisque ou un point d’interrogation.
Supposons que vous souhaitiez trouver le premier client dont le nom commence par « Dur ». Vous pouvez utiliser :
=RECHERCHEX("Dur*";A2:A100;B2:B100;"Aucun résultat";2)Excel recherchera les valeurs commençant par « Dur », comme Durant, Duroy ou Durand.
Pour rechercher un code composé de trois caractères dont le deuxième est toujours A :
=RECHERCHEX("?A?";A2:A100;B2:B100;"Aucun résultat";2)Les caractères génériques sont utiles lorsque les données comportent des variantes ou lorsque l’utilisateur ne connaît qu’une partie du texte.
Rechercher le dernier résultat trouvé
Par défaut, RECHERCHEX renvoie la première correspondance. Mais que faire lorsqu’une même référence apparaît plusieurs fois et que vous souhaitez récupérer la dernière ligne, par exemple le dernier prix enregistré ou le dernier statut d’une commande ?
Utilisez le sixième argument avec la valeur -1 :
=RECHERCHEX(F2;A2:A100;D2:D100;"Introuvable";0;-1)Excel parcourt alors la plage de recherche de bas en haut et renvoie la dernière correspondance.
Cette option est très pratique dans un historique de ventes ou de suivi client. Vous pouvez, par exemple, récupérer le dernier commentaire associé à un dossier sans devoir trier manuellement le tableau.
Retourner plusieurs colonnes avec une seule formule
RECHERCHEX peut renvoyer plusieurs colonnes à la fois. Si vous recherchez une référence en F2 et souhaitez récupérer simultanément le nom, la catégorie et le prix, utilisez :
=RECHERCHEX(F2;A2:A100;B2:D100;"Introuvable")La formule va « déborder » automatiquement dans les cellules situées à droite. Cette fonctionnalité repose sur les tableaux dynamiques disponibles dans les versions récentes d’Excel.
Veillez simplement à laisser les cellules de destination vides. Si une cellule bloque le déversement, Excel affichera l’erreur #PROPAGATION! ou son équivalent selon votre version.
Utiliser RECHERCHEX avec une recherche sur plusieurs critères
RECHERCHEX ne propose pas directement plusieurs critères dans un argument unique. Il est toutefois possible de combiner les critères grâce à une multiplication logique.
Supposons que :
- la colonne A contient le nom du client ;
- la colonne B contient l’année ;
- la colonne C contient le montant.
Pour rechercher le montant correspondant au client indiqué en F2 et à l’année inscrite en G2 :
=RECHERCHEX(1;(A2:A100=F2)*(B2:B100=G2);C2:C100;"Introuvable")Chaque condition renvoie VRAI ou FAUX. Excel les convertit ensuite en 1 ou 0. La multiplication permet d’identifier la ligne où les deux critères sont vrais en même temps.
Cette technique est très utile pour les rapports mensuels, les suivis de commandes ou les tableaux contenant plusieurs enregistrements pour une même personne.
Les erreurs fréquentes à éviter
Une formule RECHERCHEX peut sembler parfaite et pourtant ne rien renvoyer. Voici les causes les plus fréquentes :
- Des espaces invisibles : « Client A » et « Client A » avec un espace final sont deux textes différents pour Excel.
- Des nombres stockés comme du texte : la valeur 123 peut être différente de « 123 ».
- Des plages de tailles différentes : la plage recherchée et la plage renvoyée doivent être alignées.
- Des doublons : RECHERCHEX renvoie la première correspondance, sauf si vous demandez la dernière.
- Une mauvaise recherche approximative : les seuils doivent être triés correctement.
- Une cellule bloquante : une formule qui renvoie plusieurs colonnes doit disposer de suffisamment d’espace.
Pour nettoyer un texte, vous pouvez utiliser la fonction SUPPRESPACE :
=RECHERCHEX(SUPPRESPACE(F2);A2:A100;D2:D100;"Introuvable")Si le problème vient de caractères non imprimables, la fonction EPURAGE peut également être utile :
=RECHERCHEX(EPURAGE(SUPPRESPACE(F2));A2:A100;D2:D100;"Introuvable")Que faire si RECHERCHEX n’est pas disponible ?
RECHERCHEX est proposée dans les versions récentes d’Excel, notamment Microsoft 365 et plusieurs éditions modernes. Sur une ancienne version, la fonction peut être inconnue.
Vous pouvez alors utiliser RECHERCHEV pour une recherche verticale simple :
=RECHERCHEV(F2;A2:D100;4;FAUX)Le dernier argument FAUX impose une correspondance exacte. Attention toutefois : RECHERCHEV ne peut rechercher que dans la première colonne de la plage et renvoyer une colonne située à droite.
Autre solution, plus ancienne mais très flexible :
=INDEX(D2:D100;EQUIV(F2;A2:A100;0))Le duo INDEX + EQUIV reste intéressant dans les fichiers destinés à fonctionner sur différentes versions d’Excel. Mais si RECHERCHEX est disponible, elle sera généralement plus simple à lire et à transmettre à un collègue.
Quelques bonnes pratiques pour des formules plus fiables
- Utilisez des références structurées en transformant vos données en tableau avec Ctrl + T.
- Donnez des noms explicites à vos tableaux et plages.
- Ajoutez systématiquement un message dans l’argument si_non_trouvé pour éviter les erreurs visibles.
- Utilisez la correspondance exacte pour les références, codes et identifiants.
- Vérifiez la présence d’espaces et le format des données avant de modifier la formule.
- Évitez les plages énormes comme A:A dans des fichiers très volumineux si les performances deviennent lentes.
Avec un tableau nommé Produits, une formule peut devenir particulièrement lisible :
=RECHERCHEX(F2;Produits[Référence];Produits[Prix];"Produit introuvable")Cette version explique presque d’elle-même ce que fait la formule. Six mois plus tard, vous serez heureux de ne pas avoir à déchiffrer une chaîne de références du type A2:A4789.
RECHERCHEX est donc la fonction à privilégier pour la plupart des recherches dans Excel. Commencez avec ses trois premiers arguments, ajoutez un message personnalisé, puis explorez progressivement les recherches approximatives, les caractères génériques et les critères multiples. Une fois adoptée, elle rend les tableaux plus propres, les formules plus robustes et les recherches beaucoup moins laborieuses.