Comment utiliser la fonction XLOOKUP dans Excel

Qu'est-ce que la fonction Xlookup dans Excel ? Comment utiliser Xlookup dans Excel ? Découvrons-le avec LuckyTemplates.com !

Si vous utilisez régulièrement Excel, vous êtes probablement familier avec les fonctions de recherche. Si vous ne le savez pas, il vous suffit de le comprendre comme une fonction Excel utilisée pour rechercher des données ou des valeurs spécifiques sur des lignes ou des colonnes dans une feuille de calcul.

Cette fonction est particulièrement utile lorsque vous devez traiter beaucoup de données dans une feuille de calcul Excel. Dans Excel, vous disposez de plusieurs types de fonctions RECHERCHE. XLOOKUP en fait partie. Alors qu’est-ce que la fonction XLOOKUP, qu’est-ce que la formule XLOOKUP et comment l’utiliser ? Découvrons-le avec LuckyTemplates.com !

Ce qu'il faut savoir sur la fonction XLOOKUP dans Excel

• Qu’est-ce que XLOOKUP ?
  • Comment accéder à la fonction XLOOKUP
  • Syntaxe de la fonction XLOOKUP
• Avantages et inconvénients de la fonction XLOOKUP dans Excel
  • Avantages de la fonction XLOOKUP
  • Inconvénients de la fonction XLOOKUP
• Comment utiliser la fonction XLOOKUP
  • Il n'y a pas de numéros d'index de colonne supplémentaires
  • Correspondance exacte par défaut
  • XLOOKUP se trouve sur la gauche
  • Utilisez XLOOKUP pour rechercher des plages
  • XLOOKUP remplace la fonction HLOOKUP
  • XLOOKUP peut être consulté de bas en haut

Qu'est-ce que la fonction XLOOKUP dans Excel ?

Essentiellement, XLOOKUP remplace les anciennes fonctions telles que HLOOKUP, VLOOKUP et LOOKUP. Il prend en charge la recherche de données verticalement et horizontalement.

En d'autres termes, la fonction XLOOKUP d'Excel vous aide à trouver rapidement des valeurs dans l'ensemble de données fourni, à la fois horizontalement et verticalement. Ensuite, il renvoie la valeur correspondante selon différentes lignes et colonnes.

Il existe de nombreuses façons d'optimiser l'utilisation de la fonction XLOOKUP.

Comment accéder à la fonction XLOOKUP

Actuellement, XLOOKUP n'est disponible que pour les utilisateurs du programme Insiders. Toute personne qui rejoint le programme Insider peut accéder aux dernières fonctionnalités d'Excel dès qu'elles sont disponibles. Microsoft déploiera bientôt cette fonction pour tous les utilisateurs d'Office 365. Malheureusement, XLOOKUP n'est pas disponible pour les utilisateurs exécutant les versions d'Office 2010, 2013, 2016 et 2019.

Si vous n'utilisez pas la suite Microsoft 365 sur votre ordinateur, vous devrez peut-être effectuer une mise à niveau vers Microsoft 365 pour accéder à la fonctionnalité XLOOKUP. Et si vous utilisez Microsoft 365, vous verrez l'option activée dans Excel. XLOOKUP est également disponible sur Office 365 Online.

Syntaxe de la fonction XLOOKUP

La syntaxe de la fonction XLOOKUP est similaire à la syntaxe des fonctions VLOOKUP et HLOOKUP. Si vous les avez déjà utilisés, vous trouverez l’utilisation de XLOOKUP plus pratique. Voici la syntaxe de la fonction XLOOKUP dans Excel :

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

La fonction XLOOKUP prend en charge jusqu'à 6 arguments et voici leurs valeurs.

1. lookup_value (obligatoire) : la valeur que vous souhaitez rechercher.

2. lookup_array (obligatoire) : le tableau dans lequel vous souhaitez rechercher la valeur de recherche.

3. return_array (obligatoire) : tableau à partir duquel vous souhaitez récupérer et renvoyer les valeurs lorsque la valeur de recherche est trouvée.

4. [if_not_found] (facultatif) : lorsqu'aucune correspondance n'est trouvée, c'est la valeur renvoyée.

5. [match_mode] (facultatif) : Cet argument vous permet de spécifier le type de correspondance que vous recherchez. Il existe différentes valeurs pour le préciser :

• 0 - Recherche une correspondance exacte et la valeur doit correspondre exactement à la valeur dans lookup_array. Cette valeur est également définie par défaut lorsqu'elle n'est pas mentionnée.
• -1 - Recherche une correspondance exacte et, une fois trouvée, reviendra à la valeur immédiatement inférieure.
• 1 - Recherche une correspondance exacte et, une fois trouvée, renvoie la valeur immédiatement supérieure.
• 2 - Effectuez une correspondance partielle à l'aide de caractères génériques où *, ? et ~ a une signification particulière.

6. [search_mode] (facultatif) : utilisé pour spécifier le mode de recherche de XLOOKUP dans lookup_array. Il existe différentes valeurs pour spécifier la même chose :

• 1 - Effectuez une recherche à partir du premier élément. Cette valeur est définie par défaut lorsque rien n'est spécifié.
• -1 - Effectue une recherche inversée à partir du dernier élément.
• 2 - Effectuez une recherche binaire dans lookup_array où les données doivent être triées par ordre croissant. Si les données ne sont pas triées, cela peut produire des erreurs ou des résultats erronés.
• -2 - Effectuez une recherche binaire dans lookup_array où les données doivent être triées par ordre décroissant. Si les données ne sont pas triées, cela peut produire des erreurs ou des résultats erronés.

Avantages et inconvénients de la fonction XLOOKUP dans Excel

XLOOKUP reste une fonction avantageuse par rapport à VLOOKUP et INDEX/ATCH. Mais cela a aussi ses propres inconvénients.

Avantages de la fonction XLOOKUP

• La mâchoire fonctionne aussi bien verticalement qu'horizontalement.
• Seuls 3 arguments sont nécessaires, au lieu de 4 comme dans VLOOKUP et INDEX MATCH.
• Toujours par défaut une correspondance exacte.
• Des recherches partielles peuvent être effectuées à l'aide de caractères génériques.
• Les recherches peuvent être effectuées par ordre décroissant.
• Utilisez une fonction au lieu de deux dans INDEX MATCH.

Inconvénients de la fonction XLOOKUP

• Les arguments facultatifs peuvent paraître compliqués aux débutants.
• Cela peut prendre plus de temps lors de la sélection de deux plages et du fait d'avoir trop de cellules dans la feuille de calcul.
• Renvoie une erreur lors de la recherche et du renvoi d'un tableau qui n'a pas la même longueur.
• N'oubliez pas les plages de recherche et de retour.

Avantages de l'utilisation de la fonction XLOOKUP Excel

• XLOOKUP rend les formules les plus utilisées dans Excel plus simples et moins sujettes aux erreurs . Il vous suffit d'écrire =XLOOKUP (données à rechercher, liste, liste de résultats) et vous obtiendrez la réponse ou #N/A si la valeur n'est pas trouvée.
• Les résultats de la recherche par défaut sont exacts . L'un des inconvénients de RECHERCHEV est que vous devez mentionner FALSE comme dernier paramètre pour obtenir le résultat correct. XLOOKUP corrige ce problème en effectuant une correspondance exacte par défaut. Vous pouvez utiliser le paramètre match mode pour modifier le comportement de recherche si vous le souhaitez.
• Le quatrième paramètre prend en charge le cas où la valeur n'est pas trouvée . Dans la plupart des situations professionnelles, vous serez obligé de « envelopper » la formule de recherche avec SIERREUR ou IFNA pour éliminer les erreurs. XLOOKUP fournit un 4ème paramètre afin que vous puissiez connaître le résultat par défaut souhaité si la valeur n'est pas trouvée.
• XLOOKUP fournit des paramètres de sélection pour trouver des situations particulières . Vous pouvez effectuer une recherche par le haut ou par le bas, par caractères spéciaux et par des options plus rapides pour trouver des données filtrées.

Comment utiliser la fonction XLOOKUP

Jetez un œil à l’exemple ci-dessous pour comprendre comment fonctionne XLOOKUP. Dans cet exemple, nous devons renvoyer la pièce de la colonne F pour chaque ID de la colonne A.

Il s'agit d'un exemple qui recherche des résultats exacts, mais la fonction XLOOKUP ne nécessite que trois types d'informations.

L'image ci-dessous montre XLOOKUP avec 5 arguments, mais les trois premiers arguments sont nécessaires pour des résultats corrects. Alors concentrons-nous sur eux :

• Lookup_value : valeur de recherche
• Lookup_array : plage de recherche
• Return_array : Plage contenant la valeur à retourner

Dans cet exemple, nous utiliserons la formule :

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Explorons maintenant certains des avantages de XLOOKUP par rapport à VLOOKUP.

Il n'y a pas de numéros d'index de colonne supplémentaires

Le troisième argument de VLOOKUP consiste à spécifier le nombre de colonnes d'informations à renvoyer à partir de la plage du tableau. Ce n'est plus un problème avec XLOOKUP car la fonction permet à l'utilisateur de sélectionner la plage de retour. (colonne F dans cet exemple).

Et n'oubliez pas, XLOOKUP peut voir les données restantes de la cellule sélectionnée, contrairement à VLOOKUP.

Vous ne rencontrerez pas non plus d’erreurs de formule lors de l’ajout de nouvelles colonnes. Si cela se produit dans la feuille de calcul, la plage de retour sera ajustée automatiquement.

Correspondance exacte par défaut

Lors de l'utilisation de RECHERCHEV, l'utilisateur doit spécifier une correspondance exacte s'il le souhaite. Mais avec XLOOKUP, la valeur par défaut est une correspondance exacte. Cela réduit le quatrième argument et garantit moins d'erreurs pour les nouveaux utilisateurs. En bref, XLOOKUP nécessite moins de questions que VLOOKUP et est convivial.

XLOOKUP se trouve sur la gauche

La possibilité de sélectionner des plages de recherche donne à XLOOKUP plus de flexibilité que VLOOKUP. Avec XLOOKUP, l'ordre des colonnes dans le tableau n'a pas d'importance.

RECHERCHEV est limitée en recherchant la colonne la plus à gauche du tableau, puis en revenant à partir d'un nombre spécifié de colonnes à droite.

Dans l'exemple ci-dessous, nous devons rechercher l'identifiant (colonne E) et renvoyer le nom de la personne (colonne D).

Utilisez la formule ci-dessous :

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Utilisez XLOOKUP pour rechercher des plages

Bien que ce ne soit pas aussi courant que la correspondance exacte, vous pouvez utiliser une formule de recherche pour trouver une valeur dans une plage. Par exemple, nous souhaitons restituer une remise en fonction du montant dépensé.

Cette fois, nous ne recherchons pas de valeur spécifique et devons savoir dans quelle plage se situent les valeurs de la colonne B dans la colonne E pour déterminer la remise.

XLOOKUP a un quatrième argument facultatif (rappelez-vous, il s'agit par défaut d'une correspondance absolue) appelé match_mode.

Vous pouvez voir que XLOOKUP peut mieux trouver des correspondances approximatives que VLOOKUP.

Il existe une option pour trouver la correspondance la plus proche inférieure à (-1) ou la correspondance la plus proche supérieure à (1) la valeur de recherche. Il existe également une option permettant d'utiliser des caractères génériques (2), tels que ? ou *. Ce paramètre n’est pas activé par défaut comme c’est le cas avec RECHERCHEV.

La formule de cet exemple renvoie la valeur la plus proche inférieure à la valeur de recherche si aucune correspondance exacte n'est trouvée :

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Cependant, il y a eu une erreur dans la cellule C7, la remise de 0 % aurait dû être renvoyée car il n'en a dépensé que 64, ce qui ne répondait pas aux critères pour bénéficier de la remise.

Un autre avantage de la fonction XLOOKUP est qu'elle ne nécessite pas de plage de recherche par ordre croissant comme VLOOKUP.

Entrez une nouvelle ligne au bas de la table de recherche, puis ouvrez la formule. Élargissez le champ d'utilisation en cliquant et en faisant glisser les coins.

La formule corrige immédiatement l'erreur. Il n’y a aucun problème à avoir 0 à la fin du tableau des plages.

XLOOKUP remplace la fonction HLOOKUP

Comme mentionné, la fonction XLOOKUP peut également remplacer la fonction HLOOKUP. Une fonction peut remplacer deux fonctions, quoi de mieux ?

La fonction HLOOKUP recherche horizontalement, utilisée pour effectuer une recherche par ligne.

Cette fonction n'est pas aussi connue que RECHERCHEV mais est utile dans les cas où le titre est dans la colonne A et les données sont dans les lignes 4 et 5 comme dans l'exemple ci-dessous.

XLOOKUP peut effectuer une recherche dans les deux sens, dans les colonnes et le long des lignes.

Dans cet exemple, une formule est utilisée pour renvoyer la valeur des ventes associée au nom dans la colonne A2. Il recherche le nom dans la ligne 4 et renvoie la valeur de la ligne 5 :

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP peut être consulté de bas en haut

Habituellement, vous devez regarder de haut en bas dans une liste pour trouver la première occurrence d'une valeur. XLOOKUP a un cinquième argument nommé search_mode. Cet argument permet de rediriger la recherche pour commencer par le bas d'une liste pour retrouver la dernière présence d'une valeur.

Dans l'exemple ci-dessous, nous devons trouver le niveau de stock de chaque produit dans la colonne A.

La table de recherche est organisée par ordre de date. Nous souhaitons renvoyer le niveau de stock du dernier contrôle (dernière occurrence de l'ID produit).

Le cinquième argument de la fonction XLOOKUP propose quatre options. Ici, nous utiliserons l'option Rechercher du dernier au premier .

Formule utilisée dans cet exemple :

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

Dans cette formule, le quatrième argument est omis. C'est facultatif et nous voulons que la valeur par défaut corresponde exactement.

La fonction XLOOKUP est le successeur attendu des fonctions VLOOKUP et HLOOKUP. Une série d'exemples ont été utilisés dans cet article pour démontrer les avantages de la fonction XLOOKUP.

XLOOKUP est une grande amélioration en matière de fonctions de recherche. La seule limitation de la fonction XLOOKUP est qu'elle n'est pas rétrocompatible. Si vous partagez des feuilles de calcul avec des versions antérieures de la suite Microsoft Office, vous risquez de recevoir des erreurs.

Cependant, vous pouvez accéder à XLOOKUP depuis Office 365 Online. XLOOKUP est encore en développement, il faudra donc un certain temps aux utilisateurs d'Excel pour l'adopter.

Je vous souhaite du succès !

Différence entre XLOOKUP et VLOOKUP

Pourquoi XLOOKUP est-il meilleur que VLOOKUP ?

Bien que VLOOKUP ait ses propres utilisations, ses fonctionnalités sont plus limitées que XLOOKUP. Cela fait de XLOOKUP le choix le plus populaire lorsque vous devez traiter de grandes quantités de données. En conséquence, il s’exécute plus rapidement et comporte moins d’erreurs.

• XLOOKUP ne limite pas la plage de données : XLOOKUP est plus flexible que VLOOKUP. RECHERCHEV peut rechercher les valeurs situées uniquement dans la colonne la plus à gauche d'un tableau et renvoyer les valeurs de la colonne correspondante de droite. Pendant ce temps, XLOOKUP ne nécessite que des étapes plus simples et peut renvoyer des valeurs pour n'importe quelle colonne dans les deux sens.
• Le résultat par défaut de XLOOKUP est la valeur la plus précise .
• Par défaut, la fonction RECHERCHEV dans Excel trouve toujours des résultats approximatifs . Cependant, si vous avez besoin de résultats exacts, vous devez entrer FALSE dans l'argument range_lookup.
• La fonction XLOOKUP recherche une valeur de recherche dans une plage spécifique et utilise toujours par défaut la valeur exacte. Si vous n’en trouvez pas, vous pouvez modifier l’argument pour trouver une correspondance approximative inférieure ou supérieure à la valeur de recherche.

Autres raisons:

• XLOOKUP peut gérer l'insertion ou la suppression de colonnes.
• XLOOKUP implémente des recherches verticales et horizontales.
• XLOOKUP fournit des correspondances approximatives plus intelligentes.
• XLOOKUP nécessite moins de références de cellules.