Maîtriser la fonction RECHERCHEX dans Excel
Apprenez à utiliser RECHERCHEX dans Excel. Guide complet : syntaxe, recherche à gauche, retour matriciel et gestion d'erreurs pour booster vos analyses.
Forts de 12 ans d'expérience en formation Excel, nous avons vu comment la fonction RECHERCHEX peut transformer votre manière d'analyser des données. Beaucoup d'utilisateurs d'Excel ne tirent pas parti de toutes les fonctionnalités avancées de l'outil. La fonction RECHERCHEX, introduite dans Excel 365/2021, permet de rechercher des valeurs dans un tableau de manière beaucoup plus efficace que les méthodes précédentes et résout de nombreux problèmes fréquents d'analyse de données.
Maîtriser RECHERCHEX est essentiel car elle dépasse les limites des anciennes fonctions RECHERCHEV et RECHERCHEH. RECHERCHEX permet des recherches dans n'importe quelle direction (horizontalement et verticalement) et offre des options de gestion d'erreurs explicites. À travers des exemples concrets, vous apprendrez à l'utiliser pour comparer des listes de produits, analyser des ventes et créer des rapports dynamiques pour gagner du temps au quotidien.
Ce tutoriel vous guide étape par étape pour effectuer des recherches avancées dans vos feuilles de calcul : création de tableaux dynamiques, formules composées et bonnes pratiques pour automatiser des rapports. À la fin, vous pourrez résoudre des cas de recherche courants, améliorer la précision de vos analyses et optimiser votre productivité dans Excel.
Introduction à RECHERCHEX
Qu'est-ce que RECHERCHEX ?
La fonction RECHERCHEX d'Excel (XLOOKUP en anglais) est une amélioration importante par rapport aux fonctions de recherche traditionnelles. Elle recherche une valeur dans une plage (lookup_array) et renvoie la valeur correspondante d'une autre plage (return_array) — tout en autorisant la recherche dans n'importe quelle direction. C'est une solution polyvalente pour des tableaux qui ne respectent pas un ordre fixe de colonnes.
Dans des scénarios comme un tableau de suivi des ventes, RECHERCHEX permet de retrouver rapidement les détails d'un produit en utilisant son identifiant, son nom ou d'autres clés, sans contraintes liées à l'emplacement des colonnes. Cette flexibilité en fait un outil utile pour les utilisateurs débutants comme avancés.
- Recherche de valeurs dans des tableaux
- Support des recherches horizontales et verticales
- Correspondance exacte par défaut et options de correspondance avancées
- Intégration facile dans des tableaux structurés
Voici la syntaxe de base de la fonction :
RECHERCHEX(valeur_cherchée, tableau_recherche, tableau_resultat, [si_non_trouvé], [mode_correspondance], [mode_recherche])La fonction renvoie la valeur correspondante de tableau_resultat pour la valeur_cherchée trouvée dans tableau_recherche. Les deux derniers arguments sont optionnels : le 4e (si_non_trouvé) permet de définir un message ou une valeur par défaut, le 5e (mode_correspondance) et le 6e (mode_recherche) contrôlent le type de correspondance et la direction/recherche.
Syntaxe et paramètres de RECHERCHEX
Détails de la syntaxe
La syntaxe de RECHERCHEX est simple, mais chaque paramètre a son importance pour un usage fiable. Présentation rapide :
- valeur_cherchée : la valeur à rechercher
- tableau_recherche : la plage où effectuer la recherche (lookup_array)
- tableau_resultat : la plage de résultats à renvoyer (return_array)
- si_non_trouvé (optionnel) : valeur renvoyée si aucun résultat (ex. "Non trouvé")
- mode_correspondance (optionnel, défaut = 0) : 0 = correspondance exacte ; -1 = correspondance exacte ou suivant plus petit ; 1 = correspondance exacte ou suivant plus grand ; 2 = correspondance avec jokers
- mode_recherche (optionnel) : 1 = première→dernière (défaut), -1 = dernière→première, 2 = recherche binaire ascendante (nécessite tri), -2 = recherche binaire descendante
Important : le 4e argument est bien le si_non_trouvé. Beaucoup confondent et mettent FAUX comme dans RECHERCHEV — évitez cela. Pour forcer la correspondance exacte de façon explicite, utilisez 0 en 5e position.
RECHERCHEX("Produit A", A2:A100, B2:B100, "Non trouvé", 0)La formule retourne la valeur associée à « Produit A » dans la plage B2:B100 si elle existe, sinon « Non trouvé ».
Différences entre RECHERCHEX et autres fonctions
Comparaison avec RECHERCHEV et RECHERCHEH
RECHERCHEX se distingue de RECHERCHEV et RECHERCHEH par sa flexibilité et ses arguments explicites :
- RECHERCHEX permet de rechercher dans n'importe quelle colonne et de renvoyer une colonne située à gauche ou à droite (recherche bidirectionnelle).
- RECHERCHEX accepte un message si_non_trouvé comme 4e argument (mieux que gérer #N/A manuellement).
- RECHERCHEX expose des options de correspondance et de mode de recherche (5e et 6e arguments).
Exemple de recherche d'un client spécifique :
RECHERCHEX("Client X", C2:C100, D2:D100, "Non trouvé", 0)Diagramme de fonctionnement
Le schéma ci-dessous illustre la différence entre la recherche unidirectionnelle (RECHERCHEV) et la recherche omnidirectionnelle (RECHERCHEX).
Ce diagramme aide à visualiser pourquoi RECHERCHEX est recommandé lorsque l'ordre des colonnes peut changer ou lorsque vous devez rechercher vers la gauche.
Exemples pratiques d'utilisation
Applications concrètes de RECHERCHEX
Une application fréquente de RECHERCHEX est l'analyse des ventes : retrouver le prix, la disponibilité ou la catégorie d'un produit à partir d'un identifiant. Lorsque les catalogues contiennent des milliers d'articles, RECHERCHEX permet d'automatiser ces recherches sans reconfigurer la structure des colonnes.
Dans un projet où nous avons aidé une start-up à développer leur tableau de bord, nous avons remplacé plusieurs formules imbriquées par RECHERCHEX et constaté une amélioration notable des temps de calcul et de la simplicité de maintenance.
- Recherche rapide de produits dans des catalogues volumineux
- Analyse des performances de vente par produit
- Récupération d'informations utilisateurs en temps réel
- Facilitation de la prise de décisions basées sur des données
Exemple : message par défaut si non trouvé
RECHERCHEX(A2, B2:B100, C2:C100, "Non trouvé", 0)La formule cherche la valeur d'A2 dans B2:B100 et renvoie la valeur correspondante de C2:C100 ; si la valeur n'existe pas, elle affiche "Non trouvé".
Exemple : recherche vers la gauche (avantage par rapport à RECHERCHEV)
Cas : l'ID est en colonne B et le nom est en colonne A. Avec RECHERCHEX vous pouvez chercher l'ID (colonne B) et renvoyer le nom (colonne A) sans réorganiser les colonnes :
RECHERCHEX(B2, B2:B100, A2:A100, "Non trouvé", 0)Cette formule recherche la valeur de B2 dans B2:B100 et renvoie la valeur correspondante dans A2:A100 (recherche "à gauche").
Exemple : retour matriciel (plusieurs colonnes en une seule formule)
RECHERCHEX peut renvoyer une plage entière (spill) comme return_array. Par exemple, pour retourner à la fois le Prix et le Stock (colonnes B et C) :
RECHERCHEX("Produit A", A2:A100, B2:C100, "Non trouvé", 0)La formule renverra les deux colonnes adjacentes (le prix et le stock) et les résultats se propageront automatiquement dans les colonnes adjacentes (spill) si votre version d'Excel supporte les formules dynamiques.
| Cas d'utilisation | Description | Exemple |
|---|---|---|
| Suivi des ventes | Récupérer les prix des produits | Recherche de prix d'articles |
| Gestion des utilisateurs | Accéder aux données des utilisateurs | Récupération d'informations d'enregistrement |
| Analyse des stocks | Vérifier la disponibilité des produits | Consultation des niveaux de stock |
Erreurs courantes et solutions
Problèmes fréquents avec RECHERCHEX
Un problème fréquent est de ne pas vérifier la présence de la valeur dans la plage recherchée : la formule peut renvoyer « Non trouvé » ou une erreur si les données ne sont pas à jour. Structurez toujours vos données et mettez en place des contrôles simples (plages nommées, validation) avant d'exécuter les recherches.
Autre source d'erreur : l'incompatibilité de types. Rechercher une valeur numérique dans une colonne stockée en texte (ou inversement) aboutira souvent à un non‑résultat. Convertissez explicitement les formats ou utilisez des fonctions de normalisation (NUM, TEXTE) selon le cas.
- Omission de la vérification de la présence des données
- Incompatibilité des types de données
- Utilisation incorrecte des plages de recherche
- Mauvaise gestion des erreurs renvoyées
Gestion d'erreur intégrée
RECHERCHEX(A2, B2:B100, C2:C100, "Non trouvé", 0)Cette formule force la correspondance exacte (5e argument = 0) et retourne "Non trouvé" si la recherche échoue, ce qui améliore la clarté des résultats.
Exemple : message utilisateur clair avec vérification
SI(ESTERREUR(RECHERCHEX(A2, PlageProduits, PlagePrix, "Non trouvé", 0)), "Valeur non trouvée", RECHERCHEX(A2, PlageProduits, PlagePrix, "Non trouvé", 0))La formule affiche "Valeur non trouvée" si RECHERCHEX déclenche une erreur autre que l'absence de valeur, sinon elle retourne le résultat attendu.
| Erreur | Description | Solution |
|---|---|---|
| Valeur non trouvée | La valeur recherchée n'existe pas dans la plage | Vérifier et mettre à jour les données, utiliser un message par défaut (4e argument) |
| Type de données incompatible | Mauvais format de données | Convertir les types de données avant la recherche |
| Plage de recherche incorrecte | Plage mal définie ou de tailles différentes | S'assurer que les plages lookup_array et return_array ont la même hauteur/largeur selon le cas |
Conseils avancés pour utilisateurs expérimentés
Optimisation des recherches complexes
Pour optimiser RECHERCHEX sur de gros jeux de données, limitez les plages à ce qui est strictement nécessaire (plages nommées), évitez les références volatiles imbriquées et préférez les tableaux structurés. Ces pratiques réduisent les recalculs et améliorent la maintenabilité des formules.
L'utilisation de critères multiples (combiner plusieurs colonnes dans une clé composite ou utiliser des colonnes auxiliaires) permet de réaliser des recherches plus fines. Dans des rapports, la combinaison de RECHERCHEX avec FILTRE ou LET (si disponible) permet d'obtenir des résultats dynamiques et plus lisibles pour les équipes métier.
- Utilisez des plages nommées pour éviter les erreurs de type et faciliter la lecture.
- Assurez-vous que les valeurs de recherche sont au bon format.
- Exploitez les tables auxiliaires pour des recherches plus rapides.
- Utilisez des critères multiples pour des résultats plus précis.
Performance et volatilité
RECHERCHEX est conçue pour être efficace avec les plages et les tableaux structurés. Comparée à certaines constructions INDEX/EQUIV imbriquées mal conçues, RECHERCHEX est généralement moins sujette aux recalculs excessifs (non-volatile). En revanche, toute formule référant de larges plages dynamiques peut affecter les performances — limitez les plages et préférez des plages nommées ou des tables Excel.
Gestion des erreurs et débogage
Implémentez des messages d'erreur explicites pour les utilisateurs finaux afin d'éviter les ambiguïtés (#N/A). Validez les données en amont (contrôles de format, listes de validation) et conservez des notes de version sur les feuilles principales pour tracer les modifications qui peuvent impacter les recherches.
- Implémentez des messages d'erreur personnalisés.
- Validez les données d'entrée avant la recherche.
- Utilisez des logs ou une feuille dédiée pour suivre les changements structurants.
- Faites des tests unitaires pour les formules critiques (ex. petites feuilles de test).
Points Clés à Retenir
- RECHERCHEX permet de rechercher des valeurs en spécifiant indépendamment la plage de recherche et la plage de résultat.
- La recherche peut être effectuée dans plusieurs directions (horizontalement et verticalement), y compris vers la gauche.
- Le 4e argument est si_non_trouvé (valeur par défaut) ; le 5e est mode_correspondance et le 6e mode_recherche.
- Privilégiez les plages nommées et les tableaux structurés pour améliorer performance et maintenabilité.
- RECHERCHEX peut renvoyer une plage (matricielle) pour récupérer plusieurs colonnes en une seule formule.
Questions Fréquentes
- Comment RECHERCHEX se compare-t-elle à RECHERCHEV ?
- RECHERCHEX est souvent plus avantageuse car elle permet de rechercher dans n'importe quelle direction, tandis que RECHERCHEV est limitée à la recherche vers la droite. RECHERCHEX renvoie également des résultats depuis une colonne située à gauche de la colonne de recherche, ce qui n'est pas possible avec RECHERCHEV.
- Puis-je utiliser RECHERCHEX pour rechercher des valeurs dans plusieurs feuilles ?
- Oui, mais chaque feuille doit être explicitement référencée dans la formule (par ex. RECHERCHEX(valeur, Feuille1!A2:A100, Feuille2!B2:B100, "Non trouvé", 0)). Veillez à ce que les plages aient la même taille pour éviter les erreurs.
- Quels sont les types d'erreurs courants avec RECHERCHEX ?
- Erreurs communes : #N/A si la valeur n'est pas trouvée (si vous n'utilisez pas le 4e argument) et #REF! si les plages sont mal définies. Entourez RECHERCHEX de SIERREUR ou utilisez le 4e argument pour afficher un message utilisateur lisible.
- Quelle version d'Excel supporte RECHERCHEX ?
- RECHERCHEX est disponible dans Excel 365 et Excel 2021. Les versions antérieures ne disposent pas de cette fonction.
- Comment optimiser les performances de RECHERCHEX dans de grands ensembles de données ?
- Utilisez des plages nommées, limitez la taille des plages, évitez les formules volatiles et préférez des tableaux structurés. Ces pratiques réduisent les temps de recalcul et améliorent la lisibilité.
- Le 4e argument de RECHERCHEX est-il le mode de correspondance ?
- Non. Le 4e argument est si_non_trouvé (valeur à retourner si aucun résultat). Le mode_correspondance est le 5e argument (0 = exact par défaut). Évitez d'utiliser
FAUXcomme dans RECHERCHEV ; utilisez plutôt0pour la correspondance exacte si nécessaire. - RECHERCHEX peut-elle renvoyer plusieurs colonnes à la fois ?
- Oui. Si vous fournissez une plage multi-colonnes comme tableau_resultat (par ex. B2:C100), RECHERCHEX peut renvoyer ces colonnes sous forme de tableau (spill) dans les versions d'Excel qui supportent les formules dynamiques. Exemple : RECHERCHEX("Produit A", A2:A100, B2:C100, "Non trouvé", 0).
Conclusion
Maîtriser RECHERCHEX modernise la façon dont vous traitez les recherches dans Excel. En remplaçant des constructions anciennes et fragiles par des formules claires et robustes, vous simplifierez la maintenance des feuilles et réduirez les erreurs. Intégrez RECHERCHEX à vos tableaux structurés, utilisez des pratiques de validation et d'affichage d'erreur, et exploitez le retour matriciel lorsque vous devez récupérer plusieurs colonnes en une seule formule.