La fonction XLOOKUP dans Google Sheets permet de rechercher des éléments dans une plage par ligne ou par colonne. Par exemple, vous pouvez l’utiliser pour rechercher « Mango » dans une colonne et renvoyer son prix à partir d’une autre colonne. Dans un autre exemple, vous pouvez rechercher « Ben » dans une ligne et renvoyer son score à partir d’une autre ligne.
Auparavant, nous utilisions les fonctions VLOOKUP et HLOOKUP pour ces deux opérations. Cependant, ces fonctions ont une limitation : elles ne peuvent rechercher que dans la première colonne (VLOOKUP) ou la première ligne (HLOOKUP) de la plage. XLOOKUP n’a pas cette limitation, il peut donc être utilisé pour rechercher des valeurs dans n’importe quelle colonne ou ligne d’une plage.
Dans XLOOKUP, nous pouvons spécifier si nous voulons une correspondance exacte, la valeur suivante la plus petite, la valeur suivante la plus grande, une correspondance avec un caractère générique, une correspondance depuis la première entrée jusqu’à la dernière entrée, ou une correspondance depuis la dernière entrée jusqu’à la première entrée, etc.
Par exemple, la formule XLOOKUP suivante recherchera « Wheat » dans la colonne A et renverra la valeur de la colonne C à partir de la position trouvée.
=XLOOKUP("Wheat",A:A,C:C,"Non disponible")
Syntaxe de la fonction XLOOKUP dans Google Sheets
La syntaxe de la fonction XLOOKUP dans Google Sheets est la suivante :
XLOOKUP(valeur_recherche, plage_recherche, plage_resultat, [valeur_manquante], [mode_correspondance], [mode_recherche])
Contrairement à d’autres fonctions de Google Sheets, nous devons accorder plus d’attention aux arguments de la fonction XLOOKUP. C’est parce qu’elle a six arguments, ce qui est plus que la plupart des autres fonctions.
J’ai déplacé les explications des arguments sous deux sous-titres (catégories) ci-dessous :
- Arguments requis de la fonction XLOOKUP : valeur_recherche, plage_recherche et plage_resultat.
- Arguments optionnels de la fonction XLOOKUP : valeur_manquante, mode_correspondance et mode_recherche.
Avec une compréhension de base de ces arguments, nous serons en mesure d’utiliser la fonction XLOOKUP facilement dans nos feuilles de calcul.
Arguments requis dans la fonction XLOOKUP
Les trois premiers arguments de la fonction XLOOKUP sont requis. Vous trouverez ci-dessous leur objectif et quelques exemples de formules XLOOKUP les utilisant.
Les trois arguments de base sont :
- valeur_recherche : La valeur à rechercher. Par exemple, 156, « Apple », Date(2022,09,20) ou « ABC100 ».
- plage_recherche : La plage à prendre en compte pour la recherche (une seule colonne pour une recherche verticale et une seule ligne pour une recherche horizontale).
- plage_resultat : La plage à prendre en compte pour le résultat.
Puisque nous ne spécifions pas les arguments optionnels dans la syntaxe de la fonction XLOOKUP, la formule prendra leurs valeurs par défaut. Cela signifie :
- Si la valeur_recherche n’est pas disponible, la formule XLOOKUP renverra la valeur d’erreur #N/A.
- Elle effectuera une correspondance exacte de la valeur_recherche ; pas une correspondance pour la valeur suivante la plus petite ou la plus grande.
- Elle effectuera une recherche de la première entrée à la dernière entrée dans la plage_recherche, de haut en bas dans l’XLOOKUP vertical et de gauche à droite dans l’XLOOKUP horizontal.
Nous utiliserons les données suivantes (veuillez vous référer à la Figure 1 ci-dessous) pour tester la fonction XLOOKUP avec les trois arguments requis.
Figure 1
Avant de continuer, si vous n’êtes pas familier avec l’utilisation des critères de date, d’heure, de texte, de nombre ou de date/heure dans XLOOKUP, veuillez consulter mon tutoriel sur HLOOKUP. J’ai un tableau juste en dessous de la partie syntaxe que vous pouvez consulter.
Exemples de formules
- Formule F4 :
=XLOOKUP(G2,B2:B7,D2:D7)
La fonction XLOOKUP recherche la valeur « barley » (G2) dans la plage B2:B7 (la plage de recherche) et renvoie la valeur de la plage D2:D7 (la plage de résultat) dans la ligne correspondante. Si nous considérons les données ci-dessus comme la production de grains alimentaires en 2021 et 2022, la formule renvoie la quantité de production de « barley » en 2022.
- Formule F6 :
=XLOOKUP(G2,B2:B7,C2:D7)
La plage_resultat comprend deux colonnes, donc la formule XLOOKUP renverra les valeurs des deux colonnes. Il s’agit d’un exemple d’utilisation d’un tableau 2D dans la plage de résultat de la fonction XLOOKUP dans Google Sheets. Le résultat de la formule sera la quantité de production de « barley » en 2021 et 2022, dans une seule ligne.
- Formule F12 :
=ARRAYFORMULA(XLOOKUP(G9:G10,B2:B7,D2:D7))
Voici un exemple d’utilisation de plusieurs clés de recherche dans la fonction XLOOKUP dans Google Sheets. La formule recherche les valeurs « barley » et « rye » (G9:G10) dans la plage B2:B7 (la plage de recherche) et renvoie les valeurs de la plage D2:D7 (la plage de résultat) dans les lignes correspondantes. La formule renvoie les quantités de production de « barley » et « rye » en 2022.
Lorsque vous utilisez plusieurs clés de recherche dans la fonction XLOOKUP, vous devez utiliser la fonction ARRAYFORMULA avec elle. Cela indique à Google Sheets de renvoyer un tableau de valeurs, ce qui est ce dont vous avez besoin lorsque vous utilisez plusieurs clés de recherche dans la fonction XLOOKUP.
- Formule F15 :
=ARRAYFORMULA(XLOOKUP(G9:G10,B2:B7,C2:D7))
Ici, la plage_resultat de XLOOKUP comprend deux colonnes. Cependant, comme il y a plus d’une clé de recherche, la formule ne renverra des valeurs que de la première colonne. C’est parce que la fonction XLOOKUP n’est pas capable de renvoyer des tableaux 2D.
Nous pouvons résoudre le problème du non-renvoi d’un tableau 2D par la fonction XLOOKUP en utilisant une astuce qui garde toutes les fonctionnalités de XLOOKUP. Cette astuce consiste à utiliser la fonction CHOOSEROWS avec la fonction XMATCH. Vous pouvez trouver un tutoriel sur la façon de faire cela sur Crawlan.com.
Veuillez comprendre cela complètement avant de passer aux arguments optionnels ci-dessous.
Arguments optionnels dans la fonction XLOOKUP
Les trois arguments optionnels de la fonction XLOOKUP sont :
- valeur_manquante : La valeur à renvoyer si aucune correspondance de valeur_recherche n’est trouvée.
- mode_correspondance : Le type de correspondance à effectuer dans la plage_recherche.
- mode_recherche : La manière de rechercher dans la plage_recherche.
Voici comment les utiliser dans les formules XLOOKUP :
Valeur_manquante et exemples
valeur_manquante est un argument optionnel de la fonction XLOOKUP dans Google Sheets. Si cet argument est omis, la fonction renverra par défaut la valeur d’erreur #N/A si aucune correspondance n’est trouvée.
La formule XLOOKUP suivante renverra « Il semble qu’il y ait une faute de frappe ! » si la valeur_recherche n’est pas trouvée dans la plage B2:B7.
=XLOOKUP(G2,B2:B7,D2:D7,"Il semble qu'il y ait une faute de frappe !")
J’ai trois clés de recherche et l’une d’entre elles ne correspond pas. Que se passe-t-il avec la valeur_manquante dans ce scénario ?
Veuillez consulter l’image ci-dessous.
Figure 2
Modes de correspondance dans la fonction XLOOKUP dans Google Sheets
Le mode_correspondance dans la fonction XLOOKUP détermine comment trouver une correspondance pour la valeur_recherche, qui est la valeur que vous recherchez.
- 0 : Correspondance exacte (par défaut).
- 1 : Correspondance exacte ou la valeur suivante qui est plus grande que la valeur_recherche.
- -1 : Correspondance exacte ou la valeur suivante qui est inférieure à la valeur_recherche.
- 2 : Correspondance avec un caractère générique.
Exemples de formules
Dans les exemples suivants, nous avons les dates d’entrevue dans la colonne B et les noms des candidats dans la colonne C.
Utilisons la fonction XLOOKUP pour trouver le candidat à une date particulière. Si aucun candidat n’est disponible à cette date, renvoyez le candidat à la date précédente ou suivante.
Formule :
=XLOOKUP(D3,B3:B7,C3:C7,"Aucun rendez-vous",1)
Figure 3
Le 22/09/2021 (cellule D3) est la date à rechercher dans la colonne B. Comme vous pouvez le voir, elle est disponible dans la colonne B. Par conséquent, quel que soit le mode_correspondance utilisé, qu’il s’agisse de 0 (exact), 1 (plus grand), -1 (plus petit) ou 2 (caractère générique), la formule renverra le nom « Erik ».
Changez la valeur_recherche à 24/09/2021. La formule de correspondance exacte suivante renverra « Aucun rendez-vous ».
=XLOOKUP(D3,B3:B7,C3:C7,"Aucun rendez-vous",0)
Si vous changez le paramètre mode_correspondance de la fonction XLOOKUP à 1, la sortie sera « Russel », et -1 la fera renvoyer « Ben ».
Comment fonctionne la correspondance avec un caractère générique dans la fonction XLOOKUP dans Google Sheets ?
La fonction XLOOKUP prend en charge trois caractères génériques : un astérisque (*), un point d’interrogation (?) et un tilde (~).
Beaucoup de gens pensent à tort que XLOOKUP ne prend en charge que deux caractères génériques : l’astérisque et le point d’interrogation. Voici comment utiliser le tilde dans la fonction XLOOKUP dans Google Sheets.
Modes de recherche dans la fonction XLOOKUP dans Google Sheets
L’argument mode_recherche dans la fonction XLOOKUP détermine comment rechercher dans la plage_recherche, qui est la plage de cellules où la fonction XLOOKUP recherche la valeur_recherche.
Voici les 4 modes de recherche.
- 1 : Recherche de la première entrée à la dernière entrée (par défaut).
- -1 : Recherche de la dernière entrée à la première entrée.
- 2 : Recherche binaire (la plage_recherche doit être triée par ordre croissant).
- -2 : Recherche binaire (la plage_recherche doit être triée par ordre décroissant).
Exemples de formules
Parmi les quatre modes de recherche, 1 et -1 sont les plus utiles lorsque vous avez plusieurs occurrences de clés de recherche dans la plage de recherche.
Les modes de recherche binaire 2 et -2 dans la fonction XLOOKUP sont la partie la plus confuse. Vous pouvez le comprendre à l’aide du tableau ci-dessous.
Note : Triez d’abord les données par ordre croissant (A-Z) ou décroissant (Z-A). Ensuite, utilisez les modes de recherche 2 ou -2.
Essayons de comprendre ce que « le plus proche de la valeur_recherche » signifie dans la fonction XLOOKUP dans Google Sheets.
Tableau : A1:B4 (Ordre croissant [A-Z])
- 23/9/21 | Ben
- 23/9/21 | Russell
- 26/9/21 | Andrew
- 26/9/21 | Charles
Si la valeur_recherche est le 24/09/2021 en mode de recherche binaire 2, la date la plus proche de la valeur_recherche sera le 23/09/2021 lorsque le mode_correspondance est -1 (plus petit), ou le 26/09/2021 lorsque le mode_correspondance est 1 (plus grand).
Exemple 1 :
=XLOOKUP(DATE(2021,9,24),A1:A4,B1:B4,"Non disponible",-1,2)
Résultat : « Russel »
Exemple 2 :
=XLOOKUP(DATE(2021,9,24),A1:A4,B1:B4,"Non disponible",1,2)
Résultat : « Andrew »
Tableau : A1:B4 (Ordre décroissant [Z-A])
- 26/9/21 | Charles
- 26/9/21 | Andrew
- 23/9/21 | Russel
- 23/9/21 | Ben
Veuillez consulter les dates en gras pour la correspondance la plus proche avec la valeur_recherche du 24/09/2021. Le mode de recherche binaire est -2.
XLOOKUP horizontal dans Google Sheets
La fonction XLOOKUP dans Google Sheets fonctionne aussi bien avec des plages de recherche et de résultat verticales qu’horizontales. Voici un exemple qui résume mes explications précédentes, mais qui utilise des données horizontales.
Supposons que la première ligne d’un tableau contienne l’échelle temporelle d’un planning de projet. Dans les lignes suivantes, j’ai des affectations de main-d’œuvre. Voyons comment récupérer la main-d’œuvre allouée à une date particulière.
La valeur_recherche est le 15/07/2023 dans la cellule A10. Nous chercherons cette date dans B2:E2 et renverrons la main-d’œuvre allouée pour la « tâche 3 » de B5:E5. Si la valeur_recherche n’est pas disponible, nous récupérerons la valeur de la date la plus proche.
Comme nous avons une plage de recherche triée, nous pouvons utiliser soit le mode de recherche binaire 2, soit le mode de recherche 1. Bien sûr, le mode_correspondance est 1, puisque nous voulons effectuer une correspondance exacte ou la prochaine date la plus grande.
Formules XLOOKUP horizontales :
=XLOOKUP(A10,B2:E2,B5:E5,"",1,2)=XLOOKUP(A10,B2:E2,B5:E5,"",1,1)
Ressources
Je pense avoir couvert tout ce qu’un utilisateur de feuille de calcul doit savoir pour maîtriser la fonction XLOOKUP, la solution ultime de recherche dans Google Sheets. Voici quelques ressources supplémentaires.
- XLOOKUP Visible (Filtrage) des données dans Google Sheets.
- XLOOKUP Nth Valeur de correspondance dans Google Sheets.
- Fonction XLOOKUP imbriquée dans Google Sheets.
- Différences clés entre VLOOKUP et XLOOKUP dans Google Sheets.
- XLOOKUP avec plusieurs critères dans Google Sheets.
- XLOOKUP pour plusieurs colonnes de résultats dans Google Sheets.
Vous pouvez également utiliser la fonction FILTER ou la fonction QUERY pour filtrer les lignes ou les colonnes correspondant à une ou plusieurs valeurs de recherche.
