Le nouvel outil XLOOKUP d’Excel est destiné à remplacer VLOOKUP, offrant une alternative puissante à l’une des fonctions les plus utilisées d’Excel. Cette fonction inédite pallie certaines des limites de RECHERCHEV et intègre des capacités supplémentaires. Voici l’essentiel à savoir.
Qu’est-ce que XLOOKUP ?
La nouvelle fonction XLOOKUP a été conçue pour surmonter certaines des principales contraintes de VLOOKUP. De plus, elle prend également la place de HLOOKUP. Par exemple, XLOOKUP peut effectuer des recherches vers la gauche, réalise par défaut une correspondance exacte et autorise la sélection d’une plage de cellules plutôt qu’un numéro de colonne. VLOOKUP n’est ni aussi simple à manipuler ni aussi polyvalent. Nous allons vous expliquer comment tout cela fonctionne.
Actuellement, XLOOKUP n’est accessible qu’aux membres du programme Insiders. Vous pouvez rejoindre le programme Insiders pour accéder aux dernières nouveautés d’Excel dès leur sortie. Microsoft prévoit de la déployer sous peu auprès de tous les utilisateurs d’Office 365.
Comment utiliser la fonction XLOOKUP
Examinons directement un exemple concret d’utilisation de XLOOKUP. Considérons les données ci-dessous. Nous souhaitons récupérer le département figurant dans la colonne F pour chaque ID de la colonne A.
C’est un cas typique de recherche par correspondance exacte. La fonction XLOOKUP ne requiert que trois éléments d’information.
L’image ci-après illustre XLOOKUP avec six arguments, mais seuls les trois premiers sont nécessaires pour une correspondance précise. Concentrons-nous donc sur ceux-ci :
Lookup_value : la valeur que vous recherchez.
Lookup_array : l’endroit où effectuer la recherche.
Return_array : la plage contenant la valeur à extraire.
La formule suivante convient à cet exemple : = XLOOKUP(A2, $E$2:$E$8, $F$2:$F$8)
Explorons à présent quelques avantages de XLOOKUP par rapport à VLOOKUP.
Plus de numéro d’index de colonne
Le troisième argument de VLOOKUP, souvent source de confusion, consistait à indiquer le numéro de la colonne des données à récupérer depuis un tableau. Ce problème n’existe plus car XLOOKUP vous permet de sélectionner directement la plage à partir de laquelle effectuer le retour (colonne F dans notre exemple).
De plus, contrairement à VLOOKUP, XLOOKUP est capable d’afficher des données situées à gauche de la cellule ciblée. Plus de détails ci-dessous.
Vous n’avez plus non plus à craindre qu’une formule ne se brise en cas d’insertion de nouvelles colonnes. Si cela se produit dans votre feuille de calcul, la plage de retour s’ajustera automatiquement.
La correspondance exacte est la valeur par défaut
Lors de l’apprentissage de VLOOKUP, il était toujours déroutant de devoir spécifier une correspondance exacte.
Fort heureusement, XLOOKUP utilise par défaut une correspondance exacte – ce qui est bien plus souvent souhaité lors de l’utilisation d’une formule de recherche. Cela diminue la nécessité de renseigner ce cinquième argument et réduit les erreurs pour les utilisateurs novices.
En bref, XLOOKUP demande moins de paramètres que VLOOKUP, est plus simple à utiliser et plus fiable dans le temps.
XLOOKUP peut regarder vers la gauche
La capacité à choisir une plage de recherche rend XLOOKUP plus adaptable que VLOOKUP. Avec XLOOKUP, l’ordre des colonnes dans le tableau importe peu.
VLOOKUP était limitée, effectuant ses recherches dans la colonne la plus à gauche d’un tableau, puis retournant une valeur située un certain nombre de colonnes à droite.
Dans l’exemple ci-dessous, nous devons rechercher un identifiant (colonne E) et afficher le nom de la personne correspondante (colonne D).
La formule suivante peut atteindre cet objectif : = XLOOKUP(A2, $E$2:$E$8, $D$2:$D$8)
Que faire si la valeur n’est pas trouvée ?
Les utilisateurs des fonctions de recherche connaissent bien le message d’erreur #N/A, qui s’affiche lorsque RECHERCHEV ou MATCH ne trouvent pas ce qu’ils cherchent. Il existe souvent une raison logique à cela.
Ainsi, les utilisateurs cherchent souvent comment masquer cette erreur car elle n’est ni appropriée ni utile. Il existe, bien sûr, des moyens d’y parvenir.
XLOOKUP intègre un argument « si non trouvé » pour gérer ces erreurs. Examinons cela en détail avec l’exemple précédent, en utilisant cette fois un identifiant 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 par plage
Bien que moins répandue que la correspondance exacte, l’utilisation d’une formule de recherche pour trouver une valeur dans une plage est très pratique. Prenons l’exemple suivant : nous voulons afficher le niveau de remise en fonction du montant dépensé.
Cette fois, nous ne recherchons pas une valeur spécifique. Il nous faut déterminer où les valeurs de la colonne B se situent dans les plages définies dans la colonne E, afin d’établir la remise correspondante.
XLOOKUP possède un cinquième argument optionnel (il effectue par défaut une correspondance exacte) appelé mode de correspondance.
Vous voyez que XLOOKUP est plus performant que VLOOKUP en matière de correspondances approximatives.
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 aussi une option permettant d’utiliser des caractères génériques (2) tels que ? ou *. Ce paramètre n’est pas activé par défaut comme c’était le cas avec RECHERCHEV.
La formule de cet exemple retourne 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ù #N/A est renvoyé (l’argument « si non trouvé » n’a pas été utilisé). Une remise de 0 % aurait dû être affichée, car une dépense de 64 n’atteint le critère d’aucune remise.
Un autre atout de la fonction XLOOKUP est qu’elle n’impose pas à la plage de recherche d’être triée par ordre croissant, comme c’est le cas pour VLOOKUP.
Ajoutez une nouvelle ligne en bas du tableau de recherche, puis modifiez la formule. Développez la plage utilisée en cliquant et en tirant les coins.
La formule corrige immédiatement l’erreur. Il n’est pas gênant d’avoir le « 0 » à la fin de la plage.
Personnellement, je trierais toujours le tableau en fonction de la colonne de recherche. Avoir « 0 » à la fin me gênerait. Mais le fait que la formule n’ait pas été altérée est un vrai plus.
XLOOKUP remplace aussi la fonction HLOOKUP
Comme nous l’avons mentionné, la fonction XLOOKUP est également conçue pour se substituer à HLOOKUP. Une seule fonction pour en remplacer deux, c’est parfait !
La fonction HLOOKUP sert à la recherche horizontale, en parcourant des lignes.
Moins connue que sa cousine VLOOKUP, elle s’avère utile dans des cas comme celui ci-dessous où les en-têtes sont dans la colonne A et les données sur les lignes 4 et 5.
XLOOKUP peut effectuer des recherches dans les deux sens – le long des colonnes et le long des lignes. Nous n’avons plus besoin de deux fonctions distinctes.
Dans cet exemple, la formule est employée pour récupérer la valeur des ventes relative au nom de la cellule A2. Elle explore la ligne 4 pour trouver le nom et affiche la valeur de la ligne 5 : = XLOOKUP(A2, B4:E4, B5:E5)
XLOOKUP peut regarder de bas en haut
Généralement, il faut parcourir une liste afin de localiser la première (souvent la seule) occurrence d’une valeur. XLOOKUP possède un sixième argument appelé mode de recherche. Celui-ci nous permet d’inverser le sens de la recherche, en partant du bas, afin de trouver la dernière occurrence d’une valeur au sein d’une liste.
Dans l’exemple ci-dessous, nous aimerions connaître le niveau de stock pour chaque produit de la colonne A.
Le tableau de recherche est ordonné par date, et il existe plusieurs enregistrements de stock par produit. Nous souhaitons obtenir le niveau de stock au moment de la dernière vérification (dernière occurrence de l’ID du produit).
Le sixième argument de la fonction XLOOKUP offre quatre options. L’option « Rechercher du dernier au premier » nous intéresse.
Voici la formule complétée : = XLOOKUP(A2, $E$2:$E$9, $F$2:$F$9, , , -1)
Dans cette formule, les quatrième et cinquième arguments ont été omis. Ils sont optionnels, et nous souhaitions conserver la valeur par défaut de correspondance exacte.
Récapitulatif
La fonction XLOOKUP est le successeur très attendu aux fonctions RECHERCHEV et RECHERCHEH.
Divers exemples ont été employés dans cet article pour exposer les avantages de XLOOKUP. L’un d’entre eux est que XLOOKUP peut être utilisé sur des feuilles, des classeurs et aussi avec des tableaux. Les exemples ont été simplifiés dans l’article pour en faciliter la compréhension.
Grâce aux tableaux dynamiques introduits dans Excel prochainement, elle sera aussi capable de renvoyer une plage de valeurs. C’est assurément un point à approfondir.
Les jours de RECHERCHEV sont comptés. XLOOKUP est arrivée, et deviendra bientôt la formule de recherche de référence.