Exceller

Fonction Excel XLOOKUP

Excel Xlookup Function

Fonction Excel XLOOKUPRésumé

La fonction Excel XLOOKUP est un remplacement moderne et flexible des fonctions plus anciennes telles que VLOOKUP, HLOOKUP et LOOKUP. XLOOKUP prend en charge les correspondances approximatives et exactes, les caractères génériques (*?) Pour les correspondances partielles et les recherches dans des plages verticales ou horizontales.

Objectif Rechercher les valeurs dans la plage ou le tableau Valeur de retour Valeur (s) correspondante (s) du tableau de retour Syntaxe = XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]) Arguments
  • chercher - La valeur de recherche.
  • lookup_array - Le tableau ou la plage à rechercher.
  • return_array - Le tableau ou la plage à renvoyer.
  • pas trouvé - [facultatif] Valeur à renvoyer si aucune correspondance n'est trouvée.
  • match_mode - [facultatif] 0 = correspondance exacte (par défaut), -1 = correspondance exacte ou la plus petite suivante, 1 = correspondance exacte ou la prochaine plus grande, 2 = correspondance générique.
  • search_mode - [facultatif] 1 = recherche à partir du premier (par défaut), -1 = recherche à partir du dernier, 2 = recherche binaire ascendante, -2 = recherche binaire descendante.
Version Excel 365 Notes d'utilisation

XLOOKUP est un remplacement moderne de la fonction VLOOKUP. C'est une fonction flexible et polyvalente qui peut être utilisée dans une grande variété de situations.



XLOOKUP peut trouver des valeurs dans des plages verticales ou horizontales, peut effectuer des correspondances approximatives et exactes et prend en charge les caractères génériques (*?) Pour les correspondances partielles. De plus, XLOOKUP peut rechercher des données à partir de la première ou de la dernière valeur (voir les détails du type de correspondance et du mode de recherche ci-dessous). Par rapport aux fonctions plus anciennes comme RECHERCHEV , RECHERCHEH , et CHERCHER , XLOOKUP propose plusieurs avantages clés .



Message introuvable

Lorsque XLOOKUP ne trouve pas de correspondance, il renvoie l'erreur # N / A, comme les autres fonctions de correspondance dans Excel. Contrairement aux autres fonctions de correspondance, XLOOKUP prend en charge un argument facultatif appelé pas trouvé qui peut être utilisé pour remplacer l'erreur # N / A lorsqu'elle apparaîtrait autrement. Valeurs typiques pour pas trouvé peut être «Non trouvé», «Aucune correspondance», «Aucun résultat», etc. Lors de la fourniture d'une valeur pour pas trouvé , mettez le texte entre guillemets ('').

Remarque: soyez prudent si vous fournissez une chaîne vide ('') pour not_found. Si aucune correspondance n'est trouvée, XLOOKUP n'affichera rien à la place de # N / A. Si vous voulez voir l'erreur # N / A lorsqu'aucune correspondance n'est trouvée, omettez complètement l'argument.



Type de match

Par défaut, XLOOKUP effectuera un correspondance exacte . Le comportement de correspondance est contrôlé par un argument facultatif appelé Type de match, qui a les options suivantes:

comment rendre une cellule négative dans Excel
Type de match Comportement
0 (par défaut) Correspondance exacte. Renvoie # N / A si aucune correspondance.
-1 Correspondance exacte ou élément plus petit suivant.
1 Correspondance exacte ou article plus grand suivant.
deux Match générique (*,?, ~)

Mode de recherche

Par défaut, XLOOKUP commencera la correspondance à partir de la première valeur de données. Le comportement de recherche est contrôlé par un argument facultatif appelé search_mode , qui fournit les options suivantes:

Mode de recherche Comportement
1 (par défaut) Rechercher à partir de la première valeur
-1 Recherche à partir de la dernière valeur (inverse)
deux Valeurs de recherche binaires triées par ordre croissant
-deux Valeurs de recherche binaires triées par ordre décroissant

Les recherches binaires sont très rapides, mais les données doivent être triées selon les besoins . Si les données ne sont pas triées correctement, une recherche binaire peut renvoyer des résultats non valides qui semblent parfaitement normaux.

Exemple # 1 - correspondance exacte de base

Par défaut, XLOOKUP effectuera une correspondance exacte. Dans l'exemple ci-dessous, XLOOKUP est utilisé pour récupérer les ventes en fonction d'une correspondance exacte sur le film. La formule en H5 est:

 
= XLOOKUP (H4,B5:B9,E5:E9)

XLOOKUP - exemple de correspondance exacte de base

Explication plus détaillée ici .

Exemple # 2 - correspondance approximative de base

Pour activer une correspondance approximative, indiquez une valeur pour l'argument «match_mode». Dans l'exemple ci-dessous, XLOOKUP est utilisé pour calculer une remise en fonction de la quantité, ce qui nécessite une correspondance approximative. La formule en F5 fournit -1 pour match_mode pour permettre une correspondance approximative avec le comportement 'correspondance exacte ou prochaine plus petite':

 
= XLOOKUP (E5,B5:B9,C5:C9,,-1)

XLOOKUP - exemple de correspondance approximative de base

Explication plus détaillée ici .

Exemple # 3 - plusieurs valeurs

XLOOKUP peut renvoyer plus d'une valeur en même temps pour la même correspondance. L'exemple ci-dessous montre comment XLOOKUP peut être configuré pour renvoyer trois valeurs correspondantes avec une seule formule. La formule en C5 est:

 
= XLOOKUP (B5,B8:B15,C8:E15)

XLOOKUP - exemple de valeurs multiples

Notez que le tableau de retour (C8: E15) comprend 3 colonnes: First, Last, Department. Les trois valeurs sont renvoyées et Jeu dans la plage C5: E5.

Exemple # 4 - Recherche bidirectionnelle

XLOOKUP peut être utilisé pour effectuer une recherche bidirectionnelle, en nidification un XLOOKUP dans un autre. Dans l'exemple ci-dessous, le XLOOKUP «interne» récupère une ligne entière (toutes les valeurs pour Glass), qui est transmise au XLOOKUP «externe» comme tableau de retour. Le XLOOKUP externe trouve le groupe approprié (B) et renvoie la valeur correspondante (17,25) comme résultat final.

 
= XLOOKUP (I6,C4:F4, XLOOKUP (I5,B5:B9,C5:F9))

XLOOKUP - exemple de recherche bidirectionnelle

Plus de détails ici .

Exemple # 5 - message introuvable

Comme les autres fonctions de recherche, si XLOOKUP ne trouve pas de valeur, il renvoie l'erreur # N / A. Pour afficher un message personnalisé au lieu de # N / A, indiquez une valeur pour l'argument facultatif «introuvable», entre guillemets («»). Par exemple, pour afficher 'Non trouvé' lorsqu'aucun film correspondant n'est trouvé, en fonction de la feuille de calcul ci-dessous, utilisez:

 
= XLOOKUP (H4,B5:B9,E5:E9,'Not found')

XLOOKUP - exemple introuvable

Vous pouvez personnaliser ce message comme vous le souhaitez: 'Aucune correspondance', 'Film introuvable', etc.

convertir une date julienne en date grégorienne dans Excel

Exemple # 6 - Critères complexes

Avec la possibilité de gérer les tableaux de manière native, XLOOKUP peut être utilisé avec des critères complexes. Dans l'exemple ci-dessous, XLOOKUP correspond au premier enregistrement où: compte commence par «x» et la région est 'est' et le mois n'est pas avril:

 
= XLOOKUP (1,( LEFT (B5:B16)='x')*(C5:C16='east')* NOT ( MONTH (D5:D16)=4),B5:E16)

XLOOKUP - exemple de critères complexes

Détails: (1) exemple simple , (deux) exemple plus complexe .

Avantages de XLOOKUP

XLOOKUP offre plusieurs avantages importants, en particulier par rapport à VLOOKUP:

  • XLOOKUP peut rechercher des données à droite ou à gauche des valeurs de recherche
  • XLOOKUP peut renvoyer plusieurs résultats (exemple n ° 3 ci-dessus)
  • XLOOKUP est par défaut une correspondance exacte (VLOOKUP par défaut est approximatif)
  • XLOOKUP peut fonctionner avec des données verticales et horizontales
  • XLOOKUP peut effectuer une recherche inversée (du dernier au premier)
  • XLOOKUP peut renvoyer la totalité lignes ou colonnes , pas juste une valeur
  • XLOOKUP peut fonctionner avec tableaux nativement pour appliquer des critères complexes

Remarques

  1. XLOOKUP peut fonctionner avec des tableaux verticaux et horizontaux.
  2. XLOOKUP renverra # N / A si la valeur de recherche n'est pas trouvée.
  3. Le lookup_array doit avoir une dimension compatible avec le return_array argument, sinon XLOOKUP renverra #VALUE!
  4. Si XLOOKUP est utilisé entre les classeurs, les deux classeurs doivent être ouverts, sinon XLOOKUP renverra #REF !.
  5. Comme le Fonction INDEX , XLOOKUP renvoie un référence par conséquent.
XLOOKUP est une nouvelle fonction disponible dans Excel 365 seul.


^