Comment utiliser la fonction Excel XLOOKUP -

Sommaire

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

Objectif

Rechercher des valeurs dans la plage ou le tableau

Valeur de retour

Correspondance des valeurs du tableau de retour

Syntaxe

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Arguments

  • lookup - La valeur de recherche.
  • lookup_array - Le tableau ou la plage à rechercher.
  • return_array - Le tableau ou la plage à renvoyer.
  • not_found - (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 plus grande suivante, 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 RECHERCHEV. 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 telles que RECHERCHEV, RECHERCHEH et RECHERCHE, XLOOKUP offre 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é not_found qui peut être utilisé pour remplacer l'erreur # N / A lorsqu'elle apparaîtrait autrement. Les valeurs typiques de not_found peuvent être "Not found", "No match", "No result", etc. Lorsque vous fournissez une valeur pour not_found, 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 souhaitez 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 une correspondance exacte. Le comportement de correspondance est contrôlé par un argument facultatif appelé match_type, qui a les options suivantes:

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.
2 Correspondance 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)
2 Valeurs de recherche binaires triées par ordre croissant
-2 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)

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)

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)

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

Exemple # 4 - Recherche bidirectionnelle

XLOOKUP peut être utilisé pour effectuer une recherche bidirectionnelle, en imbriquant un XLOOKUP dans un autre. Dans l'exemple ci-dessous, la XLOOKUP "interne" récupère une ligne entière (toutes les valeurs pour Glass), qui est transmise à la 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))

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

Vous pouvez personnaliser ce message comme vous le souhaitez: "Aucune correspondance", "Film non trouvé", etc.

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ù: le 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)

Détails: (1) exemple simple, (2) 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 des lignes ou des colonnes entières, pas seulement une valeur
  • XLOOKUP peut travailler avec des tableaux de manière native 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 l' argument return_array , sinon XLOOKUP renverra #VALUE!
  4. Si XLOOKUP est utilisé entre les classeurs, les deux classeurs doivent être ouverts, sinon XLOOKUP renverra #REF !.
  5. Comme la fonction INDEX, XLOOKUP renvoie une référence en conséquence.

Vidéos connexes

Exemple de base de XLOOKUP Dans cette vidéo, nous allons configurer la fonction XLOOKUP avec un exemple de base. En fonction du nom de la ville, nous récupérerons le pays et la population. Correspondance approximative XLOOKUP de base Dans cette vidéo, nous allons configurer la fonction XLOOKUP pour effectuer une correspondance approximative afin de calculer une remise basée sur la quantité. XLOOKUP avec logique booléenne Dans cette vidéo, nous verrons comment utiliser la fonction XLOOKUP avec une logique booléenne pour appliquer plusieurs critères. XLOOKUP avec plusieurs valeurs de recherche Dans cette vidéo, nous allons configurer XLOOKUP pour renvoyer plusieurs valeurs dans un tableau dynamique, en fournissant une plage de valeurs de recherche au lieu d'une seule valeur de recherche.

Articles intéressants...