Fonction Excel XLOOKUP : tout ce que vous devez savoir (10 exemples)

Regarder la vidéo - Fonction Excel XLOOKUP (10 exemples XLOOKUP)

Exceller Fonction XLOOKUP est enfin arrivé.

Si vous avez utilisé VLOOKUP ou INDEX/MATCH, je suis sûr que vous adorerez la flexibilité offerte par la fonction XLOOKUP.

Dans ce tutoriel, je vais couvrir tout ce qu'il y a à savoir sur la fonction XLOOKUP et quelques exemples qui vous aideront à savoir comment l'utiliser au mieux.

Alors, commençons!

Qu'est-ce que XLOOKUP ?

XLOOKUP est une nouvelle fonction d'Office 365 et est une nouvelle version améliorée de la fonction VLOOKUP/HLOOKUP.

Il fait tout ce que VLOOKUP faisait auparavant, et bien plus encore.

XLOOKUP est une fonction qui vous permet de rechercher rapidement une valeur dans un ensemble de données (vertical ou horizontal) et de renvoyer la valeur correspondante dans une autre ligne/colonne.

Par exemple, si vous avez les notes des étudiants à un examen, vous pouvez utiliser XLOOKUP pour vérifier rapidement combien un étudiant a marqué en utilisant le nom de l'étudiant.

La puissance de cette fonction deviendra encore plus claire au fur et à mesure que je plongerai dans certains Exemples XLOOKUP plus tard dans ce tutoriel.

Mais avant d'entrer dans les exemples, il y a une grande question - comment puis-je accéder à XLOOKUP ?

Comment accéder à XLOOKUP ?

Pour l'instant, XLOOKUP n'est disponible que pour les utilisateurs d'Office 365.

Ainsi, si vous utilisez des versions antérieures d'Excel (2010/2013/2016/2019), vous ne pourrez pas utiliser cette fonction.

Je ne sais pas non plus si cela serait un jour publié pour les versions antérieures ou non (peut-être que Microsoft peut créer un complément comme il l'a fait pour Power Query). Mais à partir de maintenant, vous ne pouvez l'utiliser que si vous êtes sur Office 365.

Cliquez ici pour passer à Office 365

Si vous êtes déjà sur Office 365 (édition familiale, personnelle ou universitaire) et que vous n'y avez pas accès, vous pouvez accéder à l'onglet Fichier, puis cliquer sur Compte.

Il y aurait un programme Office Insider et vous pouvez cliquer et rejoindre le programme Office Insider. Cela vous donnera accès à la fonction XLOOKUP.

Je m'attends à ce que XLOOKUP soit bientôt disponible sur toutes les versions d'Office 365.

Remarque : XLOOKUP est également disponible pour Office 365 pour Mac et Excel pour le Web (Excel en ligne)

Syntaxe de la fonction XLOOKUP

Vous trouverez ci-dessous la syntaxe de la fonction XLOOKUP :

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Si vous avez utilisé RECHERCHEV, vous remarquerez que la syntaxe est assez similaire, avec bien sûr quelques fonctionnalités supplémentaires impressionnantes.

Ne vous inquiétez pas si la syntaxe et l'argument semblent un peu trop. Je les couvre avec quelques exemples XLOOKUP faciles plus loin dans ce didacticiel qui le rendront parfaitement clair.

La fonction XLOOKUP peut contenir 6 arguments (3 obligatoires et 3 facultatifs) :

1. valeur_recherche - la valeur que vous recherchez
2. tableau_recherche - le tableau dans lequel vous recherchez la valeur de recherche
3. tableau_retour - le tableau à partir duquel vous souhaitez récupérer et renvoyer la valeur (correspondant à la position où se trouve la valeur de recherche)
4. [if_not_found] - la valeur à retourner au cas où la valeur de recherche n'est pas trouvée. Si vous ne spécifiez pas cet argument, une erreur #N/A sera renvoyée
5. [match_mode] - Ici, vous pouvez spécifier le type de match que vous souhaitez :
   • 0 - Correspondance exacte, où lookup_value doit correspondre exactement à la valeur dans lookup_array. Ceci est l'option par défaut.
   • -1 - Recherche la correspondance exacte, mais si elle est trouvée, renvoie le prochain élément/valeur plus petit
   • 1 - Recherche la correspondance exacte, mais si elle est trouvée, renvoie le prochain élément/valeur plus grand
   • 2 - Pour faire une correspondance partielle à l'aide de caractères génériques (* ou ~)
6. [mode_recherche] - Ici, vous spécifiez comment la fonction XLOOKUP doit rechercher le lookup_array
   • 1 - C'est l'option par défaut où la fonction commence à rechercher la valeur lookup du haut (premier élément) vers le bas (dernier élément) dans le lookup_array
   • -1 - Effectue la recherche de bas en haut. Utile lorsque vous voulez trouver la dernière valeur correspondante dans lookup_array
   • 2 - Effectue une recherche binaire où les données doivent être triées par ordre croissant. S'il n'est pas trié, cela peut donner des erreurs ou des résultats erronés
   • -2 - Effectue une recherche binaire où les données doivent être triées par ordre décroissant. S'il n'est pas trié, cela peut donner des erreurs ou des résultats erronés

Exemples de fonctions XLOOKUP

Passons maintenant à la partie intéressante - quelques exemples pratiques XLOOKUP.

Ces exemples vous aideront à mieux comprendre comment fonctionne XLOOKUP, en quoi il est différent de VLOOKUP et INDEX/MATCH et certaines améliorations et limitations de cette fonction.

Cliquez ici pour télécharger le fichier d'exemple et suivez

Exemple 1 : Récupérer une valeur de recherche

Supposons que vous ayez l'ensemble de données suivant et que vous souhaitiez récupérer le score mathématique de Greg (la valeur de recherche).

Ci-dessous la formule qui fait cela :

=XRECHERCHE(F2,A2:A15,B2:B15)

Dans la formule ci-dessus, je viens d'utiliser les arguments obligatoires où il recherche le nom (de haut en bas), trouve une correspondance exacte et renvoie la valeur correspondante de B2:B15.

Une différence évidente entre les fonctions RECHERCHEX et RECHERCHEV est la façon dont elles gèrent le tableau de recherche. Dans RECHERCHEV, vous avez tout le tableau où la valeur de recherche se trouve dans la colonne la plus à gauche, puis vous spécifiez le numéro de colonne à partir duquel vous souhaitez récupérer le résultat. XLOOKUP, d'autre part, vous permet de choisir lookup_array et return_array séparément

Un avantage instantané d'avoir lookup_array et return_array en tant qu'arguments séparés signifie que maintenant vous pouvez regarde à gauche. RECHERCHEV avait cette limitation où vous pouvez uniquement rechercher et trouver une valeur à droite. Mais avec XLOOKUP, cette limitation a disparu.

Voici un exemple. J'ai le même ensemble de données, où le nom est à droite et le return_range est à gauche.

Vous trouverez ci-dessous la formule que je peux utiliser pour obtenir le score de Greg en mathématiques (ce qui signifie regarder à gauche de la valeur lookup_value)

=XRECHERCHE(F2,D2:D15,A2:A15)

XLOOKUP résout un autre problème majeur - Si vous insérez une nouvelle colonne ou déplacez des colonnes, les données résultantes seraient toujours correctes. RECHERCHEV casserait probablement ou donnerait un résultat incorrect dans de tels cas, car la plupart du temps, la valeur d'index de colonne est codée en dur.

Exemple 2 : rechercher et récupérer un enregistrement entier

Prenons les mêmes données comme exemple.

Dans ce cas, je ne veux pas simplement récupérer le score de Greg en mathématiques. Je veux obtenir les scores dans toutes les matières.

Dans ce cas, je peux utiliser la formule ci-dessous :

=XRECHERCHE(F2,A2:A15,B2:D15)

La formule ci-dessus utilise une plage return_array qui est plus qu'une colonne (B2:D15). Ainsi, lorsque la valeur de recherche est trouvée dans A2:A15, la formule renvoie la ligne entière de return_array.

De plus, vous ne pouvez pas supprimer uniquement les cellules faisant partie du tableau qui ont été automatiquement remplies. Dans cet exemple, vous ne pouvez pas supprimer H2 ou I2. Si vous essayez, rien ne se passera. Si vous sélectionnez ces cellules, la formule dans la barre de formule serait grisée (indiquant qu'elle ne peut pas être modifiée)

Vous pouvez supprimer la formule dans la cellule G2 (où nous l'avons entrée à l'origine), cela supprimera tout le résultat.

Il s'agit d'une amélioration utile car précédemment avec RECHERCHEV, vous devrez spécifier le numéro de colonne séparément pour chaque formule.

Exemple 3 : recherche bidirectionnelle à l'aide de XLOOKUP (recherche horizontale et verticale)

Vous trouverez ci-dessous un ensemble de données où je souhaite connaître le score de Greg en mathématiques (le sujet de la cellule G2).

Cela peut être fait en utilisant une recherche bidirectionnelle où je recherche le nom dans la colonne A et le nom du sujet dans la ligne 1. L'avantage de cette recherche bidirectionnelle est que le résultat est indépendant du nom de l'étudiant du nom du sujet. Si je change le nom du sujet en Chimie, cette formule XLOOKUP bidirectionnelle fonctionnera toujours et me donnera le résultat correct.

Vous trouverez ci-dessous la formule qui effectuera la recherche bidirectionnelle et donnera le résultat correct :

=RECHERCHEX(G1,B1:D1,RECHERCHEX(F2,A2:A15,B2:D15))

Cette formule utilise un XLOOKUP imbriqué, où je l'utilise d'abord pour récupérer toutes les notes de l'étudiant dans la cellule F2.

Ainsi, le résultat de RECHERCHEX(F2,A2:A15,B2:D15) est {21,94,81}, qui est un tableau de notes attribuées par Greg dans ce cas.

Ceci est ensuite utilisé à nouveau dans la formule XLOOKUP externe comme tableau de retour. Dans la formule externe XLOOKUP, je recherche le nom du sujet (qui se trouve dans la cellule G1) et le tableau de recherche est B1:D1.

Si le nom du sujet est Math, cette formule XLOOKUP externe récupère la première valeur du tableau de retour - qui est {21,94,81} dans cet exemple.

Cela fait la même chose qui était, jusqu'à présent, réalisée en utilisant le combo INDEX et MATCH

Cliquez ici pour télécharger le fichier d'exemple et suivez

Exemple 4 : Lorsque la valeur de recherche n'est pas trouvée (gestion des erreurs)

La gestion des erreurs a maintenant été ajoutée à la formule XLOOKUP.

Le quatrième argument de la fonction XLOOKUP est [if_not_found], où vous pouvez spécifier ce que vous voulez au cas où la recherche ne serait pas trouvée.

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous où vous souhaitez obtenir le score mathématique en cas de correspondance, et au cas où le nom n'est pas trouvé, vous souhaitez revenir - « N'apparaissait pas »

La formule ci-dessous fera cela:

=XLOOKUP(F2,A2:A15,B2:B15,"N'apparaissait pas")

Dans ce cas, j'ai codé en dur ce que je veux obtenir au cas où il n'y aurait pas de correspondance. Vous pouvez également utiliser une référence de cellule à une cellule ou à une formule.

Exemple 5 : XLOOKUP imbriqué (recherche dans plusieurs plages)

Le génie d'avoir l'argument [if_not_found] est qu'il vous permet d'utiliser formule XLOOKUP imbriquée.

Par exemple, supposons que vous ayez deux listes distinctes, comme indiqué ci-dessous. Bien que j'aie ces deux tableaux sur la même feuille, vous pouvez les avoir dans des feuilles séparées ou même dans des classeurs.

Vous trouverez ci-dessous la formule XLOOKUP imbriquée qui vérifiera le nom dans les deux tables et renverra la valeur correspondante de la colonne spécifiée.

=RECHERCHEX(A12,A2:A8,B2:B8,RECHERCHEX(A12,F2:F8,G2:G8))

Dans la formule ci-dessus, j'ai utilisé l'argument [if_not_found] pour utiliser une autre formule XLOOKUP. Cela vous permet d'ajouter le deuxième XLOOKUP dans la même formule et de numériser deux tables avec une seule formule.

Je ne sais pas combien de XLOOKUP imbriqués vous pouvez utiliser dans une formule. J'ai essayé jusqu'à 10h et ça a marché, puis j'ai abandonné

Exemple 6 : Trouver la dernière valeur correspondante

Celui-ci était absolument nécessaire et XLOOKUP a rendu cela possible. Maintenant, vous n'avez plus besoin de trouver des moyens alambiqués pour obtenir la dernière valeur correspondante dans une plage.

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous vouliez vérifier quand la dernière personne a été embauchée dans chaque service et quelle était la date d'embauche.

La formule ci-dessous recherchera la dernière valeur pour chaque département et donnera le nom de la dernière embauche :

=XRECHERCHE(F1,$B$2:$B$15,$A$2:$A$15,,,-1)

Et la formule ci-dessous donnera la date d'embauche de la dernière embauche pour chaque département :

=XRECHERCHE(F1,$B$2:$B$15,$C$2:$C$15,,,-1)

Étant donné que XLOOKUP a une fonctionnalité intégrée pour spécifier la direction de la recherche (du premier au dernier ou du dernier au premier), cela se fait avec une formule simple. Avec les données verticales, VLOOKUP et INDEX/MATCH regardent toujours de haut en bas, mais avec XLOOKUP et peuvent également spécifier la direction de bas en haut.

Exemple 7 : Correspondance approximative avec XLOOKUP (Trouver le taux d'imposition)

Une autre amélioration notable avec XLOOKUP est qu'il existe désormais quatre modes de correspondance (VLOOKUP en a 2 et MATCH en a 3).

Vous pouvez spécifier l'un des quatre arguments pour décider comment la valeur de recherche doit être mise en correspondance :

• 0 - Correspondance exacte, où lookup_value doit correspondre exactement à la valeur dans lookup_array. Ceci est l'option par défaut.
• -1 - Recherche la correspondance exacte, mais si elle est trouvée, renvoie le prochain élément/valeur plus petit
• 1 - Recherche la correspondance exacte, mais si elle est trouvée, renvoie le prochain élément/valeur plus grand
• 2 - Pour faire une correspondance partielle à l'aide de caractères génériques (* ou ~)

Mais la meilleure partie est que vous n'avez pas à vous soucier de savoir si vos données sont triées par ordre croissant ou décroissant. Même si les données ne sont pas triées, XLOOKUP s'en occupera.

Ci-dessous, j'ai un ensemble de données où je veux trouver la commission de chaque personne - et la commission doit être calculée à l'aide du tableau à droite.

Vous trouverez ci-dessous la formule qui le fera :

=XRECHERCHE(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2

Cela utilise simplement la valeur des ventes comme recherche et parcourt la table de recherche sur la droite. Dans cette formule, j'ai utilisé -1 comme cinquième argument ([match_mode]), ce qui signifie qu'il recherchera une correspondance exacte, et lorsqu'il n'en trouvera pas, il renverra la valeur juste plus petite que la valeur de recherche .

Et comme je l'ai dit, vous n'avez pas à vous soucier de savoir si vos données ne sont pas triées.

Cliquez ici pour télécharger le fichier d'exemple et suivez

Exemple 8 : Recherche horizontale

XLOOKUP peut faire une recherche aussi bien verticale qu'horizontale.

Ci-dessous, j'ai un ensemble de données où j'ai les noms des étudiants et leurs scores en lignes, et je veux récupérer le score pour le nom dans la cellule B7.

La formule ci-dessous fera cela:

=XRECHERCHE(B7,B1:O1,B2:O2)

Ce n'est rien d'autre qu'une simple recherche (similaire à ce que nous avons vu dans l'exemple 1), mais horizontale.

Tous les exemples que je couvre sur la recherche verticale peuvent également être effectués avec une recherche horizontale à l'aide de XLOOKUP (adieu à VLOOKUP et HLOOKUP).

Exemple 9 : Recherche conditionnelle (utilisation de XLOOKUP avec d'autres formules)

Celui-ci est un exemple légèrement avancé et montre également la puissance de XLOOKUP lorsque vous devez effectuer des recherches complexes.

Vous trouverez ci-dessous un ensemble de données où j'ai les noms des étudiants et leurs scores, et je veux connaître le nom de l'étudiant qui a obtenu le maximum dans chaque matière et le nombre d'étudiants qui ont obtenu plus de 80 dans chaque matière.

Voici la formule qui donnera le nom de l'étudiant ayant les meilleures notes dans chaque matière :

=XRECHERCHE(MAX(XRECHERCHE(G1,$B$1:$D$1,$B$2:$D$15)),XRECHERCHE(G1,$B$1:$D$1,$B$2:$D$15),$A $2 :$A$15)

Étant donné que XLOOKUP peut être utilisé pour renvoyer un tableau entier, je l'ai utilisé pour obtenir d'abord toutes les notes pour le sujet requis.

Par exemple, pour les mathématiques, lorsque j'utilise RECHERCHEX(G1,$B$1:$D$1,$B$2:$D$15), cela me donne tous les scores en mathématiques. Je peux ensuite utiliser la fonction MAX pour trouver le score maximum dans cette plage.

Ce score maximum devient alors ma valeur de recherche, et la plage de recherche serait le tableau renvoyé par XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15)

J'utilise ceci dans une autre formule XLOOKUP pour récupérer le nom de l'étudiant qui a obtenu le maximum de notes.

Et pour compter le nombre d'élèves qui ont obtenu plus de 80, utilisez la formule ci-dessous :

=COUNTIF(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15),">80")

Celui-ci utilise simplement la formule XLOOKUP pour obtenir une plage de toutes les valeurs pour le sujet donné. Il l'enveloppe ensuite dans la fonction COUNTIF pour obtenir le nombre de scores supérieurs à 80.

Exemple 10 : Utilisation de Wildcard dans XLOOKUP

Tout comme vous pouvez utiliser des caractères génériques dans RECHERCHEV et MATCH, vous pouvez également le faire avec RECHERCHEX.

Mais il y a une différence.

Dans XLOOKUP, vous devez spécifier que vous utilisez des caractères génériques (dans le cinquième argument). Si vous ne le spécifiez pas, XLOOKUP vous donnera une erreur.

Vous trouverez ci-dessous un ensemble de données où j'ai les noms des entreprises et leur capitalisation boursière.

Je souhaite rechercher le nom d'une entreprise dans la colonne D et récupérer la capitalisation boursière dans le tableau de gauche. Et comme les noms de la colonne D ne correspondent pas exactement, je devrai utiliser des caractères génériques.

Vous trouverez ci-dessous la formule qui le fera :

=XRECHERCHE("*"&D2&"*",$A$2:$A$11,$B$2:$B$11,,2)

Dans la formule ci-dessus, j'ai utilisé le caractère générique astérisque (*) avant comme après D2 (il doit être entre guillemets doubles et joint à D2 à l'aide d'une esperluette).

Cela indique à la formule de parcourir toutes les cellules, et si elle contient le mot dans la cellule D2 (qui est Apple), considérez qu'il s'agit d'une correspondance exacte. Peu importe combien et quels caractères sont avant et après le texte dans la cellule D2.

Et pour s'assurer que RECHERCHEX accepte les caractères génériques, le cinquième argument a été défini sur 2 (correspondance de caractères génériques).

Exemple 11 : Trouver la dernière valeur dans la colonne

Étant donné que XLOOKUP vous permet de rechercher de bas en haut, vous pouvez facilement trouver la dernière valeur d'une liste, ainsi que récupérer la valeur correspondante dans une colonne.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous vouliez savoir quelle est la dernière entreprise et quelle est la capitalisation boursière de cette dernière entreprise.

La formule ci-dessous vous donnera le nom de la dernière entreprise :

=XRECHERCHE("*",A2:A11,A2:A11,,2,-1)

Et la formule ci-dessous donnera la capitalisation boursière de la dernière entreprise de la liste :

=XRECHERCHE("*",A2:A11,B2:B11,,2,-1)

Ces formules utilisent à nouveau des caractères génériques. Dans ceux-ci, j'ai utilisé l'astérisque (*) comme valeur de recherche, ce qui signifie que cela considérerait la première cellule rencontrée comme une correspondance exacte (car l'astérisque pourrait être n'importe quel caractère et n'importe quel nombre de caractères).

Et puisque la direction est de bas en haut (pour les données disposées verticalement), il renverra la dernière valeur de la liste.

Et la deuxième formule utilise depuis un return_range séparé pour obtenir la capitalisation boursière du nom de famille dans la liste.

Cliquez ici pour télécharger le fichier d'exemple et suivez

Que faire si vous n'avez pas XLOOKUP ?

Étant donné que XLOOKUP ne sera probablement disponible que pour les utilisateurs d'Office 365, une façon de l'obtenir est de passer à Office 365.

Si vous possédez déjà l'édition Office 365 Famille, Personnelle ou Université, vous avez déjà accès à XLOOKUP. Il vous suffit de rejoindre le programme Office Insider.

Pour ce faire, allez dans l'onglet Fichier, cliquez sur Compte puis sur l'option Office insider. Il y aurait une option pour rejoindre le programme d'initiés.

Si vous avez d'autres abonnements Office 365 (tels que Enterprise), je suis sûr que XLOOKUP et d'autres fonctionnalités impressionnantes (telles que des tableaux dynamiques, des formules telles que SORT et FILTER) seraient bientôt disponibles.

Si vous utilisez Excel 2010/2013/2016/2019, vous n'aurez pas XLOOKUP et vous devrez continuer à utiliser VLOOKUP, HLOOKUP et INDEX/MATCH combo pour tirer le meilleur parti des formules de recherche.

Compatibilité descendante XLOOKUP

C'est une chose à laquelle vous devez faire attention - XLOOKUP est NON rétrocompatible.

Cela signifie que si vous créez un fichier et utilisez la formule XLOOKUP, puis l'ouvrez dans une version qui n'a pas XLOOKUP, il affichera des erreurs.

Étant donné que XLOOKUP est un énorme pas en avant dans la bonne direction, je pense que cela deviendra la formule de recherche par défaut, mais cela prendra sûrement quelques années avant d'être largement adopté. Après tout, je vois encore des personnes utiliser Excel 2003.

Voici donc 11 exemples XLOOKUP qui peuvent vous aider à effectuer toutes les recherches et références plus rapidement et à faciliter leur utilisation.

J'espère que vous avez trouvé ce tutoriel utile!