Le nouveau XLOOKUP d’Excel remplacera VLOOKUP, fournissant un remplacement puissant à l’une des fonctions les plus populaires d’Excel. Cette nouvelle fonction résout certaines des limitations de RECHERCHEV et possède des fonctionnalités supplémentaires. Voici ce que vous devez savoir.
Table des matières
Qu’est-ce que XLOOKUP?
La nouvelle fonction XLOOKUP a des solutions pour certaines des plus grandes limitations de VLOOKUP. De plus, il remplace également HLOOKUP. Par exemple, XLOOKUP peut regarder à sa gauche, correspond par défaut à une correspondance exacte et vous permet de spécifier une plage de cellules au lieu d’un numéro de colonne. VLOOKUP n’est pas aussi simple à utiliser ni aussi polyvalent. Nous allons vous montrer comment tout cela fonctionne.
Pour le moment, XLOOKUP n’est disponible que pour les utilisateurs du programme Insiders. Tout le monde peut rejoignez le programme Insiders pour accéder aux dernières fonctionnalités d’Excel dès qu’elles sont disponibles. Microsoft commencera bientôt à le déployer auprès de tous les utilisateurs d’Office 365.
Comment utiliser la fonction XLOOKUP
Plongeons-nous directement dans un exemple de XLOOKUP en action. Prenons l’exemple de données ci-dessous. Nous voulons renvoyer le département de la colonne F pour chaque ID de la colonne A.
Il s’agit d’un exemple classique de recherche de correspondance exacte. La fonction XLOOKUP ne nécessite que trois informations.
L’image ci-dessous montre XLOOKUP avec six arguments, mais seuls les trois premiers sont nécessaires pour une correspondance exacte. Alors concentrons-nous sur eux:
Lookup_value: ce que vous recherchez.
Lookup_array: Où chercher.
Return_array: la plage contenant la valeur à renvoyer.
La formule suivante fonctionnera pour cet exemple: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ F $ 2: $ F $ 8)
Explorons maintenant quelques avantages de XLOOKUP par rapport à VLOOKUP ici.
Plus de numéro d’index de colonne
Le tristement célèbre troisième argument de VLOOKUP était de spécifier le numéro de colonne des informations à renvoyer à partir d’un tableau de table. Ce n’est plus un problème car XLOOKUP vous permet de sélectionner la plage à partir de laquelle retourner (colonne F dans cet exemple).
Et n’oubliez pas, XLOOKUP peut afficher les données à gauche de la cellule sélectionnée, contrairement à VLOOKUP. Plus d’informations ci-dessous.
Vous n’avez également plus le problème d’une formule cassée lorsque de nouvelles colonnes sont insérées. Si cela se produisait dans votre feuille de calcul, la plage de retour s’ajusterait automatiquement.
La correspondance exacte est la valeur par défaut
Lors de l’apprentissage de VLOOKUP, il était toujours déroutant de savoir pourquoi vous deviez spécifier une correspondance exacte.
Heureusement, XLOOKUP utilise par défaut une correspondance exacte – la raison bien plus courante d’utiliser une formule de recherche). Cela réduit la nécessité de répondre à ce cinquième argument et garantit moins d’erreurs par les utilisateurs novices dans la formule.
Donc, en bref, XLOOKUP pose moins de questions que VLOOKUP, est plus convivial et est également plus durable.
XLOOKUP peut regarder vers la gauche
La possibilité de sélectionner une plage de recherche rend XLOOKUP plus polyvalent que VLOOKUP. Avec XLOOKUP, l’ordre des colonnes de la table n’a pas d’importance.
VLOOKUP a été contraint en recherchant la colonne la plus à gauche d’une table, puis en revenant d’un nombre spécifié de colonnes vers la droite.
Dans l’exemple ci-dessous, nous devons rechercher un identifiant (colonne E) et renvoyer le nom de la personne (colonne D).
La formule suivante peut y parvenir: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8)
Que faire si non trouvé
Les utilisateurs des fonctions de recherche connaissent très bien le message d’erreur # N / A qui les salue lorsque leur RECHERCHEV ou leur fonction MATCH ne peut pas trouver ce dont il a besoin. Et souvent, il y a une raison logique à cela.
Par conséquent, les utilisateurs recherchent rapidement comment masquer cette erreur car elle n’est ni correcte ni utile. Et, bien sûr, il existe des moyens de le faire.
XLOOKUP est livré avec son propre argument intégré «si non trouvé» pour gérer ces erreurs. Voyons cela en action avec l’exemple précédent, mais avec un ID mal saisi.
La formule suivante affichera le texte « ID incorrect » au lieu du message d’erreur: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8, « ID incorrect »)
Utilisation de XLOOKUP pour une recherche de plage
Bien que cela ne soit pas aussi courant que la correspondance exacte, une utilisation très efficace d’une formule de recherche consiste à rechercher une valeur dans des plages. Prenons l’exemple suivant. Nous souhaitons restituer la remise en fonction du montant dépensé.
Cette fois, nous ne recherchons pas une valeur spécifique. Nous devons savoir où les valeurs de la colonne B se situent dans les fourchettes de la colonne E. Cela déterminera la remise gagnée.
XLOOKUP a un cinquième argument facultatif (rappelez-vous, il correspond par défaut à la correspondance exacte) nommé mode de correspondance.
Vous pouvez voir que XLOOKUP a de plus grandes capacités avec des correspondances approximatives que celle de VLOOKUP.
Il est possible de trouver la correspondance la plus proche inférieure à (-1) ou la plus proche supérieure à (1) la valeur recherchée. Il existe également une option pour utiliser des caractères génériques (2) tels que le? ou la *. Ce paramètre n’est pas activé par défaut comme il l’était avec RECHERCHEV.
La formule de cet exemple renvoie la valeur la plus proche inférieure à la valeur recherchée si aucune correspondance exacte n’est trouvée: = XLOOKUP (B2, $ E $ 3: $ E $ 7, $ F $ 3: $ F $ 7 ,, – 1)
Cependant, il y a une erreur dans la cellule C7 où l’erreur # N / A est renvoyée (l’argument «si non trouvé» n’a pas été utilisé). Cela aurait dû renvoyer une remise de 0% car dépenser 64 n’atteint les critères d’aucune remise.
Un autre avantage de la fonction XLOOKUP est qu’elle ne nécessite pas que la plage de recherche soit dans l’ordre croissant comme le fait VLOOKUP.
Entrez une nouvelle ligne en bas de la table de recherche, puis ouvrez la formule. Développez la plage utilisée en cliquant et en faisant glisser les coins.
La formule corrige immédiatement l’erreur. Ce n’est pas un problème d’avoir le «0» au bas de la plage.
Personnellement, je trierais toujours la table par la colonne de recherche. Avoir «0» en bas me rendrait fou. Mais le fait que la formule n’ait pas cassé est génial.
XLOOKUP remplace également la fonction HLOOKUP
Comme mentionné, la fonction XLOOKUP est également là pour remplacer HLOOKUP. Une fonction pour en remplacer deux. Excellent!
La fonction HLOOKUP est la recherche horizontale, utilisée pour rechercher le long des lignes.
Pas aussi connu que son frère VLOOKUP, mais utile pour des exemples comme ci-dessous où les en-têtes sont dans la colonne A et les données le long des lignes 4 et 5.
XLOOKUP peut regarder dans les deux sens – colonnes descendantes et également le long des lignes. Nous n’avons plus besoin de deux fonctions différentes.
Dans cet exemple, la formule est utilisée pour renvoyer la valeur des ventes relative au nom de la cellule A2. Il regarde le long de la ligne 4 pour trouver le nom et renvoie la valeur de la ligne 5: = XLOOKUP (A2, B4: E4, B5: E5)
XLOOKUP peut regarder de bas en haut
En règle générale, vous devez rechercher une liste pour trouver la première (souvent la seule) occurrence d’une valeur. XLOOKUP a un sixième argument nommé mode de recherche. Cela nous permet de changer la recherche pour qu’elle commence par le bas et de rechercher une liste pour trouver la dernière occurrence d’une valeur à la place.
Dans l’exemple ci-dessous, nous aimerions trouver le niveau de stock pour chaque produit dans la colonne A.
La table de recherche est dans l’ordre des dates et il existe plusieurs contrôles de stock par produit. Nous voulons renvoyer le niveau de stock à partir de la dernière fois qu’il a été vérifié (dernière occurrence de l’ID de produit).
Le sixième argument de la fonction XLOOKUP fournit quatre options. Nous sommes intéressés par l’option «Rechercher du dernier au premier».
La formule complétée est affichée ici: = XLOOKUP (A2, $ E $ 2: $ E $ 9, $ F $ 2: $ F $ 9 ,,, – 1)
Dans cette formule, les quatrième et cinquième arguments ont été ignorés. Elle est facultative et nous voulions la valeur par défaut d’une correspondance exacte.
Round-Up
La fonction XLOOKUP est le successeur très attendu aux fonctions RECHERCHEV et RECHERCHEH.
Divers exemples ont été utilisés dans cet article pour démontrer les avantages de XLOOKUP. L’une d’elles est que XLOOKUP peut être utilisé sur des feuilles, des classeurs et également avec des tableaux. Les exemples ont été simplifiés dans l’article pour nous aider à comprendre.
En raison de tableaux dynamiques introduits dans Excel bientôt, il peut également renvoyer une plage de valeurs. C’est certainement quelque chose qui mérite d’être approfondi.
Les jours de RECHERCHEV sont comptés. XLOOKUP est là et sera bientôt la formule de recherche de facto.