Exceller

Fonction Excel XLOOKUP

Excel Xlookup Function

Fonction Excel XLOOKUPSommaire

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





Objet Rechercher des valeurs dans une plage ou un 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.
  • tableau_recherche - Le tableau ou la plage à rechercher.
  • tableau_retour - Le tableau ou la plage à renvoyer.
  • pas trouvé - [optionnel] Valeur à retourner 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.
  • mode_recherche - [optionnel] 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 valeur 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. Lorsque vous fournissez une valeur pour pas trouvé , placez 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'une correspondance n'est pas 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 prochain élément plus petit.
1 Correspondance exacte ou prochain élément plus grand.
2 Correspondance générique (*,?, ~)

Mode de recherche

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

Mode de recherche Comportement
1 (par défaut) Recherche à partir de la première valeur
-1 Recherche à partir de la dernière valeur (inverse)
2 Valeurs de recherche binaire triées par ordre croissant
-2 Valeurs de recherche binaire 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 invalides 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 Movie. La formule dans 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, fournissez une valeur pour l'argument 'match_mode'. Dans l'exemple ci-dessous, XLOOKUP est utilisé pour calculer une remise basée sur la quantité, ce qui nécessite une correspondance approximative. La formule dans F5 fournit -1 pour match_mode pour permettre une correspondance approximative avec le comportement « correspondance exacte ou le plus petit suivant » :

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

XLOOKUP - exemple de correspondance approximative de base

Explication plus détaillée ici .

Exemple #3 - valeurs multiples

XLOOKUP peut renvoyer plusieurs valeurs à la fois 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 imbrication un XLOOKUP dans un autre. Dans l'exemple ci-dessous, le XLOOKUP « intérieur » récupère une ligne entière (toutes les valeurs pour Glass), qui est transmise au XLOOKUP « extérieur » en tant que 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 RECHERCHEX ne trouve pas de valeur, il renvoie l'erreur #N/A. Pour afficher un message personnalisé au lieu de #N/A, fournissez une valeur pour l'argument facultatif 'not found', 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 à votre guise : « Pas de 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ù : 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)

XLOOKUP - exemple de critères complexes

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

Avantages XLOOKUP

XLOOKUP offre plusieurs avantages importants, notamment par rapport à VLOOKUP :

  • XLOOKUP peut rechercher des données à droite ou à gauche des valeurs de recherche
  • XLOOKUP peut renvoyer plusieurs résultats (exemple #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 l'intégralité lignes ou colonnes , pas une seule valeur
  • XLOOKUP peut fonctionner avec tableaux en natif 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. Les tableau_recherche doit avoir une dimension compatible avec la tableau_retour argument, sinon XLOOKUP renverra #VALUE!
  4. Si RECHERCHEXX est utilisé entre des classeurs, les deux classeurs doivent être ouverts, sinon RECHERCHEX 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.


^