Microsoft a sorti en 2019 une fonction appelée RECHERCHEX (ou XLOOKUP en anglais). Cette fonction vise à remplacer les fonctions incontournables que sont RECHERCHEV, RECHERCHEH et INDEX/EQUIV en facilitant les recherches dans des tables de données Excel.
Il a fallu attendre de nombreuses années mais désormais les utilisateurs d'Excel disposent d'une fonction plus intuitive, plus flexible et qui évite certaines erreurs frustrantes de RECHERCHEV.
Quand RECHERCHEX débarque !
Voyons donc les principes de la fonction RECHERCHEX, comment l'utiliser dans les formules Excel et comment elle fonctionne par rapport aux autres fonctions de recherche.
TABLE DES MATIERES
Rappel : la fonction RECHERCHEV
Note: Tout ce qui est mentionné à propos de RECHERCHEV dans cet article vaut pour RECHERCHEH
Comment utiliser la RECHERCHEV
RECHERCHEV est l'une des fonctions Excel les plus utilisées dans le monde.
Pourquoi donc? Supposons d'une part que vous ayez une liste d'identifiants (par exemple, un numéro de sécurité sociale), et d'autre part que vous ayez un tableau de correspondance, avec tous les identifiants dans une colonne et le nom de chaque personne correspondante dans la deuxième colonne.
Maintenant, ce dont vous avez besoin est d'identifier dans votre première liste, qui sont les personnes en fonction de leurs identifiants. Techniquement, vous souhaitez ajouter, à côté de chaque identifiant de votre première liste, le nom correspondant à ces identifiants en fonction de la correspondance que vous avez dans le deuxème tableau.
Alors, comment y parvenir ? Astuce : la réponse n'est PAS de passer les deux prochains mois à vérifier chaque pièce d'identité une par une. Donc, ce que vous feriez plutôt, c'est d'utiliser la puissance de la fonction RECHERCHEV dans Excel.
Voici à quoi ça ressemble :
=RECHERCHEV(valeur_cherchée; table_matrice; no_index_col; [valeur_proche])
valeur_cherchée: c'est la valeur que vous recherchez dans la première colonne de la base de données, ici c'est votre première liste d'identifiants.
table_matrice: c'est la plage de données dans laquelle vous voulez effectuer la recherche. Ici c'est la deuxième liste qui est un tableau avec la correspondance entre tous les identifiants et le nom.
no_index_col: représente le numéro de la colonne de la table_matrice dans laquelle se trouvent les informations que vous recherchez. Ici vous voulez le contenu de la 2ème colonne (les noms).
[valeur_proche]: 99% du temps, vous saisirrez 0 (ou FAUX) pour spécifier que vous voulez un résultat uniquement si la fonction RECHERCHEV trouve une correspondance exacte dans la table_matrice. # N/A sera renvoyé si aucune correspondance n'est trouvée. Si 1 (ou VRAI) est renseigné et qu'aucune valeur exacte n'est trouvée, la fonction renvoie la valeur imméditament inférieure à la valeur_cherchée. Parfois, c'est utile, mais la plupart du temps, ça génère des erreurs et approximations qui impactent la qualité de vos résultats !
Un exemple vaut mieux que 1000 mots, regardons celui-là :
Le(s) problème(s) avec la RECHERCHEV
Alors, qu'est-ce qui ne va pas avec RECHERCHEV ?
Avec RECHERCHEV, il y a un peu du bon, de la brute et du truand.
Le bon car une fois que vous avez compris son fonctionnement, vous ne pourrez plus vous en passer tant cette fonction est essentielle. En effet, lorsque vous travaillez beaucoup avec Excel, vous l'utilisez BEAUCOUP.
La brute car RECHERCHEV s'exécute vraiment lentement. La plupart du temps, vous ne le sentirez pas mais si vous commencez à lancer cette recherche sur des milliers de lignes, vous remarquerez que le calcul prend quelques secondes. Et si votre base de données a des dizaines ou des centaines de milliers de lignes comme ça peut être rapidement le cas à l'heure du big data, vous devez attendre plusieurs minutes pour qu'Excel calcule les résultats de la recherche. Oui, c'est brutal !
Astuce : consultez notre article sur l'astuce DOUBLE VLOOKUP pour que la RECHERCHEV s'exécute cent fois plus vite !
Le truand car RECHERCHEV n'est NI pratique NI flexible et ce pour 3 raisons :
Cela ne fonctionne que de gauche à droite. Ainsi, dans notre exemple, si les identifiants étaient dans une colonne à droite des noms, cela ne fonctionnerait pas. Mince.
La colonne avec le résultat souhaité est désignée par un index. Si vous voulez que RECHERCHEV renvoie les données de la deuxième colonne, vous devez taper 2. Mais que se passe-t-il si vous insérez a posteriori une autre colonne dans la table_matrice? Eh bien, l'indice sera toujours 2 et votre fonction ne renverra plus le résultat attendu. Mince encore.
Microsoft a fait une grosse erreur avec RECHERCHEV. L'argument [valeur_proche] est optionnel... mais pour une raison obscure, ils ont décidé que cet argument serait VRAI par défaut, alors que les gens ne l'utilisent que comme FAUX. Ainsi, bien que cet argument soit théoriquement facultatif, en pratique il est obligatoire car vous devez spécifier FAUX à chaque fois. Un vrai truand ce RECHERCHEV!
Une amélioration partielle : Combiner les fonctions INDEX et EQUIV
L'une des solutions les plus populaires pour contrer les limites de RECHERCHEV consiste à combiner 2 fonctions Excel, INDEX et EQUIV.
Voici la syntaxe de cette solution :
=INDEX(matrice; EQUIV(valeur_cherchée, tableau_recherche, [type))
L'intérêt principal de la combinaison des fonctions INDEX/EQUIV est qu'elle remplace le no_index_col de RECHERCHEV par une référence. C'est beaucoup plus flexible :
1) La colonne de recherche n'a pas besoin d'être à gauche du tableau
2) Vous pouvez insérer et supprimer des colonnes sans casser la formule
3) Cette combinaison fonctionne à la fois verticalement et horizontalement, alors que la RECHERCHEV devait être intervertie avec la RECHERCHEH pour les recherches de données horizontales
De plus, la combinaison INDEX/EQUIV s'exécute plus rapidement que la RECHERCHEV, ce qui peut faire la différence sur de grandes bases de données.
Cependant, cette méthode n'est pas parfaite. Le principal inconvénient est qu'il est plus long à écrire et plus complexe. Pour saisir rapidement des formules ou si votre fichier doit être lu par des utilisateurs d'Excel pas aussi avancés que vous, vous préférerez peut-être opter pour la RECHERCHEV.
Et maintenant... c'est là que la RECHERCHEX entre en jeu.
Comment utiliser RECHERCHEX, le futur des fonctions RECHERCHE d'Excel
Comment utiliser RECHERCHEX
Microsoft a sorti RECHERCHEX dans le but de remplacer les fonctions RECHERCHEV, RECHERCHEH et INDEX/EQUIV.
La syntaxe de RECHERCHEX se décompose comme suit :
=RECHERCHEX(valeur_cherchée, tableau_recherche, tableau_renvoyé, [si_non_trouvé],[mode_correspondance], [mode_recherche])
Comme pour RECHERCHEV ou INDEX/EQUIV, il y a 3 arguments obligatoires. Les 3 derniers, qui sont entre crochets, sont des arguments optionnels :
valeur_cherchée: c'est la valeur que vous recherchez
tableau_recherche: C'est la plage de données dans laquelle vous recherchez la valeur
tableau_renvoyé: C'est la plage de données que vous cherchez à renvoyer
[si_non_trouvé]: au lieu de # N/A, vous pouvez spécifier ce qui doit être renvoyé au cas où aucune correspondance n'est trouvée
[mode_correspondance]: RECHERCHEX a par défaut un mode de correspondance exacte, donc la plupart du temps, vous n'aurez pas besoin d'utiliser cet argument ! Comme avec RECHERCHEV ou INDEX/EQUIV, 0 assure une correspondance exacte et renvoie # N/A si aucune correspondance n'est trouvée. Vous pouvez également utiliser -1 pour retourner l'élément suivant plus petit. si aucune correspondance n'est trouvée, ou 1 pour renvoyer le prochaine élément le plus grand si aucune correspondance n'est trouvée. Il est également possible d'utiliser 2, qui est une correspondance générique où "*", "?" et "~" ont une signification particulière.
[mode_recherche]: Vous pouvez utiliser 1 pour rechercher à partir du premier élément et -1 pour rechercher à partir du dernier élément. Vous pouvez également utiliser 2 pour rechercher dans un tableau_recherche trié en ordre croissant et -2 pour rechercher dans un tableau_recherche trié en ordre décroissant ( cf. notre article sur l'astuce DOUBLE VLOOKUP pour voir l'exécution de recherches binaires dans des tableaux triés). Les résultats renvoyés seront invalides si le tableau n'est pas trié correctement.
Exemple 1: la RECHERCHEX de base
Commençons par nous concentrer sur les arguments obligatoires. Voici à quoi ressemble RECHERCHEX dans sa forme la plus simple:
Comme vous pouvez le voir ci-dessus, RECHERCHEX est utilisé dans la cellule G3 pour afficher un préfixe téléphonique de pays basé sur le texte en F3.
Facile non ?
RECHERCHEV est (presque) mort
Avec RECHERCHEX, nous obtenons le meilleur des 2 mondes : comme RECHERCHEV, c'est très simple à écrire et à mémoriser, et comme INDEX/EQUIV, la table_matrice est une référence qui la rend plus flexible. Et cerise sur le gâteau, vous n'avez pas besoin de spécifier le mode_correspondance pour obtenir une correspondance exacte.
Voyons maintenant comment vous pouvez utiliser les arguments facultatifs quelques peu effrayants pour créer des formules puissantes.
Exemple 2 : utilisation de l'argument si_non_trouvé
Ci-dessous, l'argument si_non_trouvé est utilisé pour ajouter un message d'erreur personnalisé :
C'est beaucoup plus clair que d'obtenir des résultats # N/A difficiles à interpréter.
Dans cet exemple, l'argument si_non_trouvé est du texte. Mais vous pouvez également renvoyer une valeur ou une plage. Dans de nombreux cas, cela vous évite d'avoir à combiner RECHERCHEV avec SI, SIERREUR ou d'autres formules pour gérer l'absence de correspondance.
Exemple 3: utilisation des arguments si_non_trouvé, mode_correspondance et mode_recherche
Ci-dessous, les 3 arguments optionnels sont utilisés :
L'argument si_non_trouvé est utilisé avec la valeur 0 pour retourner 0 au lieu de # N/A en l'absence de correspondance.
L'argument mode_correspondance est utilisé avec la valeur 1, ce qui va exécuter une recherche exacte ou obtenir la prochaine valeur supérieure au cas où aucune correspondance n'est trouvée
L'argument mode_recherche est aussi utilisé avec la valeur 1, ce qui va lancer la recherche en commençant du premier élément jusqu'au dernier.
Exemple 4: retourner des données de plusieurs colonnes
Dans l'exemple ci-dessous, RECHERCHEX recherche et affiche les informations d'un employé en fonction de son identifiant.
RECHERCHEX affiche à la fois le nom de l'employé en C2 et le département en D2, avec une seule formule.
En effet, RECHERCHEX est capable de renvoyer des tableaux avec plusieurs éléments. C'est très puissant et fait gagner beaucoup de temps si vous avez besoin d'obtenir des données à partir de nombreuses colonnes.
Faites attention quand même au troisième argument (l'argument table_matrice). On s'attendrait à ce que ce soit C5:C14, mais c'est C5:D14, ce qui signifie que les résultats proviendront à la fois des colonnes C et D !
Autres fonctionnalités avancées de RECHERCHEX
Les caractères génériques
Le quatrième argument dans RECHERCHEX est le [mode_correspondance] facultatif. Celui-ci a quatre options.
Les trois premiers (0, 1 ou -1) sont similaires à la fonction EQUIV et permettent d'effectuer une correspondance exacte, d'obtenir la prochaine plus petite valeur au cas où aucune correspondance n'est trouvée, ou d'obtenir la prochaine valeur supérieure au cas où aucune correspondance n'est trouvée. Rien de nouveau ici.
La quatrième option est de recourir aux caractères génériques. Utilisez 2 comme [mode_correspondance], et cela vous permettra d'utiliser des caractères génériques pour exécuter des recherches de correspondance partielles. Cela peut être extrêmement utile.
L'astérisque (*) représente n'importe quel numéro de caractères, tandis qu'un point d'interrogation (?) représente n'importe quel caractère unique.
Prenons un exemple. Imaginez que vous souhaitiez rechercher à partir d'un prénom le nom complet d'une liste de personnes. Vous exécuteriez alors une recherche partielle, comme indiqué ci-dessous :
Ici, le premier argument ("*"&A4) pourrait être lu comme "n'importe quoi suivi de la valeur en A4". En règle générale, vous pouvez également utiliser "*"&A4&"*" pour dire "tout ce qui contient la valeur en A4". Cela signifie que grâce aux caractères génériques, vous pouvez utiliser RECHERCHEX pour rechercher des valeurs contenant la valeur_cherchée, et pas seulement des valeurs qui lui sont égales.
Maintenant, un autre exemple : supposons que vous souhaitez rechercher un nom, mais que vous ne savez pas s'il doit être orthographié avec un "k" ou un "c". Vous pouvez utiliser le caractère générique point d'interrogation pour spécifier que ce caractère peut prendre n'importe quelle valeur, comme dans l'exemple ci-dessous :
Les caractères génériques sont utiles et pas seulement pour RECHERCHEX. Vous pouvez en apprendre plus à leur sujet ici.
N'oubliez pas que vous devez utiliser 2 comme [mode_correspondance] pour utiliser les caractères génériques dans RECHERCHEX.
Recherche dans l'ordre inverse
En utilisant l'argument optionnel [mode_recherche], vous pouvez effectuer des recherches dans l'ordre inverse. La valeur par défaut est 1 pour rechercher dans l'ordre du premier au dernier, mais vous pouvez utiliser -1 pour rechercher du dernier au premier. La recherche s'effectuera dans l'ordre inverse, soit de bas en haut, soit de droite à gauche.
Ca peut être utile si vous cherchez la dernière occurrence de la valeur_cherchée.
RECHERCHEX Vs RECHERCHEV Vs INDEX/EQUIV
Résumons comment RECHERCHEX surpasse RECHERCHEV et INDEX/EQUIV
C'est la fonction la plus simple, avec seulement 3 arguments nécessaires dans la plupart des cas car le mode_correspondance par défaut est 0 (correspondance exacte).
C'est une fonction unique, contrairement à la combinaison INDEX/EQUIV, donc plus rapide à saisir.
Elle fonctionne à la fois verticalement et horizontalement (contrairement à RECHERCHEV et à son alter ego RECHERCHEH).
Il n'est pas nécessaire que les valeurs de recherche soient à gauche (contrairement à RECHERCHEV).
Elle peut renvoyer des résultats personnalisés au lieu de # N/A lorsqu'aucune correspondance n'est trouvée, sans avoir besoin de combiner des fonctions.
Elle peut renvoyer un tableau, et pas seulement une valeur unique.
Elle peut effectuer des recherches binaires pour calculer plus rapidement sur des données triées lorsque [mode_recherche] est -2 ou 2.
Elle peut exécuter des recherches partielles, en utilisant des caractères génériques lorsque [mode_correspondance] est 2.
Elle peut exécuter des recherches inversées lorsque [mode_recherche] est -1.
Eh bien, ça fait BEAUCOUP de raisons pour commencer à apprendre et à utiliser RECHERCHEX !
Cependant, l'utilisation de RECHERCHEX implique certains inconvénients qu'il faut garder à l'esprit.
Avec tous ses problèmes, le no_index_col de RECHERCHEV avait quand même le mérite de ne nécessiter qu'un caractère, au lieu de sélectionner le tableau_renvoyé entier.
Ce tableau doit avoir la même taille que tableau_recherche ou vous obtiendrez une erreur.
De plus, vous devez utiliser des références absolues avant de faire glisser la formule vers le bas au risque d'obtenir des résultats erronés.
Enfin, RECHERCHEX n'est pas rétrocompatible. Donc, avant de commencer à envoyer des feuilles de calcul par e-mail avec RECHERCHEX, assurez-vous que les destinataires aient une version Excel qui le lit.
Si vous êtes un gros utilisateur d'Excel, vous aimerez le complément Power-user. Obtenez des dizaines de nouvelles fonctionnalités avancées qui feront de vous le patron d'Excel !