RECHERCHEX Excel : syntaxe et exemples pour remplacer RECHERCHEV

La fonction RECHERCHEX Excel est utilisée pour rechercher une valeur dans une plage de cellules et renvoyer une valeur associée d’une colonne correspondante. Cette formule a pour objectif de remplacer RECHERCHEV.

Si vous cherchez comment utiliser RECHERCHEX sur Excel, cette page vous montre la syntaxe de la formule, un exemple simple, les paramètres facultatifs et les cas pratiques les plus fréquents.

La syntaxe de la formule est la suivante :

=RECHERCHEX(Valeur_cherchée ; Tableau_recherche ; Tableau_renvoyé ; [Si_non_trouvé] ; [Mode_correspondance] ; [Mode_recherche])

Les 3 arguments entre crochets sont des paramètres facultatifs. Seules les 3 premières expressions sont obligatoires.

• Valeur_cherchée : la valeur à rechercher (référence à une cellule ou une valeur).
• Tableau_recherche : la plage de cellules où la recherche doit être effectuée. La plage doit être une colonne unique ou une partie d’une colonne.
• Tableau_renvoyé : la plage de cellules à renvoyer. Il est possible de sélectionner une colonne ou plusieurs colonnes.
• Si_non_trouvé : la valeur à afficher si la valeur recherchée n’a pas été trouvée dans la zone Tableau_recherche.
• Mode_correspondance (facultatif) : ce code précise comment comparer Valeur_cherchée et Tableau_recherche selon la correspondance suivante :
  • 0 : correspondance exacte et si aucune correspondance n’est trouvée, une erreur #N/A sera renvoyée ou la valeur Si_non_trouvé.
  • -1 : correspondance exacte et si aucune correspondance, la valeur inférieure suivante.
  • 1 : correspondance exacte et si aucune correspondance, la valeur supérieure suivante.
  • 2 : correspondance générique avec jokers. Par exemple, en utilisant L?na dans une recherche de prénom, la recherche peut porter sur Lena et sur Lana. Voir l’explication complémentaire plus bas dans l’article.
• Mode_recherche (facultatif) : permet de spécifier le mode de recherche à utiliser :
  • 1 : effectue une recherche à partir du premier élément. C’est la valeur par défaut.
    Si la plage de recherche est verticale : du haut vers le bas.
    Si la plage de recherche est horizontale : de gauche à droite.
  • -1 : effectue une recherche inversée en commençant par le dernier élément.
  • 2 : effectue une recherche binaire qui repose sur le tri de tableau_recherche par ordre croissant. Si le tableau n’est pas trié, des résultats non valides peuvent être renvoyés.
  • -2 : effectue une recherche binaire qui repose sur le tri de tableau_recherche par ordre décroissant. Si le tableau n’est pas trié, des résultats non valides peuvent être renvoyés.

Les différents arguments doivent être séparés par des points-virgules.

Comment fonctionne RECHERCHEX sur Excel : construction d’une formule

Ce tutoriel RECHERCHEX explique pas à pas comment construire la formule, comprendre ses paramètres et obtenir le bon résultat selon le type de recherche souhaité.

Pour présenter le fonctionnement de la formule RECHERCHEX, dans ce tutoriel, un fichier avec la liste des communes françaises, le code postal et le code INSEE servira d’exemple.

Recherche simple

Je dispose du nom de la commune et je souhaite renvoyer le code postal de la commune. Pour cela, je construis la formule suivante :

Les 3 arguments (Si_non_trouvé, Mode_correspondance et Mode_recherche) ne sont pas obligatoires. À ce stade, ils ne sont pas renseignés.

L’objectif est d’effectuer une recherche sur le code postal de la commune AIME et la valeur trouvée est 73210.

Utilisation de Si_non_trouvé

Si la recherche porte sur un nom de commune qui n’existe pas dans la liste, pour l’instant le tableur renvoie une erreur #N/A.

Pour rendre la formule plus propre, il est possible d’utiliser l’argument Si_non_trouvé qui permet de définir l’information qui sera affichée si la valeur recherchée n’est pas trouvée dans la zone de recherche (Tableau_recherche).

Pour renvoyer :

• une cellule vide, il faut spécifier 2 guillemets l’un après l’autre (sans rien mettre à l’intérieur) : "" soit =RECHERCHEX(J14;C:C;A:A;"")
• un texte : "ville inconnue" soit =RECHERCHEX(J14;C:C;A:A;"ville inconnue")
• un tiret : "-" soit =RECHERCHEX(J14;C:C;A:A;"-")
• la valeur d’une autre cellule : =RECHERCHEX(J14;C:C;A:A;K17)

Utilisation de Mode_correspondance

Ce paramètre permet de définir comment la comparaison s’effectue entre la valeur recherchée et les valeurs qui sont dans la plage de données définie dans Tableau_recherche :

• 0 : correspondance exacte et si aucune correspondance n’est trouvée, une erreur #N/A sera renvoyée ou la valeur Si_non_trouvé.
• -1 : correspondance exacte et si aucune correspondance, la valeur inférieure suivante.
• 1 : correspondance exacte et si aucune correspondance, la valeur supérieure suivante.
• 2 : correspondance générique avec jokers. Par exemple en utilisant L?na dans une recherche de prénom, la recherche peut porter sur Lena et sur Lana.

Il est préconisé dans la plupart des cas d’utiliser la valeur 0. En effet, dans la grande majorité des usages de la fonction RECHERCHEX, il est nécessaire de trouver la valeur exacte dans la zone de recherche.

Dans le cas où des abaques sont utilisées, il faut utiliser les valeurs -1 et 1.

Utilisation d’abaques

À partir de l’abaque identifiée comme base de données dans l’exemple suivant, les recherches portent sur la valeur 0,12 à rechercher en colonne A et on souhaite obtenir la valeur correspondante dans la colonne B. Cette valeur n’est pas présente dans la colonne A de la base de données. En fonction du paramètre Mode_correspondance, les résultats renvoyés sont les suivants :

• 0 : la valeur n’est pas trouvée, le résultat est #N/A
• -1 : la valeur inférieure suivante à 0,12 présente dans la colonne A est 0,1 : le résultat est 0,0398
• 1 : la valeur supérieure suivante à 0,12 présente dans la colonne A est 0,2 : le résultat est 0,0793

Cette fonctionnalité peut aussi être utilisée avec une recherche sur du texte. Cela signifie que la recherche renverra le texte le plus proche alphabétiquement parlant. Cette utilisation est déconseillée, sauf cas très particulier. Il vaut mieux savoir que la valeur n’a pas été trouvée que d’avoir un résultat qui ne correspond pas à la recherche.

Mode_correspondance = 2 : recherche générique

Dans le cas où le paramètre Mode_correspondance est à 2, il est possible de faire des recherches en utilisant des jokers dans la recherche :

• ? remplace un caractère
• * remplace un caractère ou une chaîne de caractères
• ~? ou ~* précise que le caractère ? ou * est bien recherché dans la chaîne de caractères

Besoin d’un coup de main pour structurer vos données ? Nous vous accompagnons via un développement Excel sur mesure pour transformer vos classeurs en véritables outils de pilotage.

Exemples pour la fonction RECHERCHEX

À partir du tableau suivant, la source de données est une liste de nom, prénom et téléphone. Les différents exemples permettent d’illustrer les différentes combinaisons de code pour mode_correspondance et mode_recherche. Le résultat correspond à la colonne contenant le numéro de téléphone.

Astuces

La fonction VALEUR.EN.TEXTE d’Excel est particulièrement utile lorsqu’on utilise la fonction RECHERCHEX pour effectuer des recherches entre des données qui n’ont pas le même format, notamment lorsque la valeur recherchée est un nombre et que les valeurs dans la base de données sont des chaînes de texte, ou inversement.

Exemple d’utilisation de VALEUR.EN.TEXTE avec RECHERCHEX

Imaginons un scénario où vous avez une base de données avec des codes produits enregistrés sous forme de texte, par exemple "001" et "002", mais la valeur que vous cherchez est un nombre comme 1 ou 2. Si vous utilisez RECHERCHEX pour rechercher 1, Excel ne trouvera pas de correspondance dans la colonne contenant "001", car un nombre et un texte sont traités différemment.

Intérêt de VALEUR.EN.TEXTE

Harmonisation des formats : avec VALEUR.EN.TEXTE, vous pouvez convertir le nombre recherché en texte avant d’effectuer la recherche. Par exemple, si vous souhaitez rechercher le code produit 1 dans une liste de codes au format texte "001", "002", vous pouvez le transformer en "001" :

=RECHERCHEX(VALEUR.EN.TEXTE(A2;"000");BaseDeDonnées[CodesProduit];BaseDeDonnées[NomProduit])

Ici, VALEUR.EN.TEXTE(A2;"000") transforme le nombre en une chaîne de texte à trois chiffres, correspondant au format de la base de données.

Flexibilité dans le formatage : VALEUR.EN.TEXTE permet d’ajouter un format spécifique, comme "000", afin d’adapter la valeur recherchée au format des données dans la base de données.

Éviter les erreurs de correspondance : sans VALEUR.EN.TEXTE, RECHERCHEX ne reconnaîtrait pas un nombre et une chaîne de texte comme identiques, et la recherche échouerait. Avec VALEUR.EN.TEXTE, les deux formats sont alignés, ce qui garantit que la fonction RECHERCHEX Excel trouve la correspondance correcte.

Cas d’usage

Voici les situations où VALEUR.EN.TEXTE avec RECHERCHEX est particulièrement utile :

• codes avec zéros initiaux, par exemple des codes postaux ou des codes produit ;
• formats numériques variés, lorsque certains nombres ont des décimales et d’autres non ;
• compatibilité avec des bases de données externes où les nombres sont stockés sous forme de texte.

2 Feuilles

La plupart du temps les données sont sur une feuille et on souhaite récupérer le résultat de cette formule dans une autre feuille, il y a des astuces pour utiliser RECHERCHEX entre 2 feuilles.

Affichage de plusieurs colonnes comme résultats

Selon le paramétrage, Recherchex donne plusieurs colonnes comme résultats. Dans le 3ème paramètre de la formule, en précisant les différentes plages de cellules à renvoyer, les colonnes spécifiées seront affichées. Il faut que les colonnes soient contiguës.

=RECHERCHEX("AIME";C:C;A:H;"-";0)

Quand utiliser la fonction RECHERCHEX Excel ?

La fonction RECHERCHEX d’Excel peut être utilisée pour rechercher une valeur sélectionnée dans une plage de cellules et renvoyer la valeur associée à une position sélectionnée dans la plage.

En maîtrisant cet outil et la formule SI, vous pouvez gérer un nombre très important de problématiques. Ce sont deux des fonctions les plus importantes du tableur.

RECHERCHEX vs RECHERCHEV : quelles différences ?

La fonction RECHERCHEX d’Excel présente les avantages suivants :

Position de la clé de recherche : avec la fonction RECHERCHEV, la clé de recherche doit obligatoirement être présente dans la colonne de gauche. Avec la fonction RECHERCHEX, la colonne de sélection peut être à n’importe quel endroit de la base de données.

Flexibilité : elle peut effectuer des recherches plus complexes, définir la direction de la recherche et renvoyer une valeur par défaut si la valeur n’est pas trouvée.

Meilleure précision : elle peut gérer des données plus grandes et plus complexes en permettant une recherche bidirectionnelle dans la plage.

Productivité accrue : elle peut automatiser les tâches de recherche fastidieuses, ce qui améliore la productivité en économisant du temps et des ressources.

Prise en charge des données en double : elle peut éviter les erreurs en gérant les données en double, en permettant de déterminer la position de la première occurrence d’une valeur.

RECHERCHEX remplace progressivement RECHERCHEV dans les fichiers récents, car la formule est plus souple et plus lisible. Le tableau suivant RECHERCHEX vs RECHERCHEV synthétise les différences.

Inconvénients de la formule RECHERCHE X Excel

Cette fonction n’est pas présente sur toutes les versions d’Excel et il n’y a pas de rétrocompatibilité. Si vous êtes amené à partager un classeur contenant RECHERCHEX avec des partenaires disposant d’une version plus ancienne, cette formule ne fonctionnera pas chez eux.

Attention RECHERCHEX ne s’écrit pas RECHERCHE X (certains ont essayé …).

Avec quelles versions d’Excel la formule RECHERCHEX fonctionne

La fonction RECHERCHEX est disponible dans les versions suivantes :

• Microsoft 365 : disponible dans toutes les éditions de Microsoft 365, y compris Excel pour le Web.
• Excel 2021 et versions ultérieures : RECHERCHEX est intégrée aux versions récentes d’Excel.
• Excel pour Mac : disponible dans Microsoft 365 et les versions récentes compatibles.

Si vous utilisez Excel 2016 ou une version plus ancienne, vous n’aurez pas accès à RECHERCHEX. Dans ce cas, vous pouvez utiliser RECHERCHEV pour des recherches simples, ou mettre à jour votre version d’Excel pour bénéficier de fonctionnalités plus avancées.

Dans des cas plus avancés, il est possible d’adapter la formule pour gérer plusieurs citères dans RECHERCHEX.

Version anglaise : XLOOKUP

Pour l’utilisation en version anglaise, la formule est : XLOOKUP.

Liste des erreurs possibles

Voici quelques erreurs susceptibles de survenir lors de l’utilisation de la fonction RECHERCHEX d’Excel :

• #N/A : cette erreur indique que la valeur recherchée n’a pas été trouvée dans la plage de recherche indiquée.
• #VALEUR! : cette erreur peut survenir si les arguments de la fonction RECHERCHEX sont de types différents, par exemple si la plage de recherche est un texte et la valeur recherchée est un nombre.
• si les plages définies n’ont pas la même dimension : Tableau_recherche et Tableau_renvoyé doivent avoir le même nombre de cellules ;
• si Tableau_recherche et Tableau_renvoyé n’ont pas la même orientation : une plage horizontale et l’autre verticale.

Pourquoi je n’ai pas la fonction RECHERCHEX ?

Si vous utilisez une version d’Excel antérieure à Excel 2021 ou que votre environnement n’intègre pas encore les fonctions récentes de Microsoft 365, vous n’aurez pas accès à la formule RECHERCHEX.

Si cette fonction vous semble indispensable, il faut mettre à jour votre version d’Excel.

FAQ RECHERCHEX

Comment utiliser RECHERCHEX sur Excel ?

Pour utiliser RECHERCHEX, il faut indiquer la valeur recherchée, la plage dans laquelle effectuer la recherche et la plage contenant le résultat. Les arguments facultatifs permettent ensuite de personnaliser le comportement de la formule.

Quelle est la syntaxe de la fonction RECHERCHEX ?

La syntaxe est la suivante : =RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode_recherche]).

Pourquoi RECHERCHEX renvoie #N/A ?

Le plus souvent, cela signifie que la valeur recherchée n’est pas présente dans la plage définie, ou que la correspondance exacte ne trouve rien.

Quelle est la différence entre RECHERCHEX et RECHERCHEV ?

RECHERCHEX est plus souple, car elle permet de chercher dans les deux sens, de gérer les erreurs directement et de rendre les formules plus lisibles.