Le nouveau XLOOKUP d’Excel remplacera VLOOKUP, offrant ainsi un remplacement puissant à l’une des fonctions les plus populaires d’Excel. Cette nouvelle fonction résout certaines des limitations de VLOOKUP et dispose de fonctionnalités supplémentaires. Voici ce que vous devez savoir.
Contenu
Qu’est-ce que XLOOKUP ?
La nouvelle fonction XLOOKUP offre des solutions pour certaines des plus grandes limitations de VLOOKUP. De plus, elle remplace également HLOOKUP. Par exemple, XLOOKUP peut regarder à sa gauche, la correspondance exacte par défaut, et vous permet de spécifier une plage de cellules au lieu d’un numéro de colonne. VLOOKUP n’est pas aussi facile à utiliser ni aussi polyvalent. Nous allons vous montrer comment tout cela fonctionne.
Pour l’instant, XLOOKUP n’est disponible que pour les utilisateurs du programme Insiders. Tout le monde peut rejoindre le programme Insiders pour accéder aux nouvelles fonctionnalités d’Excel dès qu’elles seront disponibles. Microsoft commencera bientôt à le déployer pour tous les utilisateurs d’Office 365.
Comment utiliser la fonction XLOOKUP
Plongeons directement dans l’action avec un exemple de XLOOKUP en action. Prenez les données de l’exemple ci-dessous. Nous voulons renvoyer le service de la colonne F pour chaque ID de la colonne A.
Il s’agit d’un exemple classique de recherche de concordance 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. Concentrons-nous donc sur eux :
- Lookup_value : Ce que vous recherchez.
- Lookup_array : Où chercher.
- Return_array : la plage contenant la valeur à retourner.
La formule suivante fonctionnera pour cet exemple : =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Voyons maintenant quelques avantages de XLOOKUP par rapport à VLOOKUP.
Plus de numéro d’index de colonne
Le troisième argument tristement célèbre de VLOOKUP était de spécifier le numéro de colonne des informations à renvoyer à partir d’un tableau. Ce n’est plus un problème car XLOOKUP vous permet de sélectionner la plage de retour (colonne F dans cet exemple).
Et n’oubliez pas que XLOOKUP peut visualiser les données à gauche de la cellule sélectionnée, contrairement à VLOOKUP. Plus d’informations à ce sujet ci-dessous.
Vous n’avez également plus le problème de la formule brisée lorsque de nouvelles colonnes sont insérées. Si cela se produisait dans votre tableur, la fourchette de rendement s’adapterait automatiquement.
La correspondance exacte est la valeur par défaut
Il était toujours déroutant, en apprenant VLOOKUP, de devoir préciser pourquoi on voulait une correspondance exacte.
Heureusement, XLOOKUP propose par défaut une correspondance exacte – la raison la plus courante pour utiliser une formule de recherche). Cela réduit la nécessité de répondre à ce cinquième argument et garantit moins d’erreurs de la part des utilisateurs novices dans la formule.
En bref, XLOOKUP pose moins de questions que VLOOKUP, est plus convivial et est également plus durable.
XLOOKUP peut regarder à gauche
Le fait de pouvoir sélectionner une plage de recherche rend XLOOKUP plus polyvalent que VLOOKUP. Avec XLOOKUP, l’ordre des colonnes du tableau n’a pas d’importance.
VLOOKUP a été contraint en recherchant la colonne la plus à gauche d’un tableau et en revenant d’un nombre déterminé 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 permet d’y parvenir : =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Que faire en cas d’intrusion
Les utilisateurs des fonctions de recherche connaissent bien le message d’erreur #N/A qui les accueille lorsque leur fonction VLOOKUP ou MATCH ne trouve pas ce dont elle a besoin. Et il y a souvent une raison logique à cela.
Par conséquent, les utilisateurs cherchent rapidement comment dissimuler cette erreur parce qu’elle n’est pas correcte ou utile. Et, bien sûr, il existe des moyens de le faire.
XLOOKUP est livré avec son propre argument « si non trouvé » intégré pour gérer de telles erreurs. Voyons cela en action avec l’exemple précédent, mais avec une ID mal tapée.
La formule suivante affichera le texte « ID incorrect » au lieu du message d’erreur : =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")
Utilisation de XLOOKUP pour une recherche de gamme
Bien qu’elle ne soit pas aussi courante que la correspondance exacte, une utilisation très efficace d’une formule de recherche consiste à chercher une valeur dans des fourchettes. Prenons l’exemple suivant. Nous voulons rendre 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 réduction obtenue.
XLOOKUP dispose d’un cinquième argument optionnel (souvenez-vous, il s’agit par défaut de la correspondance exacte) appelé mode de correspondance.
Vous pouvez voir que XLOOKUP a de plus grandes capacités avec des correspondances approximatives que celles 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 est également possible d’utiliser des caractères génériques (2) tels que le ? ou le *. Ce paramètre n’est pas activé par défaut comme il l’était avec VLOOKUP.
La formule de cet exemple renvoie la valeur la plus proche inférieure à celle recherchée si une correspondance exacte n’est pas 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é). Cette erreur aurait dû entraîner une remise de 0 %, car les dépenses de 64 n’atteignent pas les critères de remise.
Un autre avantage de la fonction XLOOKUP est qu’elle n’exige pas que la plage de recherche soit en ordre croissant comme le fait VLOOKUP.
Saisissez une nouvelle ligne en bas du tableau de recherche, puis ouvrez la formule. Élargissez la plage utilisée en cliquant et en faisant glisser les coins.
La formule corrige immédiatement l’erreur. Le fait d’avoir le « 0 » dans le bas de la fourchette ne pose pas de problème.
Personnellement, je classerais toujours le tableau en fonction de la colonne de recherche. Avoir un « 0 » en bas me rendrait fou. Mais le fait que la formule n’ait pas été rompue est brillant.
XLOOKUP remplace aussi 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 la recherche le long des lignes.
Pas aussi connu que son frère VLOOKUP, mais utile pour des exemples comme celui ci-dessous où les en-têtes sont dans la colonne A, et les données se trouvent le long des lignes 4 et 5.
XLOOKUP peut regarder dans les deux sens – vers le bas des colonnes et aussi le long des lignes. Nous n’avons plus besoin de deux fonctions différentes.
Dans cet exemple, la formule est utilisée pour retourner la valeur de vente relative au nom dans la cellule A2. Elle cherche le nom le long de la ligne 4 et renvoie la valeur de la ligne 5 : =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP peut regarder de bas en haut
En général, il faut parcourir une liste pour trouver la première (et souvent la seule) occurrence d’une valeur. XLOOKUP a un sixième argument appelé mode de recherche. Cela nous permet de changer le mode de recherche pour commencer en bas et de chercher une liste pour trouver la dernière occurrence d’une valeur à la place.
Dans l’exemple ci-dessous, nous aimerions connaître le niveau de stock de chaque produit dans la colonne A.
La table de consultation est classée par date, et il y a plusieurs contrôles de stock par produit. Nous voulons renvoyer le niveau de stock de la dernière fois qu’il a été vérifié (dernière occurrence de l’ID du produit).
Le sixième argument de la fonction XLOOKUP propose quatre options. Nous sommes intéressés par l’utilisation de l’option « Recherche du dernier au premier ».
La formule complétée est présentée ici : =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
Dans cette formule, le quatrième et le cinquième argument ont été ignorés. C’est facultatif, et nous voulions une correspondance exacte par défaut.
Tour d’horizon
La fonction XLOOKUP est le successeur tant attendu des fonctions VLOOKUP et HLOOKUP.
Divers exemples ont été utilisés dans cet article pour démontrer les avantages de XLOOKUP. L’un d’entre eux est que XLOOKUP peut être utilisé sur des feuilles, des cahiers d’exercices et aussi avec des tableaux. Les exemples ont été simplifiés dans l’article afin de faciliter notre compréhension.
En raison de l’introduction prochaine de tableaux dynamiques dans Excel, il peut également renvoyer une série de valeurs. C’est un point qui mérite d’être approfondi.
Les jours de VLOOKUP sont numérotés. XLOOKUP est là et sera bientôt la formule de recherche de facto.
https://www.howtogeek.com/439286/how-to-use-the-xlookup-function-in-microsoft-excel/
