Comment utiliser XLOOKUP dans Google Sheets (avec des exemples)

Selon Exploding Topics, plus de 900 millions de personnes utilisent Google Sheets chaque mois. Mais combien d'entre elles utilisent XLOOKUP dans Google Sheets ?

Ce tutoriel vous montrera comment l'utiliser pour tirer des données à partir de la même feuille de calcul ou de deux feuilles de calcul différentes.

Il présente aussi le seul cas où FILTER fonctionne mieux que XLOOKUP : renvoyer toutes les correspondances.

Dans ce guide :

• Ce que fait XLOOKUP et ses six arguments
• Comment utiliser XLOOKUP, étape par étape
• XLOOKUP depuis une autre feuille
• XLOOKUP entre deux feuilles de calcul
• Exemples : recherches horizontales, critères multiples, caractères génériques, plages nommées, valeurs par défaut, FILTER, et dernière correspondance ou correspondance la plus proche
• XLOOKUP ou VLOOKUP

Qu'est-ce que XLOOKUP dans Google Sheets ?

En termes simples, XLOOKUP vous aide à trouver des informations spécifiques dans vos feuilles de calcul Google. Vous indiquez à XLOOKUP ce qu'il doit rechercher (la note d'un élève, par exemple) et où il doit le faire (quelle colonne de votre carnet de notes). XLOOKUP trouve rapidement les informations correspondantes et vous les renvoie.

XLOOKUP est une alternative plus souple aux anciennes fonctions comme VLOOKUP et HLOOKUP.

Syntaxe et arguments de XLOOKUP

XLOOKUP accepte six arguments. Seuls les trois premiers sont obligatoires.

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Excel nomme ces mêmes arguments lookup_value, lookup_array, return_array et if_not_found, et certains exemples ci-dessous utilisent ces noms.

Comment utiliser XLOOKUP dans Google Sheets : étape par étape

Dans cette section, vous apprendrez à utiliser XLOOKUP à la verticale pour trouver une correspondance exacte à partir d'une seule clé de recherche.

1. Ouvrez dans Google Sheets la feuille de calcul qui contient votre tableau.

2. Au lieu de saisir la clé de recherche directement dans la fonction, vous pouvez la saisir dans une cellule et utiliser ensuite la référence de cette cellule.

3. Saisissez la fonction XLOOKUP et sélectionnez la cellule qui contient la clé de recherche comme premier paramètre.

4. Comme deuxième paramètre, sélectionnez la colonne dans laquelle vous voulez chercher la clé de recherche.

5. Comme troisième paramètre, sélectionnez la colonne dont vous voulez obtenir le résultat. Ignorez les paramètres facultatifs pour garder les valeurs par défaut.

6. Fermez la parenthèse et appuyez sur "Entrée". Vous obtenez le résultat.

Comment utiliser XLOOKUP depuis une autre feuille

Supposons que vous soyez enseignant et que vous utilisiez une feuille de calcul Google pour suivre les notes de vos élèves. Nous nommerons la feuille de calcul ’Examens des étudiants“.”

La feuille de calcul comporte maintenant deux feuilles :

• L'un d'eux s'intitule “Étudiants” - Il contient les noms et les numéros d'identification des étudiants, ainsi que les informations suivantes
• L'une d'entre elles, intitulée “Résultats des tests”, contient les numéros d'identification et les résultats des élèves.

- Fiche 1 : Les élèves

- Fiche 2 : Résultats des tests

N'oubliez pas que ces deux feuilles font partie de la même feuille de calcul. Nous verrons plus tard comment utiliser XLOOKUP dans deux feuilles de calcul distinctes.

Vous voulez voir le score de chaque étudiant à côté de son nom dans la feuille “Etudiants”. Choisissons John Doe. 

1. Dans la cellule située sous “Test Score” pour John Doe, utilisez cette formule XLOOKUP :

Décomposons cette formule :

• Valeur de consultation (A2) : Le numéro d'identification que vous recherchez.
• Tableau de consultation (‘Test Scores’!A:A) : Il s'agit de la colonne de la feuille “Résultats des tests” dans laquelle la fonction XLOOKUP doit trouver les numéros d'identification.
• Return Array (‘Test Scores’!B:B) : S'il trouve une correspondance, il renvoie la valeur correspondante du tableau de retour (le score de l'élève).
• S'il n'est pas trouvé (“Non trouvé”) : S'il ne trouve pas de correspondance, il affiche “Non trouvé”.”

En termes simples, voici ce que dit la formule :

2. Appuyez sur la touche “Entrée” de votre clavier ou cliquez n'importe où sur votre feuille. La partition apparaît.

Lecture recommandée : Comment utiliser la fonction XLOOKUP dans Excel ?

Comment utiliser la fonction XLOOKUP dans deux Google Sheets distinctes ?

Pouvez-vous utiliser la fonction XLOOKUP dans deux Google Sheets distinctes ?

Oui. Placez la plage de recherche et la plage de résultats dans IMPORTRANGE pour que XLOOKUP les lise dans l'autre fichier. La première fois que vous connectez les deux fichiers, Google Sheets vous demande d'autoriser l'accès.

Comment cela fonctionne-t-il ?

Utilisez la formule suivante :

Reprenons l'exemple de notre professeur.

Imaginez que vous collaborez avec un autre enseignant sur un projet. Vous avez chacun votre propre Google Sheet contenant des données sur les élèves.

Votre feuille Google s'appelle “Étudiants” et contient les noms et les numéros d'identification des étudiants.

En revanche, la feuille de votre collègue s'intitule “Résultats des tests” et contient les numéros d'identification des élèves et leurs résultats à un test récent.

Vous voulez combiner ces informations dans votre feuille “Étudiants” pour voir le score de chaque étudiant à côté de son nom.

Comment procéder ?

1. Ouvrez la feuille de votre collègue et copiez l'ID de l'URL. Il commence après ‘/d/’ et se termine avant le prochain “/”.”

2. Remplacez les deux “sheet-id” dans la formule ci-dessous par l'ID que vous venez de copier.

3. Copiez le nom de la feuille de calcul de votre collègue.

4. Remplacez les deux “Feuille1” de la formule par le nom de la feuille de votre collègue, “Résultats des tests”. Voici à quoi ressemblera votre formule finale :

5. Dans la cellule sous "Test Score" pour Jane Smith, utilisez la formule XLOOKUP ci-dessus.

6. Appuyez sur la touche “Entrée” de votre clavier ou cliquez n'importe où sur votre feuille. La partition apparaît.

7. Pour obtenir le score de Jane Smith en ligne 3, remplacez "=XLOOKUP(A2" par "=XLOOKUP(A3".

Ancienne formule avec (A2 :

Nouvelle formule avec (A3 :

Exemples de XLOOKUP dans Google Sheets

Maintenant que vous savez utiliser XLOOKUP pour trouver une correspondance exacte pour une seule valeur, voyons d'autres façons de l'utiliser. Dans les exemples ci-dessous, vous apprendrez à utiliser XLOOKUP pour effectuer des recherches horizontales, chercher avec plusieurs critères, renvoyer plusieurs colonnes à la fois, chercher avec des caractères génériques et utiliser des plages nommées. Les derniers exemples montrent une valeur par défaut en l'absence de correspondance, FILTER pour renvoyer toutes les correspondances, et comment trouver la dernière correspondance ou la plus proche.

XLOOKUP horizontal (à la place de HLOOKUP)

XLOOKUP cherche aussi dans une ligne, vous pouvez donc l'utiliser à la place de HLOOKUP. Suivez les étapes ci-dessous pour effectuer une recherche horizontale.

1. Dans une cellule vide, saisissez la formule XLOOKUP et ajoutez la clé de recherche comme premier paramètre. Ici, la clé de recherche est l'en-tête de colonne que vous voulez trouver.

2. Comme deuxième paramètre, sélectionnez la ligne dans laquelle vous voulez chercher la clé de recherche. Ici, les en-têtes se trouvent dans la ligne 1.

3. Comme troisième paramètre, indiquez la ligne dont vous voulez obtenir le résultat. Ignorez les paramètres facultatifs pour garder les valeurs par défaut.

4. Fermez la parenthèse et appuyez sur "Entrée" pour obtenir le résultat.

XLOOKUP avec plusieurs critères

Pour chercher plusieurs valeurs avec XLOOKUP, vous pouvez la combiner avec ARRAYFORMULA.

1. Dans une cellule vide, saisissez la fonction XLOOKUP.

2. Comme premier paramètre, sélectionnez la première clé de recherche, ajoutez une esperluette (&) et sélectionnez la deuxième clé de recherche.

3. Comme deuxième paramètre, saisissez la fonction ARRAYFORMULA et sélectionnez les colonnes dans lesquelles chercher les clés de recherche, reliées par une esperluette (&).

4. Comme troisième paramètre, sélectionnez la colonne dont vous voulez renvoyer le résultat.

5. Fermez la parenthèse et appuyez sur "Entrée" pour obtenir le résultat.

Renvoyez plusieurs colonnes avec une seule XLOOKUP

Pour renvoyer plusieurs valeurs de la ligne correspondante, comme l'e-mail, le produit et le montant, vous pouvez utiliser la fonction ARRAYFORMULA. XLOOKUP ne renvoie toujours que la première correspondance. Pour obtenir toutes les lignes correspondantes, utilisez FILTER, comme indiqué plus bas.

1. Dans une cellule vide, saisissez la fonction XLOOKUP et ajoutez la clé de recherche comme premier paramètre.

2. Comme deuxième paramètre, sélectionnez la colonne dans laquelle vous voulez chercher la clé de recherche.

3. Comme troisième paramètre, ajoutez la fonction ARRAYFORMULA et sélectionnez les colonnes dont vous voulez obtenir les résultats.

4. Fermez les deux parenthèses et appuyez sur "Entrée" pour voir les résultats.

XLOOKUP avec des caractères génériques

Si vous n'avez ou ne vous rappelez que des bribes de votre clé de recherche, vous pouvez utiliser des caractères génériques pour trouver une correspondance.

• * correspond à zéro caractère ou plus.
• ? correspond à exactement un caractère.
• ~ neutralise les caractères génériques pour que vous puissiez les rechercher.

1. Saisissez la clé de recherche partielle dans une cellule pour y faire référence ensuite.

2. Dans une cellule vide, saisissez la fonction XLOOKUP.

3. Ajoutez le caractère générique et la clé de recherche partielle, comme indiqué ci-dessous. Le caractère générique doit être placé entre guillemets et relié à la clé de recherche partielle par une esperluette (&).

4. Comme deuxième paramètre, sélectionnez la colonne dans laquelle vous voulez chercher la correspondance.

5. Comme troisième paramètre, sélectionnez la colonne dont vous voulez obtenir le résultat.

6. Comme vous cherchez avec un caractère générique, vous devez indiquer que le cinquième paramètre est "2". En revanche, inutile de préciser le quatrième et le sixième paramètres si vous voulez les valeurs par défaut.

XLOOKUP avec des plages nommées

Enfin, voyons comment utiliser des plages nommées dans XLOOKUP pour simplifier encore le processus.

1. Ouvrez la feuille de calcul qui contient votre tableau et allez dans Données > Plages nommées.

2. Dans le panneau latéral, cliquez sur "Ajouter une plage".

3. Sélectionnez la colonne à nommer et saisissez le nom. Cliquez sur "OK".

4. Répétez les étapes 2 et 3 jusqu'à avoir nommé toutes les colonnes nécessaires.

5. Dans une cellule vide, saisissez la fonction XLOOKUP et ajoutez la clé de recherche.

6. Comme deuxième paramètre, commencez à saisir le nom de la colonne dans laquelle chercher et sélectionnez-la dans la liste déroulante.

7. Comme troisième paramètre, commencez à saisir le nom de la colonne à renvoyer et sélectionnez-la comme précédemment.

8. Fermez la parenthèse et appuyez sur "Entrée" pour voir le résultat.

Afficher une valeur par défaut en l'absence de correspondance

Vous pouvez utiliser XLOOKUP dans Google Sheets pour trouver les valeurs manquantes d'un jeu de données en comparant deux listes (par exemple, une liste de produits existants et une liste de nouveaux produits).

Prenons un exemple.

Vous êtes directeur commercial et vous suivez les performances de vos employés dans une feuille Google. Vous avez :

• Feuille 1 (données sur les employés) : Contient les noms et les numéros d'identification des employés.

• Feuille 2 : contient les numéros d'identification des employés et leurs chiffres d'affaires.

• Feuille 3 : Contient le informations consolidées. Cependant, certains employés n'ont pas de données sur les ventes, comme le montre le tableau ci-dessous.

Gardez à l'esprit que ces trois feuilles se trouvent dans la même feuille de calcul.

1. Ouvrez la feuille 3 (“consolidée” dans notre exemple).
2. Dans la cellule C2 de Consolidated, saisissez la formule XLOOKUP suivante :

Cette formule permet :

1. Recherchez l'ID de l'employé dans B2 dans la plage d'ID d'employés de la feuille 2 (Sheet2!A:A).
2. S'il est trouvé, il renvoie le chiffre d'affaires correspondant de la feuille 2 (Sheet2!B:B).
3. S'il n'est pas trouvé, il renvoie la valeur 0, ce qui indique qu'il n'y a pas de ventes.

N'oubliez pas que vous devez modifier la formule avant de l'utiliser. Par exemple, ma feuille s'appelle “Ventes” et non “Feuil2” et l'ID “102” se trouve à la ligne B3 et non à la ligne B2. Ma nouvelle formule sera donc la suivante :

Utilisez-la dans la cellule et appuyez sur Entrée.

Renvoyer toutes les correspondances avec FILTER

On nous demande souvent si XLOOKUP peut renvoyer plusieurs résultats dans Google Sheets.

Le meilleur moyen de répondre à cette question est de donner un exemple.

Supposons que vous gériez une petite librairie et que vous enregistriez vos stocks dans une feuille Google. Votre feuille comporte les colonnes suivantes :

• Titre du livre
• Auteur
• ISBN
• Quantité en stock
• Prix

Vous souhaitez trouver tous les livres écrits par un auteur spécifique, ainsi que leur ISBN et les quantités en stock.

1. Identifiez votre valeur de recherche. Dans cet exemple, je choisirai le nom d'un auteur puisque c'est la valeur que je veux trouver : J.K. Rowling.
2. Identifie le tableau de recherche, la colonne contenant les noms des auteurs : Auteur.
3. Identifiez les tableaux de retour, les colonnes à partir desquelles vous souhaitez renvoyer des données :
   • ISBN
   • Quantité en stock
4. Créez la formule XLOOKUP :

N'oubliez pas que vous devez modifier cette formule pour qu'elle corresponde à votre scénario.

5. Ouvrez votre feuille et utilisez votre formule dans une cellule vide.

6. Appuyez sur "Entrée". Comme vous le voyez, XLOOKUP ne renvoie que le premier livre correspondant, même si la formule vérifie toutes les lignes.

XLOOKUP peut-elle renvoyer toutes les lignes correspondantes dans Google Sheets ?

Pas vraiment. XLOOKUP renvoie généralement un unique ou la première correspondance trouvée. Malheureusement, il ne permet pas de renvoyer des correspondances multiples comme le fait FILTER. Ainsi, dans sa forme standard, XLOOKUP n'est pas adapté à l'extraction de plusieurs lignes.

Avez-vous remarqué que j'ai parlé de FILTRE ? Comment fonctionne-t-il ?

7. Changez votre formule en :

8. Appuyez sur "Entrée". Et voilà : FILTER a extrait les données de plusieurs lignes.

Trouver la dernière correspondance ou la valeur la plus proche

Les deux derniers arguments modifient la façon dont XLOOKUP cherche. Ces exemples reprennent le tableau des ventes de la section étape par étape, où la colonne F contient le montant et la colonne G le commercial.

Pour obtenir le montant de la dernière vente de la liste pour Agent2, définissez search_mode sur -1 pour que XLOOKUP cherche de bas en haut :

=XLOOKUP("Agent2", G:G, F:F, "Not found", 0, -1)

Pour trouver la valeur la plus proche plutôt qu'une valeur exacte, définissez match_mode sur -1 (valeur immédiatement inférieure) ou 1 (valeur immédiatement supérieure). Supposons que K2:K5 contienne des seuils de ventes et L2:L5 le taux de commission de chacun. Cette formule renvoie le taux du seuil le plus élevé qui ne dépasse pas le montant en F2 :

=XLOOKUP(F2, K2:K5, L2:L5, , -1)

Aucune de ces formules n'a besoin de données triées. Seuls les modes de recherche dichotomique, 2 et -2, en ont besoin.

XLOOKUP ou VLOOKUP : quelle est la différence ?

VLOOKUP effectue une recherche verticale, de gauche à droite, dans un tableau (d'où le “V”) ; la valeur recherchée doit se trouver dans la première colonne. XLOOKUP est toutefois plus souple. Il peut effectuer des recherches verticales et horizontales ; il n'est pas nécessaire de spécifier un indice de colonne.

De plus, VLOOKUP ne peut renvoyer que des valeurs situées à droite de la colonne de recherche, alors que XLOOKUP peut renvoyer des valeurs des deux côtés.

Voici la différence en termes de syntaxe :

VLOOKUP

XLOOKUP

Conclusion

Comme vous l'avez vu, la fonction XLOOKUP de Google Sheets est plus puissante que VLOOKUP et HLOOKUP, et plus simple à utiliser que des alternatives comme INDEX & MATCH. Vous avez passé en revue la fonction et sa syntaxe, et vous savez maintenant utiliser XLOOKUP pour effectuer des recherches verticales et horizontales d'une seule correspondance exacte. Vous savez aussi extraire des données d'une autre feuille ou d'un autre fichier, chercher avec plusieurs critères ou des caractères génériques, et renvoyer plusieurs colonnes à la fois. Quand une recherche ne trouve rien, missing_value vous donne une valeur par défaut, et quand vous avez besoin de toutes les correspondances, FILTER fait ce que XLOOKUP ne peut pas faire.

Si vous répétez les mêmes recherches dans plusieurs fichiers chaque semaine, Sheetgo, une plateforme d'automatisation des données basée sur l'IA, peut connecter ces feuilles de calcul et tenir les données combinées à jour selon un calendrier, sans formules IMPORTRANGE à entretenir.