RECHERCHEX Excel, comment utiliser cette fonction au travers d’exemples

La fonction RECHERCHEX dans Microsoft 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 et l’association INDEX/EQUIV.

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 crochet sont des paramètres facultatifs. Seuls les 3 premières expressions sont obligatoires.

– Valeur_cherchée : La valeur à rechercher (référence à une celulle 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_cherché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 (utilisation possible de joker dans la recherche, par exemple en utilisant L?na dans une recherche de prénom, la recherche peut porter sur Lena et sur Lana) voir explication complémentaire sur la recherche générique explication complémentaire sur la recherche générique.

– Mode_recherche (facultatif) permet de spécifier le mode de recherche à utiliser :

• 1 : Effectuez une recherche à partir du premier élément. Il s’agit de la valeur par défaut.
  Si la plage de recherche est vertical : du haut vers les bas
  Si la plage de recherche est horizontal : de droite à gauche
• -1 : Effectuez une recherche inversée en commençant par le dernier élément.
  L’inverse de la solution précédente
• 2 : Effectuez une recherche binaire qui repose sur le tri de tableau_recherche par ordre croissant. S’il n’est pas trié, les résultats non valides seront renvoyés.
• -2 : Effectuez une recherche binaire qui repose sur le tri de tableau_recherche par ordre décroissant. S’il n’est pas trié, les résultats non valides seront renvoyés.

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

Pour présenter le fonctionnement de la formule RECHERCHEX, dans ce tutoriel, un fichier avec la liste des communes française, le code postal, le code INSEE servira d’exemple. Cette feuille de calcul peut être télécharger pour étudier les différentes manières d’utiliser RECHERCHEX : fichier Excel avec des exemples de formules RECHERCHEX

Je dispose du nom de la commune et je souhaite renvoyer le code postal de la commune, pour cela je construit la formule suivante :

Les 3 arguments (Si_non_trouvé , Mode_correspondance et Mode_recherche) ne sont pas obligatoires. A 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.

Si la recherche porte sur un nom de commune qui n’exite 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 soit =RECHERCHEX(J14;C:C;A:A;K17)

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 (utilisation possible de joker dans la recherche, par exemple en utilisant L?na dans une recherche de prénom, la recherche peut porter sur Lena et sur Lana) voir explication de la recherche générique.

Il est préconisé dans la plupart du temps d’utiliser la valeur 0. En effet, dans 95% des cas d’utilisation 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

A partir de l’abaque (identifiée : 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 (0,12) n’est pas présente dans la colonne A de la base de données. En fonction du paramètre Mode_correspondance, les résultat renvoyés sont les suivantes :

• 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 (ligne 4) : le résultat est 0,0398
• 1 : la valeur supérieure suivante à 0,12 présente dans la colonne A est 0,2 (ligne 5) : 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 (il s’agit d’une correspondance approximative). Cette utilisation est déconseillée (sauf cas tous à fait 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 (cf. recherche ligne 10 du tableau suivant)

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 carcatère ? ou * est bien recherché dans la chaîne de caractères

Si vous souhaitez approfondir cette fonction, consultez nos formations Excel disponibles dans votre région.

A 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.

La fonction VALEUR.EN.TEXTE d’Excel est particulièrement utile lorsqu’on utilise 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 (ex. « 001 », « 002 »), mais la valeur que vous cherchez est un nombre (ex. 1, 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 1dans une liste de codes au format texte ( « 001 »; « 002 »), vous pouvez le transformer 1en « 001 »en utilisant VALEUR.EN.TEXTE.

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

Ici, VALEUR.EN.TEXTE(A2; « 000 »)transformez 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 »pour obtenir un nombre à trois chiffres) 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 RECHERCHEX trouvera 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 : Des cas où certains nombres ont des décimales et d’autres non, et où on souhaite les formater de manière cohérente.
• Compatibilité avec des bases de données externes : Lorsque les données viennent d’une source externe où les nombres sont stockés sous forme de texte, et que vous avez besoin de les interroger avec des valeurs numériques.

Le résultat de la recherche peut renvoyer plusieurs colonnes. 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. Ils faut que les colonnes soient contigues.

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

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ématique. C’est les 2 fonctions les plus importantes du tableur.

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 permettant à la fonction RECHERCHE standard de définir la direction de la recherche et en renvoyant 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 fastidieuse, ce qui peut améliorer 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.

En résumé, la fonction RECHERCHEX d’Excel est un outil puissant pour les utilisateurs qui souhaitent effectuer des recherches plus complexes dans leurs feuilles de calcul et améliorer leur productivité.

Cette fonction n’est pas encore présente sur toutes les versions d’Excel et il n’y a pas de rétrocompatibilité. Si vous êtes amenés à partager un classeur avec Recherchex avec des partenaires qui ont une version plus ancienne que la vôtre, cette formule ne fonctionnera pas.

La fonction RECHERCHEX d’Excel est une fonction relativement nouvelle et n’est disponible que dans les versions d’Excel suivantes :

Microsoft 365 : La fonction RECHERCHEX est disponible dans toutes les éditions de Microsoft 365, y compris Excel pour le Web.

Excel 2019 : La fonction RECHERCHEX est disponible dans Excel 2019.

Excel pour Mac : La fonction RECHERCHEX est également disponible dans la version d’Excel pour Mac dans Microsoft 365.

Si vous utilisez une version plus ancienne d’Excel, vous pouvez utiliser la fonction RECHERCHEV standard pour effectuer des recherches simples dans les données. Si vous avez besoin de fonctionnalités de recherche plus avancées, il est recommandé de mettre à jour vers une version plus récente d’Excel.

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

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ée (même nombre de cellule)

Si les éléments Tableau_recherche et Tableau_renvoyée n’ont pas la même orientation : une plage horizontale et l’autre verticale

Si vous utilisez une version d’Excel antérieure à Excel 2019, vous n’aurez pas accès à la formule RECHERCHEX.

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

Si vous avez des difficultés à intégrer cette fonctionnalité dans vos projets, nous pouvons développer un fichier Excel personnalisé pour vous.