La fonction XLOOKUP dans Google Sheets est une nouvelle fonction de recherche dans Google Sheets qui est plus puissante et flexible que les anciennes fonctions de recherche telles que VLOOKUP ou HLOOKUP.
XLOOKUP recherche une clé de recherche dans une plage de recherche et renvoie la valeur de la plage de résultat à la même position. Si XLOOKUP ne trouve pas de correspondance, vous pouvez spécifier une valeur par défaut. Vous pouvez contrôler le mode de correspondance, comme d’autres fonctions de recherche, et même contrôler le mode de recherche. Nous en parlerons plus en détail ci-dessous, mais d’abord, voyons un exemple simple.
Voici une formule XLOOKUP simple qui recherche la clé de recherche dans la colonne A et renvoie une valeur de la colonne C :
=XLOOKUP(A2, A:C, C:C)Ça ressemble à ça dans la feuille :
🔗 Retrouvez cet exemple et d’autres dans le modèle en bas de cet article.
Syntaxe de la fonction XLOOKUP
Elle prend un minimum de trois et un maximum de six arguments :
clé_de_recherche: la valeur que vous voulez rechercher.plage_de_recherche: la plage dans laquelle rechercher. Elle doit être soit une seule colonne, soit une seule ligne.plage_de_résultat: la plage à prendre en compte pour le résultat. La valeur de retour est prise à la position de la valeur correspondante dans le tableau de recherche si la clé de recherche est trouvée. La plage de résultat doit avoir les mêmes dimensions que la plage de recherche.[valeur_manquante]: la valeur de remplacement à renvoyer si aucune correspondance n’existe. Il s’agit d’un argument facultatif et s’il est omis, une erreur est renvoyée s’il n’y a pas de correspondance.[mode_correspondance]: cet argument facultatif vous permet de spécifier le mode de correspondance à utiliser. S’il n’est pas spécifié, une correspondance exacte est utilisée.
Les options sont les suivantes :
| Option | Comportement du mode de correspondance |
|---|---|
| 0 | Recherche exacte |
| 1 | Correspondance exacte ou prochaine valeur qui est supérieure à la clé de recherche |
| -1 | Correspondance exacte ou prochaine valeur qui est inférieure à la clé de recherche |
| 2 | Correspondance avec des caractères génériques |
[mode_recherche] : cet argument facultatif vous permet de spécifier le mode de recherche à utiliser. S’il est omis, XLOOKUP recherche par défaut dans la plage de recherche de la première entrée à la dernière entrée.
Les différentes options de recherche sont les suivantes :
| Option | Comportement du mode de recherche |
|---|---|
| 1 | Recherche de la première entrée à la dernière |
| -1 | Recherche de la dernière entrée à la première |
| 2 | Recherche à travers la plage en utilisant la recherche binaire en supposant que la plage est triée par ordre croissant |
| -2 | Recherche à travers la plage en utilisant la recherche binaire en supposant que la plage est triée par ordre décroissant |
Notes sur la fonction XLOOKUP
- La plage de recherche ne peut être qu’une seule colonne ou une seule ligne. Elle ne peut pas être un tableau avec plusieurs lignes et colonnes.
- La plage de résultat doit être compatible avec la taille de la plage de recherche. Par exemple, si la plage de recherche est une colonne de données avec 10 lignes et 1 colonne, alors la plage de résultat doit également avoir 10 lignes (bien qu’elle puisse avoir plus d’une colonne).
Exemples de la fonction XLOOKUP
Voyons quelques exemples supplémentaires de la fonction XLOOKUP dans Google Sheets.
Exemple 1 : Correspondance exacte de base
Si vous omettez l’argument facultatif du mode de correspondance, la fonction XLOOKUP effectuera une correspondance exacte.
C’est-à-dire que lorsque vous l’écrivez avec seulement les trois premiers arguments, une clé de recherche, une plage de recherche et une plage de résultat, elle recherchera une correspondance exacte. Nous avons vu cela dans l’exemple en haut de cette page :
=XLOOKUP(A2, A:A, C:C)Ce qui fonctionne comme cela dans la feuille :
Exemple 2 : Valeur manquante
Maintenant, nous pouvons spécifier une valeur de remplacement si aucune correspondance n’est trouvée. Cela se fait avec le quatrième argument (facultatif), par exemple :
=XLOOKUP(A2, A:A, C:C, "Valeur manquante")Dans notre feuille :
Dans ce cas, la clé de recherche « XYZ123 » n’est pas trouvée dans le tableau de recherche (colonne A), donc la fonction XLOOKUP renvoie la valeur de remplacement manquante, que nous avons définie sur « Valeur manquante ».
Exemple 3 : Fonction XLOOKUP à gauche
Un autre avantage de la fonction XLOOKUP est que la plage de recherche n’a pas besoin d’être à gauche de la plage de résultat, ce qui est le cas avec la fonction VLOOKUP (bien qu’il existe une solution compliquée avec les listes de tableaux).
La formule ne change pas, mais cette fois-ci, la plage de résultat est positionnée à gauche de notre plage de recherche :
=XLOOKUP(A2, C:C, A:A)Comme vous pouvez le voir, cela fonctionne tout aussi bien dans notre feuille :
Exemple 4 : Correspondance approchée
Le cinquième argument de la fonction XLOOKUP détermine le mode de correspondance. S’il est omis ou réglé sur 0, une correspondance exacte est effectuée.
Cependant, il y a des situations où l’option de correspondance approximative fonctionne très bien.
Considérons le cas où notre clé de recherche se situe entre deux valeurs de la plage de recherche. Ce n’est pas une correspondance exacte, mais nous voulons peut-être quand même renvoyer un résultat pour dire qu’il est inférieur à X ou supérieur à Y.
Par exemple, considérons ce scénario d’épargne bancaire :
=XLOOKUP(B2, B:B, C:C, "", -1)La formule XLOOKUP pour cet exemple est :
=XLOOKUP(B2, B2:B6, C2:C6, "", -1)Remarquez le -1 comme dernier argument, qui indique à la fonction de rechercher une correspondance exacte et si elle n’en trouve pas, de renvoyer la valeur qui est inférieure dans le tableau.
Dans cet exemple, il ne trouve pas les 137 832 $ exactement, donc il regarde la valeur inférieure dans le tableau, c’est-à-dire 100 000 $. Cela se trouve à la position 3 du tableau de recherche, donc il renvoie la valeur de la 3ème position du tableau des résultats, c’est-à-dire 1,25 %.
Une dernière chose à mentionner avec cet exemple, remarquez comment le quatrième argument est laissé vide. C’est là que nous pouvons spécifier une « valeur manquante » lorsque aucune correspondance n’est trouvée. Cependant, ce n’est pas nécessaire ici car nous utilisons une correspondance approximative de toute façon.
Exemple 5 : Correspondance avec des caractères génériques
XLOOKUP dans Google Sheets prend en charge trois caractères génériques : *, ?, et ~.
L’astérisque * correspond à zéro ou plusieurs caractères.
Le point d’interrogation ? correspond à exactement un caractère.
Le tilde ~ est un caractère d’échappement qui vous permet de rechercher un * ou un ?, au lieu de les utiliser comme caractères génériques.
Voyons un exemple qui utilise un nom de famille pour trouver le nom complet :
=XLOOKUP("*Doe", A:A, B:B)Et un autre exemple qui utilise un nom de famille pour renvoyer le revenu de transaction de cette ligne :
=XLOOKUP("*Doe", A:A, C:C)Les deux formules sont visibles sur l’image suivante, avec la première dans la cellule B17 et la deuxième dans la cellule B18 :
Il y a deux choses importantes à remarquer avec cette formule :
- La clé de recherche est « Doe », mais pour l’utiliser dans la fonction XLOOKUP, nous ajoutons d’abord le caractère générique astérisque qui correspond à n’importe quoi avant le « Doe ».
Cela donne donc *Doe.
Notez que s’il y avait plusieurs « Doe » dans cet ensemble de données, cela pourrait poser un problème. Dans ce cas, vous voudrez peut-être essayer d’utiliser la fonction QUERY ou la fonction FILTER pour renvoyer tous les résultats « Doe ».
- Le mode de correspondance dans le cinquième argument est réglé sur 2, ce qui indique qu’il s’agit d’une recherche avec caractères génériques.
Exemple 6 : Renvoyer plusieurs résultats
La fonction XLOOKUP peut renvoyer plusieurs résultats pour une seule correspondance, pas seulement un seul résultat comme une fonction VLOOKUP (bien qu’il existe une solution de contournement pour renvoyer plusieurs colonnes avec VLOOKUP).
XLOOKUP renvoie plusieurs résultats en spécifiant une plage de résultat avec plusieurs colonnes (ou lignes si vous effectuez une recherche horizontale).
La formule est la suivante :
=XLOOKUP(A2, A:A, B:C)Cela donne le résultat :
Exemple 7 : Mode de recherche différent
Le dernier argument vous permet de modifier la méthode de recherche utilisée. Par défaut, la recherche s’effectue de haut en bas de votre plage, mais vous pouvez changer cela pour rechercher du bas vers le haut si cela a du sens.
XLOOKUP peut également effectuer des recherches binaires super rapides, mais cela nécessite que vos données soient triées correctement pour éviter les résultats incorrects.
Modèle de la fonction XLOOKUP
Cliquez ici pour ouvrir une copie en lecture seule > Crawlan.com
N’hésitez pas à en faire une copie : Fichier > Faire une copie…
Si vous ne pouvez pas accéder au modèle, cela peut être dû aux paramètres de votre compte Google Workspace.
Dans ce cas, faites un clic droit sur le lien pour l’ouvrir dans une fenêtre de navigation privée pour le visualiser.
Il fait partie de la famille des fonctions de recherche dans Google Sheets. Vous pouvez en lire plus à ce sujet dans la documentation Google.
