XLOOKUP dans Excel : syntaxe, exemples et pourquoi il surpasse VLOOKUP
Par Mirko Peters · · Mis à jour
XLOOKUP dans Excel expliqué : la syntaxe, chaque argument, des exemples de correspondances exactes, avec caractères génériques et dernières occurrences, la recherche sur plusieurs colonnes, et ce qui le distingue de VLOOKUP.
Traduit automatiquement par IA à partir de l'article original. Lire l'original en anglais
XLOOKUP dans Excel recherche une valeur dans une plage et renvoie l’élément correspondant d’une autre plage : =XLOOKUP(lookup_value, lookup_array, return_array). Saisissez un identifiant d’employé, pointez la colonne des identifiants, puis la colonne des noms, et vous obtenez le nom. Cet article s’adresse aux utilisateurs d’Excel qui écrivent encore VLOOKUP par habitude, ou qui viennent de découvrir XLOOKUP dans le fichier d’un collègue. Vous y trouverez la syntaxe, les six arguments, des exemples concrets pour les cas rencontrés au travail, la comparaison avec VLOOKUP et la liste des erreurs que vous risquez de rencontrer.
Points clés
- XLOOKUP prend une valeur recherchée, une plage de recherche et une plage de renvoi : =XLOOKUP(lookup_value, lookup_array, return_array). Il trouve la première correspondance et renvoie l’élément de la plage de renvoi.
- Il peut chercher à gauche de la colonne de recherche et effectue une correspondance exacte par défaut, deux possibilités que VLOOKUP n’offre pas ou n’applique pas par défaut.
- Le quatrième argument, if_not_found, remplace l’erreur #N/A par le texte de votre choix. Sans lui, une recherche sans résultat renvoie #N/A.
- match_mode 2 active les caractères génériques, -1 et 1 recherchent l’élément immédiatement inférieur ou supérieur, et search_mode -1 effectue la recherche du dernier élément au premier.
- La page de Microsoft répertorie XLOOKUP pour Microsoft 365, 2021 et 2024, et ses remarques sur Excel 2016 et 2019 se contredisent : testez donc les fichiers partagés avant de vous y fier.
La syntaxe et les options ci-dessous suivent la page de Microsoft consacrée à la fonction XLOOKUP. Lorsqu’un modèle n’y figure pas, le texte le précise. Il s’agit d’une fonction d’Excel, et c’est celle que Microsoft lui-même recommande comme meilleur choix lorsque vous ouvrez la page d’aide de VLOOKUP.
La syntaxe et les six arguments
La formule complète se présente ainsi :
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Les trois premiers arguments sont obligatoires. Les trois derniers sont facultatifs.
lookup_valueest la valeur recherchée, par exemple un identifiant en cellule D2. Si vous l’omettez, XLOOKUP renvoie les cellules vides qu’il trouve dans le tableau de recherche.lookup_arrayest la plage ou le tableau dans lequel effectuer la recherche, par exemple A2:A100.return_arrayest la plage ou le tableau dans lequel prélever la valeur à renvoyer, par exemple B2:B100.if_not_foundest le texte affiché lorsqu’aucune correspondance valide n’est trouvée. Si vous l’omettez, vous obtenez #N/A.match_modedéfinit le mode de correspondance.0correspond à une correspondance exacte et constitue la valeur par défaut.-1signifie une correspondance exacte ou, à défaut, l’élément immédiatement inférieur.1signifie une correspondance exacte ou l’élément immédiatement supérieur.2active une recherche avec caractères génériques, où*,?et~ont une signification particulière.search_modedéfinit le sens de la recherche.1parcourt les éléments du premier au dernier et constitue la valeur par défaut.-1parcourt du dernier au premier.2et-2sont des recherches dichotomiques sur des données triées par ordre croissant ou décroissant. Microsoft avertit que des données non triées produisent des résultats erronés avec ces modes dichotomiques.
Gardez le tableau de recherche et le tableau de retour de même taille. Si le tableau de recherche couvre les lignes 2 à 100, le tableau de retour doit couvrir lui aussi les lignes 2 à 100. La page de Microsoft ne l'énonce pas comme une règle, mais des plages qui ne correspondent pas sont la cause habituelle d'une recherche qui renvoie la mauvaise ligne ou une erreur.
Recherches de base et argument de valeur introuvable
Supposons que les identifiants des employés se trouvent dans A2:A100 et les noms dans B2:B100. Saisissez en D2 l'identifiant recherché.
- Cliquez sur la cellule où doit apparaître le résultat, par exemple E2.
- Saisissez
=XLOOKUP(D2,A2:A100,B2:B100). - Appuyez sur Entrée.
Vous obtenez le nom correspondant à l'identifiant. XLOOKUP renvoie l'élément de la première correspondance trouvée. Si l'identifiant n'existe pas, vous obtenez #N/A.
Pour afficher un message plus convivial, ajoutez le quatrième argument :
=XLOOKUP(D2,A2:A100,B2:B100,"No record found")
Désormais, un identifiant absent affiche « No record found ». Utilisez cette option pour les feuilles que d'autres personnes consultent. Attention toutefois : un message de ce type peut masquer un vrai problème. Si toutes les lignes affichent ce message, le problème vient des plages et non des données. Testez donc d'abord un identifiant connu.
Regarder vers la gauche
VLOOKUP ne peut pas renvoyer une valeur provenant d'une colonne située à gauche de la colonne de recherche. XLOOKUP le peut, car les plages de recherche et de retour sont indépendantes. Si le nom se trouve en colonne C et que l'identifiant recherché se trouve en colonne A :
=XLOOKUP(F2,C2:C100,A2:A100)
La page de Microsoft le dit explicitement : la colonne de recherche peut se trouver de part et d'autre de la colonne de retour.
Modes de correspondance : caractères génériques et valeurs les plus proches
Correspondances avec caractères génériques
Définissez match_mode sur 2 pour utiliser des caractères génériques. L'astérisque remplace un nombre quelconque de caractères, et le point d'interrogation exactement un caractère. Le tilde permet de rechercher un astérisque ou un point d'interrogation littéral.
=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)
Cette formule renvoie la première entrée qui commence par « Jan ». Si vous oubliez le 2, Excel recherche le texte littéral « Jan* » et renvoie votre message de valeur introuvable. C'est l'erreur la plus fréquente avec les caractères génériques. Rendez vos motifs aussi précis que possible, car XLOOKUP ne renvoie que la première correspondance, et un motif trop large peut en trouver une que vous ne souhaitiez pas.
Élément immédiatement inférieur ou supérieur
Les modes -1 et 1 servent pour les barèmes : taux d'imposition, paliers de commission, seuils de prix. Placez les seuils en A2:A6 et les taux en B2:B6, puis recherchez un revenu en E2 :
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)
Avec -1, Excel renvoie la correspondance exacte ou, à défaut, le seuil immédiatement inférieur. L'emplacement vide avant le -1 ignore l'argument de valeur introuvable. L'exemple de Microsoft pour la correspondance approximative utilise 1 pour renvoyer le taux d'imposition correspondant à la valeur de revenu immédiatement supérieure. Choisissez donc le sens adapté à votre tableau. Contrairement à l'ancienne recherche approximative de VLOOKUP, vous n'avez pas besoin de trier les seuils pour ces modes. Ce sont les modes de recherche binaire qui exigent des données triées.
Rechercher depuis la fin : la dernière correspondance
Lorsqu'une valeur apparaît plusieurs fois, XLOOKUP renvoie la première rencontrée. Pour obtenir la dernière, lancez la recherche depuis la fin en définissant search_mode sur -1 :
=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)
Si la feuille répertorie les commandes par ordre chronologique, cette formule renvoie la dernière entrée d'un client. Le 0 garantit une correspondance exacte. C'est la méthode à utiliser pour les questions du type « dernier prix » ou « dernier contact », qui étaient fastidieuses à résoudre avec VLOOKUP.
Renvoyer plusieurs colonnes à la fois
La page de Microsoft précise que, contrairement à VLOOKUP, XLOOKUP peut renvoyer plusieurs colonnes sous forme de tableau unique. Élargissez la plage de retour :
=XLOOKUP(D2,A2:A100,B2:D100)
La formule place la correspondance de la colonne B dans la cellule et répartit les valeurs des colonnes C et D dans les cellules situées à droite. Prévoyez l'espace nécessaire. Si les cellules de droite contiennent déjà des données, Excel affiche une erreur de débordement au lieu de les écraser. L'exemple de Microsoft renvoie, à partir d'une seule formule, le nom d'un employé et son service.
Recherches à double entrée
Microsoft montre comment imbriquer deux fonctions XLOOKUP pour trouver une valeur à l'intersection d'une ligne et d'une colonne. Le modèle, tiré de sa page, est le suivant :
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
La fonction interne sélectionne la colonne qui correspond à l’en-tête situé en C3. La fonction externe recherche ensuite D2 dans les étiquettes de lignes et renvoie la valeur de cette colonne. Microsoft compare ce fonctionnement à la combinaison INDEX et EQUIV. Si vous disposez d’un rapport avec des produits en lignes et des mois en colonnes, cette formule remplace une formule beaucoup plus complexe.
Plusieurs critères
XLOOKUP prend une seule valeur de recherche, et la page de Microsoft ne propose aucun exemple avec plusieurs critères. Une méthode courante consiste à faire tester par Excel deux conditions simultanément. Supposons que la catégorie figure en A, le statut en B et le prix en C. Pour trouver le prix correspondant à la catégorie en F2 et au statut en G2 :
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Non trouvé")
Chaque comparaison renvoie VRAI ou FAUX. Leur multiplication donne 1 uniquement pour les lignes où les deux conditions sont vraies, et XLOOKUP recherche le premier 1. Il s’agit d’une méthode qui fonctionne dans les versions actuelles de Microsoft 365, mais elle ne figure pas sur la page de fonction de Microsoft ; testez-la donc sur un petit échantillon. Une colonne auxiliaire qui concatène les deux champs constitue l’alternative plus ancienne. Insérez un séparateur entre les valeurs, par exemple =A2&"|"&B2, puis recherchez ce même texte concaténé.
XLOOKUP et VLOOKUP côte à côte
La page consacrée à RECHERCHEV de Microsoft indique la syntaxe VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) et précise que la valeur recherchée doit se trouver dans la première colonne de la plage. Si vous omettez range_lookup, VLOOKUP effectue une correspondance approximative, qui suppose que la première colonne est triée. Définissez-le sur FAUX pour une correspondance exacte. La même page suggère d’essayer XLOOKUP, qui fonctionne dans n’importe quel sens et renvoie par défaut des correspondances exactes.
Le même travail avec les deux fonctions, avec des identifiants en colonne A et des noms en colonne B :
=VLOOKUP(D2,A2:B100,2,FALSE)=XLOOKUP(D2,A2:A100,B2:B100)
Les différences qui comptent au quotidien :
- VLOOKUP désigne le résultat par son numéro de colonne ; l’insertion d’une colonne dans le tableau modifie donc la signification de ce numéro. XLOOKUP fait directement référence à la plage de retour.
- VLOOKUP ne peut pas rechercher vers la gauche. XLOOKUP le peut.
- VLOOKUP effectue par défaut une correspondance approximative. XLOOKUP effectue par défaut une correspondance exacte.
- XLOOKUP intègre un argument pour les valeurs introuvables ainsi que le sens de recherche.
Il n’est pas nécessaire de réécrire les anciens classeurs. Si une formule VLOOKUP fonctionne, conservez-la. Utilisez XLOOKUP pour les nouvelles formules et pour les recherches qui nécessitent une recherche vers la gauche, une recherche de la dernière correspondance ou plusieurs colonnes de retour.
Versions d’Excel prenant en charge XLOOKUP
Il faut être vigilant sur ce point. La page de Microsoft répertorie XLOOKUP pour Excel pour Microsoft 365 sous Windows et Mac, Excel 2024, Excel 2021, ainsi que pour les applications iPad, iPhone et Android. Une note de la page indique qu’il n’est pas disponible dans Excel 2016 ou 2019, mais qu’il peut apparaître dans les classeurs créés dans des versions plus récentes ; or la page mentionne aussi les versions 2016 et 2019 dans sa ligne « s’applique à ». Les informations se contredisent : ne considérez donc pas Excel 2016 et 2019 comme sûrs. Excel pour le web n’figure pas non plus sur cette page.
Si vous envoyez un classeur à une personne utilisant une version plus ancienne, la formule peut afficher une erreur là où une valeur devrait apparaître. Demandez quelle version utilisent vos destinataires et, pour les fichiers destinés à de nombreuses personnes, testez sur la plus ancienne version en service. Si vous ne pouvez pas tester, utilisez une fonction de recherche classique pour ce fichier.
Erreurs courantes et messages d’erreur
- Oublier la valeur
match_mode2 lorsque vous utilisez des caractères génériques. La recherche porte alors sur les caractères littéraux. - Utiliser un
search_modebinaire (2 ou -2) sur des données non triées. Microsoft indique que les résultats sont alors invalides. - Plages de recherche et de retour de tailles différentes. Veillez à ce qu’elles aient la même longueur.
- Espaces cachés et nombres stockés sous forme de texte. « 100 » et 100 sont différents ; la recherche échoue donc et affiche #N/A. Nettoyez les données avec SUPPRESPACE, ou convertissez les nombres.
- Masquer les problèmes derrière un message convivial de valeur introuvable. Testez une valeur connue avant de vous y fier.
- Pointer la plage de retour vers des cellules qui doivent accueillir un résultat déversé, et les laisser occupées.
- Références à des colonnes entières sur de très grandes feuilles. Utilisez une plage délimitée ou un tableau Excel, afin qu’Excel parcoure moins de cellules.
Sujets connexes à explorer
- XLOOKUP versus INDEX et EQUIV. Les deux permettent des recherches vers la gauche, et le couple INDEX et EQUIV fonctionne aussi dans les anciennes versions d’Excel. Documentez-vous si vous partagez des fichiers avec des utilisateurs d’Excel 2016 ou 2019.
- Résultats multiples. XLOOKUP ne renvoie que la première correspondance. Pour obtenir toutes les correspondances, utilisez plutôt la fonction FILTER.
- XLOOKUP avec des tableaux Excel. Les références structurées permettent aux plages de s’agrandir avec les données, et elles restent lisibles dans les formules.
- Données issues de Power Query. Une fois les données chargées dans un tableau, XLOOKUP peut y effectuer des recherches comme dans n’importe quel autre tableau. Actualisez d’abord la requête, puis vérifiez les résultats.
- Excel pour le web. La page Microsoft consacrée à XLOOKUP ne le mentionne pas. Avant de partager un fichier, vérifiez dans votre propre environnement que la fonction fonctionne pour vos collègues.
Prochaine étape
Prenez un VLOOKUP de votre classeur actuel et réécrivez-le avec XLOOKUP. Ajoutez un message affiché en cas d’absence de résultat, puis comparez les deux résultats sur quelques identifiants que vous connaissez. Si les deux concordent, vous disposez d’un modèle réutilisable pour chaque nouveau XLOOKUP dans Excel.