XLOOKUP() vs. VLOOKUP() : Une comparaison pour les utilisateurs d'Excel

Syntaxe de XLOOKUP() et VLOOKUP()

Maintenant que vous avez une connaissance de base de ces deux fonctions, nous allons comprendre leur syntaxe et comment les utiliser dans des applications réelles. 

Syntaxe de VLOOKUP()

Examinons la syntaxe de VLOOKUP(). 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Dans la formule ci-dessus :

• lookup_value est la valeur que vous souhaitez rechercher. Il peut s'agir d'un nombre, d'un texte ou d'une référence à une cellule contenant la valeur recherchée.

• table_array est la plage de cellules qui contient les données. La première colonne de cette plage doit contenir l'adresse search_key.

• col_index_num est le numéro de la colonne de l'intervalle dont vous voulez extraire la valeur. 

• range_lookup est l'endroit où vous entrez une valeur logique (TRUE ou FALSE).

Syntaxe de XLOOKUP()

Examinons maintenant la syntaxe de xlookup(). Comme nous pouvons le constater, XLOOKUP() a plus d'arguments que VLOOKUP().

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Dans la formule ci-dessus :

• lookup_value est la valeur que vous souhaitez rechercher.

• lookup_array est la plage de cellules dans laquelle la valeur de référence est recherchée.

• return_array est la plage de cellules qui contient la valeur que vous souhaitez renvoyer.

• if_not_found (facultatif) renvoie une valeur si aucune correspondance n'est trouvée. Si la valeur n'est pas spécifiée, il renvoie #N/A.

• match_mode (facultatif) détermine le type de correspondance à effectuer. 0 correspond à la correspondance exacte (par défaut). -1 correspond à la correspondance exacte ou à l'élément inférieur suivant. 1 correspond à la correspondance exacte ou à l'élément supérieur suivant. 2 correspond à la correspondance par caractères génériques.

• search_mode (facultatif) détermine le mode de recherche à utiliser. 1 est utilisé pour effectuer une recherche du premier au dernier (par défaut). -1 est utilisé pour effectuer une recherche du dernier au premier. 2 est utilisé pour effectuer une recherche binaire dans l'ordre croissant. -2 est utilisé pour effectuer une recherche binaire dans l'ordre décroissant.

Principales différences : XLOOKUP() vs. VLOOKUP()

Examinons maintenant les principales différences entre les fonctions XLOOKUP() et VLOOKUP().

Mode de correspondance exacte 

XLOOKUP() est par défaut en mode de correspondance exacte, mais VLOOKUP() nécessite de spécifier FALSE pour une correspondance exacte.

Différence de correspondance exacte entre XLOOKUP() et VLOOKUP(). Source : Image par l'auteur.

Dans VLOOKUP(), je n'ai pas spécifié la valeur de range_lookup (4e argument), ce qui donne la correspondance la plus proche, ce qui n'est pas le résultat souhaité. XLOOKUP() En revanche, le moteur de recherche de l'ENT, par défaut, renvoie la correspondance exacte.

Direction de recherche

VLOOKUP() est limité à la recherche à droite de la première colonne du tableau sélectionné. Mais XLOOKUP() peut rechercher des valeurs dans n'importe quelle direction.

Différence de sens de recherche entre VLOOKUP() et XLOOKUP(). Source : Image par l'auteur.

Ici, VLOOKUP() n'a pas trouvé les notes car il ne peut rechercher que les données situées à droite de lookup_value (nom de l'étudiant). En comparaison, XLOOKUP() trouve les notes pour Robin car il peut effectuer une recherche dans les deux sens (gauche ou droite).

Références aux tableaux

Dans VLOOKUP(), vous devez définir l'ensemble de la plage de données (tableau) et spécifier le numéro de la colonne (indice de colonne) contenant le résultat souhaité (colonne de retour) dans une seule formule. XLOOKUP() est plus souple que cela. Il vous permet de définir des tableaux distincts pour la valeur de la recherche et les données que vous souhaitez renvoyer. 

Références de tableaux entre VLOOKUP() et XLOOKUP(). Source : Image par l'auteur.

Consultation horizontale

VLOOKUP() ne vous permet pas d'effectuer des recherches horizontales. Si vous souhaitez effectuer une recherche horizontale, vous devez utiliser HLOOKUP(). XLOOKUP() consolide ces deux fonctions car il effectue des recherches verticales et horizontales.

Différence de recherche horizontale entre VLOOKUP() et XLOOKUP(). Source : Image par l'auteur.

Gestion des insertions/suppressions de colonnes 

Les changements de colonne affectent VLOOKUP() à cause du code en dur column_index_num. Cependant, XLOOKUP() n'est pas affecté par les changements de colonnes. Il continue à fonctionner sans ajustement de la formule.

XLOOKUP() n'est pas affecté, tandis que VLOOKUP() échoue. Source : Image par l'auteur.

Tri et recherche

Vous pouvez trier VLOOKUP() par ordre croissant, mais cela entraînerait des difficultés lorsque vous traitez des données non triées. Toutefois, XLOOKUP() peut utiliser l'argument search_mode pour effectuer une recherche à la fois par ordre croissant et par ordre décroissant.

Gestion de l'insertion/suppression dans XLOOKUP() et VLOOKUP(). Source : Image par l'auteur.

Messages d'erreur personnalisés

VLOOKUP() affiche un signe d'erreur #N/A lorsqu'aucune correspondance n'est trouvée. XLOOKUP() dispose d'un paramètre optionnel if_not_found qui vous permet de personnaliser le texte de sortie si une valeur n'est pas trouvée. 

Personnalisation des messages d'erreur dans XLOOKUP() et VLOOKUP(). Source : Image par l'auteur.

Vous pouvez voir que VLOOKUP() n'a pas trouvé l'étudiant dans sa liste et a affiché une erreur #N/A. De même, la fonction XLOOKUP() n'a pas pu localiser le nom de l'étudiant. Cependant, contrairement à VLOOKUP(), XLOOKUP() renvoie un message spécifique lorsqu'il n'y a pas de correspondance.

Renvoi de plusieurs valeurs

VLOOKUP() ne peut renvoyer qu'une seule valeur à la fois. Mais XLOOKUP() peut extraire des valeurs de plusieurs colonnes simultanément. C'est pourquoi vous pouvez utiliser une formule XLOOKUP() au lieu de plusieurs formules VLOOKUP().

Renvoi de valeurs multiples avec XLOOKUP() et VLOOKUP(). Source : Image par l'auteur.

Mode de recherche

VLOOKUP() parcourt une liste depuis le début et ne renvoie que la première valeur qui correspond à ce que vous voulez. Mais XLOOKUP() peut parcourir une liste dans les deux sens (de haut en bas ou de bas en haut) et trouver rapidement des éléments dans de longues listes.

Différence entre VLOOKUP() et XLOOKUP(). Source : Image par l'auteur.

Ici, VLOOKUP() récupère la première occurrence, qui est celle du premier semestre. Cependant, avec XLOOKUP(), j'utilise le code de mode de recherche -1 pour effectuer une recherche de bas en haut.

Exemples pratiques et cas d'utilisation

Voyons maintenant quelques exemples pratiques et cas d'utilisation.

Utiliser une recherche sans avoir à réorganiser un tableau

Je dispose d'une liste de noms et de notes d'élèves, et je souhaite trouver la note de chaque élève en fonction de son nom. À cette fin, j'utiliserai les fonctions XLOOKUP() et VLOOKUP() pour vous montrer les différences. 

Lorsque j'utilise XLOOKUP(), j'entre la formule suivante :

=XLOOKUP(D8,B2:B5,A2:A5)

Dans cette formule :

• D8 contient la valeur de recherche qui est Charlie.

• B2:B5 contient les noms des étudiants. La fonction XLOOKUP() recherche la valeur de D8 à l'intérieur de cette plage.

• A2:A5 est l'intervalle qui contient les scores correspondant aux noms des étudiants. 

Lorsque j'utilise VLOOKUP(), j'entre la formule suivante :

=VLOOKUP(D4,A1:B5,1,0)

Dans cette formule :

• D4 contient la valeur de recherche qui est Charlie.

• A1:B5 permet d'effectuer des recherches dans toutes les colonnes(notes, nom de l' élève ).

• 1 renvoie la valeur de la 1ère colonne de l'intervalle (Scores).

• 0 permet d'obtenir la correspondance exacte.

VLOOKUP() renvoie une erreur #N/A mais XLOOKUP() la gère. Source : Image par l'auteur.

Comme vous pouvez le constater, VLOOKUP() affiche une erreur #N/A car il ne peut pas effectuer de recherche vers la gauche.

Création d'une sortie personnalisée lorsqu'une valeur n'est pas trouvée

J'ai une liste d'étudiants avec leurs identifiants, leurs noms et leurs notes. Lors de ma recherche, j'ai accidentellement saisi un numéro d'étudiant qui ne figurait pas sur la liste. Pour gérer un tel cas, je souhaite qu'un message personnalisé s'affiche lorsque l'identifiant de l'étudiant n'est pas trouvé. J'utiliserai les fonctions XLOOKUP() et VLOOKUP() pour vous montrer les différences. 

Lorsque j'utilise XLOOKUP(), j'entre la formule suivante :

(=XLOOKUP(E9, A2:A5, B2:B5, "Student not Found")) 

Dans cette formule :

• E9 contient la valeur à rechercher. Dans mon cas, il s'agit de 14256.

• A2:A5 est la plage de cellules contenant la valeur de référence (ID de l'étudiant).

• B2:B5 recherche le score de la ID 14526.

• Student not Found s'affiche si le score ne figure pas dans la liste. 

Lorsque j'utilise VLOOKUP(), j'entre la formule suivante :

(=VLOOKUP(E6, A2:C5,2,0)) 

Dans cette formule :

• E6 renvoie à l'identifiant de l' étudiant, ce qui correspond à ce que je recherche.

• A2:C5 fait référence à la plage qui contient toutes les données du tableau.

• 2 indique le numéro de la colonne à partir de laquelle les données seront extraites.

• 0 permet d'obtenir la correspondance exacte.

XLOOKUP() renvoie un message, mais pas VLOOKUP(). Source : Image par l'auteur.

Vous pouvez voir que 14256 n'existe pas dans la plage A2:A5, donc XLOOKUP() renvoie le message personnalisé Student not found . En revanche, VLOOKUP() provoque une erreur #N/A. 

Recherche à partir de la base

Je veux trouver le salaire de Sarah pour l'année la plus récente. À cette fin, j'utiliserai les fonctions XLOOKUP() et VLOOKUP() pour vous montrer les différences. 

Lorsque j'utilise XLOOKUP(), j'entre la formule suivante :

=XLOOKUP(F8,B2:B10,C2:C10,,,-1)

Dans cette formule :

• F8 contient la valeur de recherche Sarah.

• B2:B10 regarde dans le rayon d'action et cherche Sarah.

• C2:C10 récupère les données.

• -1 à partir du bas de la colonne. 

Lorsque j'utilise VLOOKUP(), j'entre la formule suivante :

=VLOOKUP(F4, B2:C10,2,0)

Dans cette formule :

• F4 contient la valeur de recherche Sarah.

• B2:C10 recherche la valeur de référence à l'intérieur de cette plage.

• 2 indique le numéro de la colonne Salaire à partir de laquelle les données seront extraites.

• 0 permet d'obtenir la correspondance exacte.

Différence entre VLOOKUP() et XLOOKUP() en mode recherche. Source : Image par l'auteur.

Vous pouvez voir que XLOOKUP() trouve le salaire souhaité pour l'année souhaitée, alors que VLOOKUP() n'affiche que la première valeur correspondante. J'ai pu le faire parce que XLOOKUP() me permet d'effectuer des recherches de bas en haut.

Performance et compatibilité

Comparons maintenant les performances et la compatibilité des deux fonctions. 

Performance dans les grands ensembles de données

VLOOKUP() peut être plus lent lorsqu'il s'agit de grands ensembles de données, en particulier si la colonne de recherche n'est pas triée et que l'argument de recherche d'intervalle est fixé à FALSE pour une correspondance exacte. En effet, VLOOKUP() parcourt l'ensemble des données de manière séquentielle jusqu'à ce qu'il trouve une correspondance, ce qui peut prendre beaucoup de temps pour les tableaux de grande taille. XLOOKUP() offre de meilleures performances avec les grands ensembles de données. Il peut traiter plus efficacement les correspondances exactes et effectuer des recherches horizontales et verticales sans tri. 

Compatibilité avec différentes versions d'Excel

Maintenant, vous devez penser à abandonner VLOOKUP() après avoir vu les avantages de XLOOKUP(). Mais permettez-moi de clarifier les choses : bien que XLOOKUP() soit une fonctionnalité excellente et beaucoup plus rapide, il y a quelques cas où elle n'est pas disponible. En effet, XLOOKUP() est une fonctionnalité plus récente qui ne fonctionne que dans Excel 2021 et Microsoft 365 (à partir de 2019). Si vous collaborez avec des personnes utilisant des versions plus anciennes d'Excel, vous devrez utiliser VLOOKUP() et d'autres fonctions au lieu de XLOOKUP().

Tableau récapitulatif

Créons un tableau récapitulatif pour faciliter la consultation. 

Réflexions finales

XLOOKUP() est plus performant que VLOOKUP(), en particulier lorsqu'il s'agit de traiter de grands ensembles de données ou de répondre à des exigences complexes en matière de recherche. Sa flexibilité, notamment la recherche bidirectionnelle, l'extraction de résultats multiples et l'adaptabilité aux changements de données, en fait un outil utile. Cependant, VLOOKUP() conserve sa place, en particulier pour les utilisateurs d'anciennes versions d'Excel ou pour ceux qui préfèrent une approche plus simple des recherches de base. Le meilleur choix dépend en fin de compte de vos besoins spécifiques et de votre version d'Excel.

Si vous êtes novice en matière d'Excel ou si vous souhaitez acquérir des bases solides, notre cours d' introduction à Excel et le cursus de compétences Excel Fundamentals constituent d'excellents points de départ pour maîtriser les notions de base.