Selon le Sujets explosifs, Selon les estimations de Google, plus de 900 millions de personnes utilisent Google Sheets chaque mois. Mais combien 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.
Nous discuterons également de la meilleure et de la plus simple des alternatives à XLOOKUP.
Poursuivez votre lecture !
Qu'est-ce que XLOOKUP ?
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 anciens systèmes fonctions comme VLOOKUP et HLOOKUP.
Comment utiliser XLOOKUP dans Google Sheets ?
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.
- Dans la cellule située sous “Test Score” pour John Doe, utilisez cette formule XLOOKUP :
=XLOOKUP(A2, ‘Test Scores’!A:A, ‘Test Scores’!B:B, “Not Found”)
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 :
Trouvez le numéro d'identification de cet élève sur la feuille des résultats des tests. Si vous le trouvez, dites-moi son score. Si vous ne le trouvez pas, dites simplement ‘Non trouvé’.
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, vous devez vous assurer que les deux feuilles sont ouvertes dans votre navigateur et que vous faites correctement référence à l'autre feuille dans votre formule XLOOKUP.
Comment cela fonctionne-t-il ?
Utilisez la formule suivante :
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/sheet-id”, “Sheet1!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/sheet-id”, “Sheet1!B:B”), “Not Found”)
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 ?
- 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 “/”.”
- Remplacez les deux “sheet-id” dans la formule ci-dessous par l'ID que vous venez de copier.
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0“, “Sheet1!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0“, “Sheet1!B:B”), “Not Found”)
- 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 :
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Résultats des tests!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Résultats des tests!B:B”), “Non trouvé”)
5. Dans la cellule sous “Test Score” pour Jane Smith, utiliser 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. Si vous souhaitez obtenir Score de Jane Smith à la ligne 3, changer “=XLOOKUP(A2)” à “=XLOOKUP(A3.”
Ancienne formule avec (A2 :
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Test Scores!B:B”), “Not Found”)
Nouvelle formule avec (A3 :
=XLOOKUP(A3, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Résultats des tests!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Résultats des tests!B:B”), “Non trouvé”)
Exemples de XLOOKUP en action
Exemple 1 : Valeur manquante
Vous pouvez utiliser XLOOKUP dans Google Sheets pour trouver les valeurs manquantes dans un ensemble 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.
- Ouvrez la feuille 3 (“consolidée” dans notre exemple).
- Dans la cellule C2 de Consolidated, saisissez la formule XLOOKUP suivante :
=XLOOKUP(B2, Sheet2!A:A, Sheet2!B:B, 0)
Cette formule permet :
- Recherchez l'ID de l'employé dans B2 dans la plage d'ID d'employés de la feuille 2 (Sheet2!A:A).
- S'il est trouvé, il renvoie le chiffre d'affaires correspondant de la feuille 2 (Sheet2!B:B).
- 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 :
=XLOOKUP(B3, Ventes!A:A, Ventes!B:B, 0)
Utilisez dans la cellule et appuyez sur la touche Entrée de votre clavier.
Exemple 2 : Renvoi de plusieurs résultats
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.
- 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.
- Identifie le tableau de recherche, la colonne contenant les noms des auteurs : Auteur.
- Identifiez les tableaux de retour, les colonnes à partir desquelles vous souhaitez renvoyer des données :
- ISBN
- Quantité en stock
- Créez la formule XLOOKUP :
=ARRAYFORMULA(XLOOKUP(lookup_value, lookup_array, {return_array1, return_array2}, “”, 0))
N'oubliez pas que vous devez modifier cette formule pour qu'elle corresponde à votre scénario.
=ARRAYFORMULA(XLOOKUP(“J.K. Rowling”, B2:B100, {C2:C100, D2:D100}, “”, 0))
5. Ouvrez votre feuille et utilisez votre formule dans une cellule vide.
6. Appuyez sur “Entrée”. Comme vous pouvez le constater, XLOOKUP ne renvoie qu'une seule valeur, bien que notre formule demande explicitement que toutes les lignes soient vérifiées.
7. Néanmoins, il y a encore un petit moyen de récupérer toutes les données. Cliquez sur la première donnée retournée.
Et faites-le glisser vers le bas. D'autres données apparaîtront, bien qu'elles soient souvent répétées.
Pouvez-vous utiliser XLOOKUP pour renvoyer plusieurs résultats 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 ?
- Changez votre formule en :
=ARRAYFORMULA(FILTER({C2:C100, D2:D100}, B2:B100 = “J.K. Rowling”))
9. Appuyez sur “Enter”. Et voilà, FILTER a extrait des données de plusieurs lignes.
XLOOKUP vs 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.
Voici la différence en termes de syntaxe :
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
XLOOKUP
=XLOOKUP(valeur_du_regard, tableau_du_regard, tableau_du_retour, [if_not_found], [match_mode], [search_mode])
Utiliser XLOOKUP dans Google Sheets
Nous venons de voir comment utiliser XLOOKUP dans Google Sheets à l'aide d'exemples. Nous avons également partagé des formules brutes et expliqué comment les utiliser.
English 
Español 
Português do Brasil 
Français