Comment utiliser la fonction XLOOKUP dans Excel

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 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.

Comment utiliser la fonction XLOOKUP dans Excel

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

Comment utiliser la fonction XLOOKUP dans Excel

Dans cet exemple, nous utiliserons la formule :

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

Comment utiliser la fonction XLOOKUP dans Excel

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).

Comment utiliser la fonction XLOOKUP dans Excel

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.

Comment utiliser la fonction XLOOKUP dans Excel

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).

Comment utiliser la fonction XLOOKUP dans Excel

Utilisez la formule ci-dessous :

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

Comment utiliser la fonction XLOOKUP dans Excel

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.

Comment utiliser la fonction XLOOKUP dans Excel

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

Comment utiliser la fonction XLOOKUP dans Excel

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)

Comment utiliser la fonction XLOOKUP dans Excel

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.

Comment utiliser la fonction XLOOKUP dans Excel

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

Comment utiliser la fonction XLOOKUP dans Excel

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)

Comment utiliser la fonction XLOOKUP dans Excel

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).

Comment utiliser la fonction XLOOKUP dans Excel

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

Comment utiliser la fonction XLOOKUP dans Excel

Formule utilisée dans cet exemple :

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

Comment utiliser la fonction XLOOKUP dans Excel

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

RECHERCHEV

RECHERCHEXL

Correspondance exacte par défaut

N

Oui

Renvoie la valeur à droite de la recherche

Oui

Oui

Renvoie la valeur à gauche de la recherche

N

Oui

Rechercher par ligne

N

Oui

Renvoie plusieurs valeurs

N

Oui

Besoin de classer la colonne de recherche

Correspondances approximatives uniquement

Recherche binaire uniquement

Rechercher de haut en bas

Oui

Oui

Rechercher de bas en haut

N

Oui

Recherche binaire

N

Oui

Personnalisez le message si la valeur de recherche n'est pas trouvée

N

Oui

Recherche par caractère générique

Oui

Oui

Recherche exacte

Oui

Oui

La recherche approximative renvoie la valeur la plus petite suivante

Oui

Oui

La recherche approximative renvoie la valeur immédiatement supérieure

N

Oui

Une valeur incorrecte peut être renvoyée si les valeurs de recherche ne sont pas triées

Y - approximatif

N - approximatif,

Y - binaire

L'ajout d'une nouvelle colonne pour trouver le tableau peut casser la formule

Oui

N

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.

Excel 2016 - Leçon 6 : Modifier la taille des colonnes, des lignes et des cellules dans Excel

Excel 2016 - Leçon 6 : Modifier la taille des colonnes, des lignes et des cellules dans Excel

Découvrez comment modifier la taille des colonnes, des lignes et des cellules dans Excel 2016 pour afficher vos données efficacement.

Comment utiliser la fonction HLOOKUP dans Excel

Comment utiliser la fonction HLOOKUP dans Excel

Apprenez comment utiliser la <strong>fonction HLOOKUP</strong> dans Excel pour rechercher des données efficacement. Découvrez des conseils et des exemples utiles !

Comment utiliser la fonction WORKDAY dans Microsoft Excel

Comment utiliser la fonction WORKDAY dans Microsoft Excel

Découvrez comment utiliser la fonction WORKDAY dans Microsoft Excel pour calculer efficacement les jours ouvrables en excluant week-ends et jours fériés.

Comment utiliser les fonctions VSTACK et HSTACK dans Excel

Comment utiliser les fonctions VSTACK et HSTACK dans Excel

Découvrez comment empiler et ajouter des données efficacement dans Excel avec les fonctions VSTACK et HSTACK. Améliorez votre productivité !

Comment utiliser la fonction NORMDIST dans Excel

Comment utiliser la fonction NORMDIST dans Excel

Découvrez comment utiliser la fonction NORMDIST dans Excel pour analyser les données statistiques, en suivant nos étapes simples et efficaces.

Comment utiliser la boucle do-while dans Excel VBA

Comment utiliser la boucle do-while dans Excel VBA

Vous souhaitez automatiser des tâches répétitives dans Excel VBA ? Découvrez comment utiliser une boucle Do-While pour exécuter des actions jusqu

Comment accéder et ouvrir un fichier Excel 97/2003 .xls dans Excel 2016, 2019 et 365 ?

Comment accéder et ouvrir un fichier Excel 97/2003 .xls dans Excel 2016, 2019 et 365 ?

Découvrez comment ouvrir danciens fichiers Excel avec Excel 365 (2016/2019).

Comment utiliser la fonction DSUM pour calculer des sommes avec des conditions complexes dans Excel

Comment utiliser la fonction DSUM pour calculer des sommes avec des conditions complexes dans Excel

Découvrez comment utiliser la fonction DSUM dans Excel pour calculer des sommes conditionnelles complexes efficacement.

Fonction Match dans Excel : Comment utiliser la fonction Match avec des exemples

Fonction Match dans Excel : Comment utiliser la fonction Match avec des exemples

Découvrez comment utiliser la <strong>fonction Match</strong> dans Excel pour le traitement de données. Apprenez avec des exemples pratiques.

Jouez à puzzle sur Excel

Jouez à puzzle sur Excel

MehmetSalihKoten, un utilisateur de Reddit, a créé une version entièrement fonctionnelle de Tetris dans Microsoft Excel.