XLOOKUP() : la fonction de recherche la plus polyvalente d’Excel

Si vous avez déjà utilisé VLOOKUP() et été bloqué parce que vous deviez rechercher vers la gauche, XLOOKUP() règle ce problème. C’est la nouvelle fonction de recherche d’Excel. Elle peut remplacer VLOOKUP(), HLOOKUP() et même la plupart des formules INDEX() et MATCH() avec une seule formule simple.

XLOOKUP() peut rechercher dans n’importe quelle direction, renvoyer plusieurs colonnes à la fois et afficher un message personnalisé lorsqu’une valeur est manquante. 

Dans ce guide, nous commencerons par la syntaxe. Puis nous construirons de vraies formules pas à pas, en utilisant le même petit jeu de données tout du long. Nous verrons aussi les correspondances exacte et approximative, comment renvoyer plusieurs colonnes, comment gérer les erreurs, et comment XLOOKUP() se compare à VLOOKUP() et INDEX()/MATCH(). 

Comment utiliser XLOOKUP() dans Excel

Créons une vraie formule XLOOKUP() depuis zéro. 

Notre jeu de données contient cinq produits, chacun avec un ID, un nom, une catégorie et un prix, des colonnes A à D, avec les en-têtes en ligne 1.

Jeu de données pour XLOOKUP(). Image par l’auteur.

Disons que nous voulons trouver le prix de « P104 ». Voyons cela étape par étape.

Rechercher une valeur unique

Cliquez sur une cellule vide où afficher le résultat. J’utilise F2. Et saisissez la formule suivante :

=XLOOKUP("P104", A2:A6, D2:D6)

Dans cette formule : 

• « P104 » est le lookup_value, l’élément recherché

• A2:A6 est le lookup_array, la plage dans laquelle Excel cherche

• D2:D6 est le return_array, la plage d’où Excel récupère la réponse

Excel cherche « Product ID » en colonne A, trouve « P104 » en ligne 5 et renvoie la valeur correspondante de la colonne D : 45 $.

Au lieu de taper directement « P104 », vous pouvez faire référence à une cellule, pour changer la valeur de recherche sans modifier la formule elle-même. Dans notre cas, la cellule A5 contient « P104 », donc la formule devient :

=XLOOKUP(A5, A2:A6, D2:D6)

Utiliser XLOOKUP() dans Excel. Image par l’auteur.

Vous pouvez maintenant saisir n’importe quel ID produit dans A5, et le prix se mettra à jour automatiquement.

Renvoyer une valeur d’une autre colonne

C’est ici que se voit la vraie différence entre XLOOKUP() et VLOOKUP(). Vous n’êtes pas limité à renvoyer une valeur d’une colonne située à droite de votre colonne de recherche.

Disons qu’au lieu du prix, vous voulez la catégorie pour « P102 ». Même configuration, mais pointez le return_array vers la colonne C, « Category » :

=XLOOKUP(A3, A2:A6, C2:C6)

Ici, A3 renvoie Home. Si vous passez à A5, cela devient Electronics. De la même façon, changez à nouveau le return_array pour récupérer le nom du produit, le prix, ou toute autre colonne, tout en gardant le même lookup_value et lookup_array.

XLOOKUP() renvoie une valeur depuis une autre colonne. Image par l’auteur.

Avec VLOOKUP(), vous deviez compter les colonnes depuis la colonne de recherche et ajuster un numéro d’index à chaque fois que vous vouliez un résultat différent. Avec XLOOKUP(), vous pointez simplement directement sur la colonne souhaitée. 

Recopier la formule sur plusieurs lignes

Jusqu’ici, nous avons recherché une valeur à la fois. Mais la plupart des feuilles réelles doivent rechercher de nombreuses valeurs d’un coup, par exemple une liste complète d’IDs de commande à laquelle associer un prix.

Voici comment configurer cela : la colonne A, à partir de A2, contient déjà notre liste d’IDs produit. Nous voulons que les prix correspondants apparaissent à côté de chacun, en colonne F.

Pour ce faire, cliquez sur F2 et saisissez la formule, en référant A2 comme valeur de recherche ainsi :

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Cliquez à nouveau sur F2, puis faites glisser la poignée de recopie (le petit carré en bas à droite de la cellule) vers le bas sur le reste des lignes.

Excel recopie la formule vers le bas, et chaque ligne renvoie désormais le bon prix pour son propre ID produit. L’ID de A2 obtient son prix en F2, celui de A3 en F3, et ainsi de suite.

Recopiez la formule vers d’autres cellules. Image par l’auteur.

Remarquez les signes dollar autour de A2:A6 et D2:D6. C’est une référence absolue. Elle verrouille la plage pour qu’elle ne se décale pas lorsque vous copiez la formule vers le bas. 

C’est important ici, car chaque ligne doit interroger la même liste de produits, simplement avec une valeur de recherche différente à chaque fois.

Comment utiliser la correspondance exacte et approximative dans XLOOKUP()

Par défaut, XLOOKUP() cherche une correspondance exacte. S’il n’en trouve pas, il renvoie une erreur, sauf si vous avez défini l’argument if_not_found que nous avons vu plus haut. 

Mais XLOOKUP() peut aussi trouver la correspondance la plus proche, via l’argument match_mode. 

Correspondance exacte (par défaut)

Vous n’avez rien à ajouter. Toutes les formules construites jusqu’ici utilisent déjà la correspondance exacte, car c’est le comportement par défaut de XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel recherche la valeur exacte de A2 et ne renvoie un résultat que s’il trouve cette correspondance précise quelque part dans la plage.

Si vous voulez l’écrire explicitement, ajoutez un zéro en quatrième argument :

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() renvoie une correspondance exacte. Image par l’auteur.

Vous notez l’espace avant le 0 ? Cela permet de sauter if_not_found, que nous laissons vide ici, et de passer directement à match_mode. La plupart du temps, vous pouvez omettre totalement cet argument et laisser Excel se baser sur la correspondance exacte par défaut.

Correspondance approximative

Supposons maintenant que vos données n’aient pas une valeur pour chaque recherche possible. Pensez à une grille de commissions où les commerciaux gagnent des taux différents selon l’intervalle dans lequel se situe leur chiffre d’affaires total. Vous n’aurez pas de ligne pour chaque montant, seulement le point de départ de chaque plage.

C’est là qu’une correspondance approximative est utile. 

Réglez match_mode sur -1, et XLOOKUP() trouve la plus grande valeur inférieure ou égale à votre valeur recherchée.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Supposons que votre plage de recherche contienne des seuils de ventes de 0 $, 5 000 $ et 10 000 $, chacun associé à un taux de commission différent. 

Un commercial avec 7 500 $ de ventes ne correspond à aucune ligne exactement. Avec match_mode à -1, XLOOKUP() trouve 5 000 $, le plus grand seuil qui n’excède pas 7 500 $, et renvoie le taux de commission de cette ligne.

Réglez plutôt match_mode sur 1, et XLOOKUP() inverse la logique. Il trouve la plus petite valeur supérieure ou égale à votre valeur recherchée. Utile par exemple pour les paliers de poids d’expédition, où l’on arrondit au palier supérieur plutôt qu’inférieur.

XLOOKUP() renvoie une correspondance approximative. Image par l’auteur.

Remarque : la correspondance approximative suppose que votre plage de recherche est triée, croissante pour -1, décroissante pour 1. Si elle n’est pas triée correctement, XLOOKUP() peut renvoyer un mauvais résultat sans erreur, ce qui est pire. Vérifiez donc toujours l’ordre de tri avant de vous y fier.

Comment rechercher vers la gauche avec XLOOKUP()

VLOOKUP() ne recherche que dans la colonne la plus à gauche de votre plage et renvoie une valeur d’une colonne à sa droite ; il ne peut pas faire l’inverse. Son troisième argument n’est pas une plage ; c’est un index de colonne compté depuis le bord gauche. 

Impossible de lui dire « va une colonne à gauche ». Si la valeur voulue se trouve à gauche de votre colonne de recherche, vous devez réorganiser vos données ou ajouter une colonne d’aide juste pour que la recherche fonctionne.

XLOOKUP() n’a pas cette contrainte, car vous lui indiquez des plages distinctes pour la recherche et le renvoi.

Reprenons notre liste produits : l’ID produit est en colonne A, le nom du produit en colonne B. 

Supposons que vous ayez un nom de produit et souhaitiez trouver son ID ; l’inverse de ce que VLOOKUP() sait faire.

En C9, saisissez Bluetooth Speaker (un nom présent dans la liste). En E2, entrez :

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() cherche « Bluetooth Speaker » en colonne B, le trouve en ligne 5, et renvoie « P104 » depuis la colonne A, même si la colonne A est à gauche de la colonne B. 

XLOOKUP() recherche vers la gauche, contrairement à VLOOKUP(). Image par l’auteur.

Comment renvoyer plusieurs colonnes avec XLOOKUP()

XLOOKUP() peut renvoyer plusieurs colonnes d’un coup, dans une seule formule, grâce aux tableaux dynamiques. Pour cela, pointez return_array vers une plage au lieu d’une seule colonne, et XLOOKUP() « déverse » toute la ligne correspondante sur autant de cellules que nécessaire. 

Essayons avec notre liste de produits. 

Dans C9, je saisis un ID produit, par exemple « P104 ». En E2, j’entre :

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Remarquez que le return_array est ici B2:D6, sur trois colonnes, couvrant le nom, la catégorie et le prix. Une fois Entrée pressée, XLOOKUP() trouve « P104 » en colonne A, puis renvoie « Bluetooth Speaker », « Electronics » et 45 $ d’un coup, en se « déversant » dans E9, F9 et G9.

XLOOKUP() renvoie plusieurs colonnes avec une seule formule. Image par l’auteur.

C’est le principe de la plage de déversement. E9 contient la formule réelle, et F9 et G9 se remplissent d’eux-mêmes, sans formules propres. 

Cliquez sur F9, et la barre de formule affiche une version grisée de la formule d’origine, pas quelque chose tapé directement ici. Essayez de saisir dans F9 pendant que le déversement de E9 est actif, et Excel renverra une erreur de déversement. 

Nous verrons comment corriger cela dans la section sur les erreurs fréquentes.

Comment gérer les valeurs manquantes dans XLOOKUP()

Par défaut, XLOOKUP() renvoie une erreur #N/A lorsqu’il ne trouve aucune correspondance. L’argument if_not_found permet d’afficher ce que vous souhaitez à la place de l’erreur par défaut.  

Supposons qu’en C9, je tape un Product ID qui n’existe pas dans mes données, comme « P999 ».

Renvoyer un message personnalisé

En G2, je saisis :

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Comme « P999 » n’existe pas en colonne A, XLOOKUP() ignore l’erreur et renvoie « Not Found ». Changez l’expression selon vos besoins, par exemple « Vérifiez l’ID produit » ou « Aucune correspondance ».

Affichez un message personnalisé au lieu de l’erreur par défaut. Image par l’auteur.

Renvoyer une cellule vide

Parfois, vous voulez une cellule vide sans texte. Pour cela, utilisez deux guillemets sans rien entre, comme ceci :

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Utile lorsque vous utilisez cette formule dans un rapport, où un « Not Found » parasite ferait tache à côté d’une colonne de vraies valeurs.

Affichez une cellule vide au lieu de l’erreur par défaut. Image par l’auteur.

Laisser une erreur réelle

Si vous ignorez complètement if_not_found, XLOOKUP() revient à #N/A. Ce n’est pas toujours une mauvaise chose. Si vous créez une formule pour votre usage et voulez repérer immédiatement une faute de frappe ou une valeur manquante, voir apparaître #N/A est plus utile qu’une cellule silencieusement vide qui masque le problème.

Remarque : if_not_found ne capture que les correspondances manquantes. Si votre lookup_array et votre return_array n’ont pas la même taille, par exemple, XLOOKUP() renverra quand même une erreur #VALUE!, et if_not_found ne l’empêchera pas. Nous verrons ce cas précis dans la section « Erreurs fréquentes » plus bas.

Fonctionnalités avancées de XLOOKUP()

Voyons les fonctionnalités avancées de XLOOKUP().

Rechercher de bas en haut

Réglez search_mode sur -1, et XLOOKUP() balaie votre plage du bas vers le haut, au lieu de haut en bas. Utilisez-le lorsque vos données ont des doublons et que vous voulez l’entrée la plus récente, pas la première.

Disons que mon jeu de données contient trois IDs client en double. En cellule E2, j’entre la formule par défaut qui recherche de haut en bas : 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Elle est calculée depuis le début du tableau et affiche le résultat de la première correspondance. 

Mais si je veux rechercher depuis le bas, j’utiliserai cette formule : 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

Ici, -1 indique à Excel de démarrer la recherche depuis le bas du jeu de données. Cela renverra 340, car c’est la première valeur correspondante en partant du bas.  

XLOOKUP() balaie les données de bas en haut. Image par l’auteur.

Mode de recherche binaire

Réglez search_mode sur 2, et XLOOKUP() passera d’une vérification ligne par ligne à une recherche binaire. 

Voici le principe : 

• Il regarde d’abord l’élément du milieu de la plage. 

• Si ce n’est pas une correspondance, il élimine la moitié où la valeur ne peut pas se trouver, puis regarde le milieu de ce qui reste. 

• Il continue ainsi, en divisant les données par deux à chaque étape, jusqu’à trouver la correspondance. 

• Avec search_mode à -2, il fonctionne de la même manière, mais en partant du dernier élément plutôt que du premier.

Ici, en E2, entrez :

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Cette formule renvoie 200 à la place, une ligne complètement différente.

Le mode de recherche binaire donne un mauvais résultat. Image par l’auteur.

Parce que la recherche binaire ne fonctionne que si votre lookup_array est trié. Comme notre colonne Customer ID n’est pas triée (C100, C101, C100, C102, C100), la recherche binaire écarte la mauvaise moitié et renvoie une mauvaise correspondance. 

Voyons maintenant ce qui se passe lorsque les données sont réellement triées. 

Triez le même journal par Customer ID, par ordre croissant (les trois C100 au début) et relancez la même formule. 

Le mode de recherche binaire fonctionne avec des données triées. Image par l’auteur.

Cette fois, E2 renvoie 120, conforme à une recherche normale. Voilà la différence : la recherche binaire n’ignore que les données qu’elle peut écarter en toute sécurité, et cela n’est vrai que si les données sont triées au départ. 

Formules XLOOKUP() imbriquées

Imbriquez une XLOOKUP() dans une autre lorsqu’un premier résultat doit alimenter une seconde recherche, au lieu d’une plage fixe.

Imaginons que vous vouliez trouver un prix, mais que la colonne à utiliser dépende d’une recherche séparée, par exemple le segment tarifaire d’un client.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

Dans cette formule, la XLOOKUP() interne détermine d’abord quelle plage de colonnes utiliser, en fonction de D9, puis la XLOOKUP() externe emploie ce résultat comme return_array. 

XLOOKUP() imbriquée. Image par l’auteur.

XLOOKUP() vs. VLOOKUP()

Nous avons détaillé le fonctionnement de XLOOKUP(). Voici sa comparaison avec VLOOKUP().

Deux points méritent d’être soulignés :

• VLOOKUP() est en correspondance approximative par défaut. Si vous oubliez le 4e argument, il ne renverra pas d’erreur ; il renverra silencieusement une mauvaise valeur.

• Si vous insérez une colonne dans une feuille VLOOKUP(), les formules se mettent discrètement à renvoyer la mauvaise valeur. XLOOKUP() évite cela en se référant directement aux plages plutôt qu’en comptant les colonnes.

Rien de tout cela ne rend VLOOKUP() obsolète. Il reste très bien pour des feuilles simples et statiques. Mais pour tout nouveau fichier, XLOOKUP() est l’option la plus sûre.

XLOOKUP() vs. INDEX() et MATCH()

Avant XLOOKUP(), INDEX() et MATCH() ensemble étaient la méthode standard pour contourner les limites de VLOOKUP(). Cela utilise deux fonctions au lieu d’une. 

• MATCH() trouve la position de la valeur recherchée. 

• INDEX() utilise ensuite cette position pour récupérer le résultat où vous le souhaitez : à gauche comme à droite, peu importe.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Comparez avec la version XLOOKUP() utilisée tout au long de cet article :

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() fonctionne uniquement avec Microsoft 365 et Excel 2021 et versions ultérieures, comme nous l’avons indiqué plus haut. Si votre classeur doit s’ouvrir dans Excel 2019 ou 2016, ou si vous partagez avec des personnes sur des versions plus anciennes, INDEX() et MATCH() restent compatibles partout.

Il existe aussi un cas plus pointu. 

INDEX() et MATCH() séparent « trouver la position » de « renvoyer la valeur », ce qui aide dans certaines formules imbriquées complexes. Cela arrive rarement, donc la plupart des lecteurs n’en auront pas besoin.

En dehors de ces deux cas, XLOOKUP() est le meilleur choix pour un nouveau classeur. 

Erreurs XLOOKUP() courantes et comment les corriger

Voici un aide-mémoire des problèmes les plus fréquents et comment chacun se corrige.

Erreurs #N/A

C’est la réponse par défaut de XLOOKUP() lorsqu’il ne trouve pas de correspondance. Je suis tombé dessus plus haut lorsque mon lookup_value et mon lookup_array n’étaient pas alignés, et qu’XLOOKUP() n’avait rien à comparer en colonne A.

Pour corriger : vérifiez que votre lookup_value existe bien dans votre lookup_array, avec la même orthographe et le même format. Si les absences de correspondance sont normales, utilisez l’argument if_not_found pour remplacer #N/A par quelque chose de plus utile, comme « Not Found » ou une cellule vide, comme vu plus haut. 

Mauvaise plage de recherche

Cela se produit lorsque vos lookup_array et return_array ne sont pas correctement alignés : mauvaises lignes, mauvaises colonnes ou tailles de plage différentes. 

Selon votre version d’Excel ou de Sheets, cela peut apparaître en #VALUE!, ou parfois en #N/A avec un message séparé expliquant le vrai problème. Ne laissez pas le #N/A vous faire croire à une simple absence de correspondance. 

Pour corriger : assurez-vous que lookup_array et return_array couvrent le même nombre de lignes ou de colonnes. Si lookup_array est A2:A5, return_array doit aussi couvrir les lignes 2 à 5, pas 4 à 5 ni toute autre plage décalée.

Incohérences de type de données

VLOOKUP() peut renvoyer une erreur s’il attendait un nombre mais a reçu du texte. XLOOKUP() peut rencontrer le même souci. Si votre lookup_value est un nombre, mais que votre lookup_array stocke ces valeurs comme du texte, ou l’inverse, XLOOKUP() ne trouvera pas la correspondance, même si visuellement les valeurs semblent identiques.

Pour corriger : vérifiez que votre lookup_value et votre lookup_array utilisent le même type de données. Les nombres stockés en texte apparaissent souvent alignés à gauche plutôt qu’à droite. Enveloppez votre lookup_value dans VALUE() pour convertir du texte en nombre, ou utilisez TEXT() pour faire l’inverse, selon le sens du décalage.

Erreurs de déversement

Cela arrive lorsque votre return_array couvre plus d’une colonne, mais que les cellules nécessaires au déversement de XLOOKUP() ne sont pas vides. Excel a besoin d’espace libre à côté de la cellule de formule pour déverser les résultats. Si quelque chose occupe déjà ces cellules, la formule échoue.

Pour corriger : videz les cellules où les résultats doivent aller, puis saisissez votre formule. Si vous avez besoin de contenu dans ces cellules, placez votre formule XLOOKUP() ailleurs, avec de l’espace libre autour.

En conclusion 

Vous savez désormais utiliser XLOOKUP() pour des valeurs uniques, plusieurs colonnes, des données manquantes, et même des recherches imbriquées sur deux plages. C’est déjà beaucoup.

À l’avenir, faites de XLOOKUP() votre réflexe pour tout nouveau classeur. Plus flexible que VLOOKUP(), plus lisible que INDEX/MATCH(), et vous évite d’envelopper chaque formule dans IFERROR().

Si vous voulez aller plus loin, cela vaut la peine d’apprendre aussi INDEX() et MATCH() séparément.