in

Comment utiliser la fonction XLOOKUP dans Microsoft Excel

excel logo

Le nouveau XLOOKUP d’Excel remplacera RECHERCHEV, 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.

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 RECHERCHEH. 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. RECHERCHEV 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.

Exemple de données pour l'exemple XLOOKUP

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: Qu’est-ce que tu cherches.
  • Lookup_array: Où regarder.
  • Return_array: la plage contenant la valeur à renvoyer.

Informations requises par la fonction XLOOKUP

La formule suivante fonctionnera pour cet exemple: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

XLOOKUP pour une correspondance exacte

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 RECHERCHEV é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).

L'argument de numéro d'index de colonne de RECHERCHEV

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 colonne insérée ne rompt pas XLOOKUP

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).

Exemple de données pour une formule de recherche à gauche

La formule suivante peut y parvenir: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Fonction XLOOKUP renvoyant une valeur à sa gauche

Que faire si vous ne le trouvez pas

Les utilisateurs des fonctions de recherche sont très familiers avec 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 de telles 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,"Incorrect ID")

Texte alternatif s'il n'est pas trouvé avec XLOOKUP

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 obtenue.

Données de table pour une recherche de plage

XLOOKUP a un cinquième argument facultatif (rappelez-vous, il correspond par défaut à la correspondance exacte) nommé mode de correspondance.

Argument de mode de correspondance pour une recherche de plage

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)

Une recherche de gamme avec une erreur

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.

Corrigez l'erreur en élargissant la gamme utilisée

La formule corrige immédiatement l’erreur. Ce n’est pas un problème d’avoir le «0» au bas de la plage.

Erreur corrigée en développant la table de recherche

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 RECHERCHEH 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 directions – 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 comme remplacement de la fonction HLOOKUP

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 en 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 classée par date 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).

Exemple de données pour une recherche en arrière

Le sixième argument de la fonction XLOOKUP fournit quatre options. Nous sommes intéressés par l’option «Rechercher du dernier au premier».

Options du mode de recherche avec XLOOKUP

La formule complétée est affichée ici: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

XLOOKUP recherche de bas en haut une liste de valeurs

Dans cette formule, les quatrième et cinquième arguments ont été ignorés. C’est facultatif, 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.

À cause 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.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Comment verrouiller la position d’un graphique dans Excel

Comment déplacer ou copier du texte dans Microsoft Word sans affecter le presse-papiers

Comment déplacer ou copier du texte dans Microsoft Word sans affecter le presse-papiers