Name: Tremplin Numérique
Price range: $$$

En tant que passionné d'Excel de longue date, j'ai toujours aimé utiliser RECHERCHEV, l'une des fonctions de recherche les plus connues d'Excel. Cependant, l'introduction de XLOOKUP par Microsoft en 2019 a tout changé. Une fois que j'ai réalisé à quel point XLOOKUP était utile, j'ai su que je ne regarderais jamais en arrière.

J'utiliserai des références directes à des cellules pour illustrer mes arguments dans cet article, car elles sont plus claires que les références structurées. Je ne parlerai pas non plus explicitement de HLOOKUP car, mis à part son orientation, il fonctionne exactement de la même manière que VLOOKUP.

Sommaire

Les syntaxes : XLOOKUP et VLOOKUP

Avant d'expliquer pourquoi je préfère XLOOKUP à VLOOKUP, je vais vous montrer comment ils fonctionnent.

RECHERCHEXL

XLOOKUP a six arguments :

=XLOOKUP(a,b,c,d,e,f)

où

un (obligatoire) est la valeur de recherche,
b (obligatoire) est le tableau de recherche,
c (obligatoire) est le tableau de retour,
d (facultatif) est le texte à renvoyer si la valeur de recherche (un) est introuvable dans le tableau de recherche (b),
e (facultatif) est le mode de correspondance, et
f (facultatif) est le mode de recherche.

Dans cet exemple, je souhaite qu'Excel recherche le numéro d'employé en fonction du nom dans la cellule H1 et renvoie le résultat dans la cellule H2.

Pour ce faire, je vais taper la formule suivante dans la cellule H2 :

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)

Dans ce cas, la cellule H1 contient la valeur qu'Excel doit rechercher (Mary), B2 à B12 sont l'endroit où cette valeur peut être trouvée (noms des employés), A2 à A12 est l'endroit d'où le résultat correspondant sera extrait (identifiants des employés ), et « Nom invalide » est ce que je veux qu'Excel renvoie si la valeur de recherche ne se trouve nulle part dans le tableau de recherche.

J'ai opté pour les valeurs par défaut pour les arguments e et f parce que je veux une correspondance exacte et je veux qu'Excel recherche à partir du haut du tableau de recherche (nous y reviendrons plus tard).

RECHERCHEV

Voici la syntaxe RECHERCHEV, qui comporte quatre arguments :

=VLOOKUP(a,b,c,d)

où

un (obligatoire) est la valeur de recherche,
b (obligatoire) est le tableau de recherche et de retour,
c (obligatoire) est le numéro d'index de la colonne, et
d (facultatif) est le mode match.

Dans cet exemple, je souhaite qu'Excel recherche la nationalité en fonction du numéro d'identification de l'employé dans la cellule H4 et renvoie le résultat dans la cellule H5.

Pour ce faire, dans la cellule H5, je vais taper

=VLOOKUP(H4,A2:E12,5,FALSE)

parce que la cellule H4 contient la valeur de recherche (ID 3264), les cellules A2 à E12 sont celles où Excel doit trouver cette valeur et le retour correspondant, la cinquième colonne (le pays) est le tableau dans lequel le résultat sera trouvé, et je veux une correspondance exacte (FAUX).

Pas besoin de compter les colonnes

Une différence clé entre VLOOKUP et XLOOKUP est que le premier m'oblige à spécifier un numéro d'index de colonne où le résultat sera trouvé, alors que le second ne le fait pas. En effet, RECHERCHEV combine les tableaux de recherche et de retour en un seul argument, alors que RECHERCHEXL les définit dans deux arguments distincts.

RECHERCHEV

Devoir spécifier le numéro d'index de la colonne dans RECHERCHEV peut entraîner plusieurs problèmes :

Il est facile de mal compter accidentellement les colonnes, surtout si votre tableau de recherche comporte des centaines de colonnes.
L'ajout ou la suppression de colonnes peut affecter l'exactitude du numéro d'index de la colonne.
Le tableau de recherche doit être la colonne la plus à gauche et le tableau de retour doit être à droite. Cela limite la polyvalence de RECHERCHEV.

RECHERCHEXL

D'un autre côté, XLOOKUP contient le tableau de recherche comme argument distinct, ce qui signifie que vous pouvez apprécier les avantages suivants :

Il n'y a pas de comptage ! Utilisez simplement votre souris pour sélectionner le tableau de retour lorsque vous arrivez à cette partie de votre formule. Cela permet de gagner du temps et améliore la précision.
Étant donné que le tableau renvoyé se trouve dans une plage de cellules spécifiée, la suppression ou l'ajout de colonnes à votre feuille de calcul n'affectera pas votre formule XLOOKUP.
Le tableau de retour peut se trouver de chaque côté du tableau de recherche, ce qui signifie que XLOOKUP est plus polyvalent que VLOOKUP.

Plus d'options de correspondance approximative

VLOOKUP et XLOOKUP peuvent renvoyer une correspondance exacte (la valeur correspondante exacte dans une ligne) et une correspondance approximative (une valeur correspondante proche).

RECHERCHEV

Plus précisément, la correspondance approximative de VLOOKUP (indiquée par TRUE dans la formule) recherche dans le tableau de recherche jusqu'à ce qu'elle trouve une valeur supérieure à la valeur de recherche. Il renvoie ensuite la valeur située une ligne plus haut à partir de là.

Prenons le score de 65 de l'étudiant D dans l'exemple ci-dessous. RECHERCHEV prend la valeur de recherche de 65, examine le tableau de recherche, trouve la première valeur supérieure à la valeur de recherche (dans ce cas, 70), puis renvoie la note de la ligne ci-dessus (note C).

Cela présente deux inconvénients. Tout d’abord, le tableau de recherche doit être répertorié par ordre croissant. Deuxièmement, je dois ajouter une ligne FAIL au tableau de recherche, car je n'ai pas la possibilité dans RECHERCHEV d'indiquer un argument qui ne correspond pas.

RECHERCHEXL

D'un autre côté, XLOOKUP propose trois alternatives à la correspondance approximative unidimensionnelle de VLOOKUP :

-1 : renvoie la valeur la plus petite suivante dans le tableau de recherche s'il n'y a pas de correspondance exacte.
1 : renvoie la valeur la plus grande suivante dans le tableau de recherche s'il n'y a pas de correspondance exacte.
2 : cela utilise des caractères génériques pour permettre des recherches plus flexibles.

Encore une fois, prenons l’étudiant D comme exemple. Avec un score de 65, Excel verra que la prochaine plus petite valeur dans le tableau de recherche est 60 et la deuxième plus grande valeur est 70. Étant donné que l'élève n'a pas encore atteint le seuil de la note B (70), j'ai besoin d'Excel pour prenez la valeur la plus petite suivante dans le tableau de recherche (60) pour renvoyer la note C à partir du tableau de retour. Alors je vais taper -1 comme option de correspondance dans la formule.

Cela signifie que le tableau de recherche n'a pas besoin d'être dans l'ordre : Excel parcourt l'ensemble du tableau de recherche pour trouver les valeurs supérieures ou inférieures les plus proches s'il n'y a pas de correspondance exacte. Je peux également omettre la ligne FAIL de mon tableau de recherche, car si le score d'un élève ne correspond à aucune note, je peux utiliser le quatrième argument de la syntaxe XLOOKUP pour renvoyer le mot FAIL.

Plus de modes de recherche (directions)

Pendant que RECHERCHEV effectue une recherche du premier au dernier, renvoyant la première valeur correspondante, XLOOKUP propose quatre options de recherche.

RECHERCHEV

Dans la plupart des scénarios de recherche, la recherche dans le tableau de recherche du premier au dernier renverra les résultats dont vous avez besoin. Par exemple, si vous disposez d'un répertoire de numéros de téléphone et de noms de personnes, utiliser RECHERCHEV pour trouver le numéro de téléphone en fonction du nom que vous avez saisi fonctionnera très bien, car le nom de cette personne n'apparaîtra probablement qu'une seule fois.

RECHERCHEXL

Cependant, XLOOKUP vous permet de choisir l'orientation de la recherche :

1 : Rechercher du premier au dernier
-1 : Recherche du dernier au premier
2 : Recherche binaire (avec le tableau de recherche par ordre croissant)
-2 Recherche binaire (avec le tableau de recherche par ordre décroissant)

L'avantage d'une recherche du dernier au premier est que vous pouvez trouver l'occurrence la plus récente d'une valeur dans un tableau de recherche répertorié par ordre de date. Pour y parvenir avec RECHERCHEV, vous devrez d'abord inverser l'ordre des données.

Définir la sortie d'erreur

Une fonctionnalité très utile de XLOOKUP est l'argument « si non trouvé », que RECHERCHEV n'a pas.

RECHERCHEV

Si une valeur n'est pas trouvée dans une formule de correspondance exacte RECHERCHEV, Excel renvoie le redoutable message d'erreur #N/A. Pour résoudre ce problème, j'ai toujours intégré les fonctions RECHERCHEV dans la fonction SIERREUR afin de pouvoir définir la sortie si RECHERCHEV ne trouve pas de correspondance.

=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE)," ")

Bien qu'il s'agisse d'une solution raisonnable, elle rend l'écriture de formules beaucoup plus complexe et peut masquer des problèmes susceptibles d'affecter la précision de votre analyse de données.

RECHERCHEXL

Puisque XLOOKUP est livré prêt à l'emploi avec un argument « si non trouvé », vous pouvez définir ce qui se passe si la valeur n'apparaît pas dans la recherche, vous évitant ainsi d'avoir à intégrer la formule dans SIERREUR.

Renvoyez un tableau renversé

L'une des propriétés les plus tenaces de VLOOKUP est qu'il ne peut renvoyer qu'une seule correspondance, alors que XLOOKUP peut renvoyer une plage.

RECHERCHEXL

Dans cet exemple, en tapant

=XLOOKUP(I1,A2:A7,B2:F7)

recherche la valeur dans la cellule I1 (dans ce cas, Quizpicable Me), recherche cette valeur dans les cellules A2 à A7 et renvoie toutes les valeurs correspondantes sous forme de tableau renversé.

RECHERCHEV

Si je devais essayer de reproduire cela en utilisant RECHERCHEV, je taperais

=VLOOKUP(I1,A2:F7,2:6)

mais cela renvoie un #REF ! erreur car le troisième argument (numéro d’index de colonne) ne peut être qu’un seul chiffre, pas une plage. Cela signifie que XLOOKUP est beaucoup plus adaptable, car il peut renvoyer soit une valeur unique, soit une plage, en fonction des paramètres que vous ajoutez à la formule.

Étant donné que les versions antérieures à 2019 d'Excel ne prennent pas en charge XLOOKUP, ne supprimez pas complètement VLOOKUP et HLOOKUP ! Il se peut que vous ayez encore besoin de les utiliser, par exemple si vous envoyez une feuille de calcul à quelqu'un qui n'a pas mis à niveau son package Office depuis quelques années.