RECHERCHEX (XLOOKUP en anglais) est une fonction d’Excel permettant d’éffectuer des recherches assez avancées dans une plage de cellules ou un tableau, et de renvoyer les éléments correspondant.
RECHERCHEX rassemble au sein d’une même fonction plusieurs cas d’usage qui étaient couverts par les fonctions RECHERCHEV, RECHERCHEH, INDEX et EQUIV.
Au niveau des possibilités, RECHERCHEX peut faire des recherches verticales et horizontales, et sait gérer nativement les cas où aucune correspondance n’est trouvée. La recherche pourra se faire dans une plage de cellules ou un tableau, et renvoyer les éléments correspondants dans un deuxième tableau ou une plage.
A noter : Avec RECHERCHEX, la valeur cherchée (la clé qui permet de faire les recherche) n’a plus besoin de se trouver dans la première colonne du tableau de recherche.
La fonction RECHERCHEX est à saisir de la façon suivante :
=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])
Liste des arguments de la fonction et description :
- valeur_cherchée : Il s’agit de la valeur à chercher dans votre tableau.
- tableau_recherche : Il s’agit du tableau ou de la plage de cellule où la recherche sera effectuée.
- tableau_renvoyé : Il s’agit du tableau ou de la plage à retourner lorsque valeur_cherchée a été trouvé dans tableau_recherche.
Les trois arguments listés ci-dessus sont obligatoires. D’autres arguments facultatifs sont proposés. S’ils ne sont pas renseignés, une valeur par défaut s’applique.
- [si_non_trouvé] : Si aucune correspondance valide n’est trouvée, la fonction pourra renvoyer le texte que vous aurez saisi dans l’argument [si_non_trouvé]. Si aucun élément n’est saisi dans [si_non_trouvé] et qu’il n’y a pas de correspondance trouvée, la fonction retourne une erreur #N/A.
- [mode_correspondance] : Permet de choisir le type de correspondance à utiliser avec la recherche. Les types de correspondance disponibles sont :
- 0 : Si aucun élément n’est trouvé, la fonction retourne #N/A (ou la valeur de [si_non_trouvé] si l’argument est renseigné). Il s’agit de la valeur par défaut.
- -1 : Si aucun élémént n’est trouvé, retournez l’élément suivant plus petit.
- 1 : Si aucun élément n’est trouvé, retournez le prochain article plus grand.
- 2 : Une correspondance générique où les caractères *, ?, et ~ ont une utilité particulière.
- [mode_recherche] : Permet de choisir le mode de recherche à utiliser. Les modes de recherche disponibles sont :
- 1 : Effectuez une recherche à partir du premier élément. Il s’agit de la valeur par défaut.
- -1 : Effectuez une recherche inversée en commençant par le dernier élément.
- 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.
Eléments de contexte de notre exemple :
Nous disposons d’une liste avec diverses informations mises en colonnes (colonnes A,B,C,D et E), dont un numéro de matricule.
Dans un autre tableau (colonne G), une liste de matricule tirés au sort est présente. l’objectif est de récupérer des éléments liés au matricule et de les afficher (en colonne H).
Aperçu :
Pour afficher le Nom (dans la cellule H2) associé au Matricule de la colonne G2, nous allons construire la fonction de la façon suivante :
=RECHERCHEX(G2;$D$2:$D$11;$A$2:$A$11)
- valeur_cherchée : G2 : Il s’agit de la cellule qui contient la valeur qu’on recherche (le matricule dans notre exemple)
- tableau_recherche : $D$2:$D$11 : Il s’agit de la plage de données qui contient les matricules dans le tableau dans lequel nous allons faire la recherche.
- tableau_renvoyé : $A$2:$A$11 : Il s’agit de la plage de cellules qui contient l’information qu’on souhaite afficher à coté du matricule (le nom dans notre exemple)
Remarque : Nous avons figés les plages de cellules avec des $ afin qu’elles ne soient pas modifiées en cas de duplication de la formule. La touche F4 permet d’ajouter les $ automatiquement à la saisie des éléments).
Aperçu des éléments sélectionnés dans la formule :
Aperçu du résultat :
Commentaires sur ce premier exemple et son résultat :
- RECHERCHEX nous a permis de rechercher un élément (le matricule) dans un tableau sans nécessiter cet élément recherché soit dans la première colonne du tableau de recherche. C’est une différence majeure avec RECHERCHEV.
- Seul le nom a été affiché, ce qui limite l’intérêt du tableau de résultat
- Il y a une erreur en H6 (c’est normal, le matricule 00014 n’existe pas).
- Dans les arguments saisis, nous avons omis tous les arguments facultatifs.
Dans l’exemple suivant, nous allons modifier notre tableau pour améliorer le résultat et corriger l’erreur.
Eléments de contexte de notre exemple :
Nous allons reprendre notre tableau précédent et l’améliorer.
L’idée sera d’afficher le nom et le prénom en face de chaque matricule existant, et de gérer l’erreur lorsqu’un matricule n’a pas été trouvé.
Aperçu du tableau :
Pour afficher le Nom (dans la cellule H2) et le Prénom (dans la cellule H3) associés au Matricule de la colonne G2, nous allons saisir la formule suivante dans la cellule H2 :
=RECHERCHEX(G2;$D$2:$D$11;$A$2:$B$11;"Inconnu")
- valeur_cherchée : G2 : Il s’agit de la cellule qui contient la valeur qu’on recherche (le matricule dans notre exemple)
- tableau_recherche : $D$2:$D$11 : Il s’agit de la plage de données qui contient les matricules dans le tableau dans lequel nous allons faire la recherche.
- tableau_renvoyé : $A$2:$B$11 : Il s’agit de la plage de cellules qui contient les informations qu’on souhaite afficher à coté du matricule (le nom et le prénom dans notre exemple). A noter, les informations trouvées seront restituées dans le même ordre (nom puis prénom dans notre cas. Les informations doivent être juxtaposées).
- [si_non_trouvé] : « Inconnu » sera affiché à la place de l’erreur #N/A.
Aperçu des éléments sélectionnés dans la formule :
Aperçu du résultat :
Commentaires sur ce deuxième exemple et son résultat :
- Nous avons pu restituer deux valeurs (nom et prénom) avec une seule formule. RECHERCHEV aurait nécessité de faire deux formules et de gérer les index de colonnes. Il faut cependant que les données soient juxtaposées dans le tableau.
- Nous avons géré très facilement l’absence de résultat en affichant un mot plutôt qu’une erreur #N/A. Avec RECHERCHEV, nous aurions du utiliser une formule imbriquée avec des fonctions SI et ESTERREUR.
La fonction RECHERCHEX permet d’utiliser des caractères génériques pour le paramètre valeur_cherchée. Cela peut vous être utile dans une recherche pour retrouver une correspondance alors que vous ne disposez pas de la valeur exacte à rechercher.
Les caractères génériques disponibles sont :
- * : Permet de remplacer une chaine de caractères dont on ne connait pas la longueur
- ? : Permet de remplacer un caractère dont on connait l’emplacement
- ~ : Permet de faire une recherche sur un caractère générique (rechercher un *, un ? ou un ~)
Dans cet exemple, nous allons tenter de retrouver le matricule d’une personne dont nous ne connaissons pas le nom exact. Nous en profiterons pour étudier un argument [mode_recherche] qui peut faire varier le résultat affiché.
Aperçu de notre tableau :
Dans la cellule H2, nous allons saisir la formule suivante :
=RECHERCHEX(G2;A2:A11;D2:D11;"";2;1)
- valeur_cherchée : G2 : Il s’agit de la cellule dans laquelle nous allons saisir la partie du nom dont on souhaite le matricule.
- tableau_recherche : A2:A11 : Plage de données qui contient les noms.
- tableau_renvoyé : D2:D11 : Plage de données qui contient les matricules.
- [si_non_trouvé] : « » : En cas d’erreur, on affichera du vide plutôt qu’une erreur.
- [mode_correspondance] : 2 : Ce paramètre est important, c’est lui qui permet de faire des recherches avec des caractères génériques (*, ? et ~).
- [mode_recherche] : 1 : On commence la recherche à partir du premier élément de la liste.
Testons maintenant différentes saisies dans la cellule G2 et voyons les résultats.
Si on tape « MA » dans la cellule G2, le résultat est : Vide.
Pourquoi ? : Aucun caractère générique n’a été saisi, et MA n’est pas un nom présent dans la liste des noms. C’est donc l’argument [si_non_trouvé] qui s’affiche.
Aperçu :
Utilisons un caractère générique. Nous allons saisir MA* qui signifie que le nom commence par MA, et qu’on ne sait pas combien de caractères sont manquants après « MA ».
Le résultat est 00001 car le premier nom qui commence par MA dans notre tableau est « MAKIMURA » et que le matricule correspondant est 00001.
Aperçu :
Remarque : MA?????? aurait retourné le même résultat car il y a 6 caractères inconnus.
Notre formule affiche le résultat du premier élément trouvé. Dans notre tableau, nous avons deux noms qui commencent par MA. En modifiant l’argument [mode_recherche], il est possible de faire la recherche en partant du dernier élément plutôt que du premier.
Il suffit de mettre -1 dans l’argument [mode_recherche] et le résultat sera différent.
Aperçu de la formule :
=RECHERCHEX(G2;A2:A11;D2:D11;"";2;-1)Aperçu du résultat :
La fonction RECHERCHEX peut également vous permettre de combiner des recherches verticales et horizontales (le même type de recherche pouvait être fait avec une combinaison de fonctions INDEX et EQUIV).
Nous allons reprendre notre tableau exemple et y ajouter, pour chaque personne, un chiffre d’affaire par trimestre (donc 4 colonnes supplémentaires). Nous allons utiliser la fonction RECHERCHEX pour faire une recherche du chiffre d’affaire d’un trimestre pour un matricule (par exemple, retrouver le chiffre d’affaire du deuxième trimestre pour la personne ayant le matricule 00003). L’objectif sera de pouvoir faire varier le matricule et le trimestre et d’obtenir le bon résultat. Pour réaliser cette recherche, nous allons imbriquer deux fonctions RECHERCHEX.
Aperçu du tableau :
Les cellules qui contiendront nos paramètres variables seront K2 et L2. Le montant du chiffre d’affaire trouvé par RECHERCHEX sera en cellule M2.
Dans la cellule M2, nous allons saisir la formule suivante :
=RECHERCHEX(K2;D2:D11;RECHERCHEX(L2;F1:I1;F2:I11))
Les arguments de notre formule sont les suivants :
- valeur_cherchée : K2 : Il s’agit de la cellule qui va contenir le matricule de la personne.
- tableau_recherche : D2:D11 : Il s’agit de la plage de cellules qui contient les matricules des personnes.
Remarque : A ce stade, notre recherche verticale est définie (on cherche le matricule dans un tableau). Dans l’argument tableau_renvoyé, nous allons saisir une deuxième fonction RECHERCHEX qui, pour le matricule trouvé, affichera le CA du trimestre souhaité. Notre deuxième fonction va faire une recherche horizontale sur les trimestres de chiffre d’affaire.
- tableau_renvoyé : RECHERCHEX(L2;F1:I1;F2:I11) : Nous saisissons une nouvelle fonction RECHERCHEX complète en tant qu’argument tableau_renvoyé. Les arguments de cette deuxième fonction RECHERCHEX sont les suivants :
- valeur_cherchée : L2 : Il s’agit de la cellule qui va contenir le nom du trimestre à rechercher.
- tableau_recherche : Il s’agit de la plage de cellules qui contient les noms de trimestre.
- tableau_renvoyé : Il s’agit de la plage de cellules qui va contenir la donnée recherchée (un croisement entre le matricule et le chiffre d’affaire d’un trimestre).
Aperçu des plages de données :
Testons la formule :
Saisissons 00003 en K2 et CA T2 en L2. La formule nous renvoie 6006, ce qui correspond bien au ciffre d’affaire du 2eme trimestre pour la personne ayant le matricule 00003.
Aperçu du résultat :