Comment utiliser la fonction XLOOKUP dans Excel 2016

Excel 2016 pour les abonnés Office 365 sur Windows et Mac prend désormais en charge une nouvelle fonction XLOOKUP, présentée comme un remplacement considérablement plus simple et plus polyvalent pour la fonction de recherche verticale très populaire (mais souvent décriée), VLOOKUP (je ne sais pas ce que le X dans XLOOKUP signifie ; extensif, peut-être ?).

Pour ceux d'entre vous qui ne connaissent pas encore RECHERCHEV (considérée comme la troisième fonction la plus utilisée juste après SUM et MOYENNE), cette fonction recherche verticalement par ligne dans la colonne la plus à gauche d'une table de recherche désignée de haut en bas jusqu'à ce qu'elle trouve une valeur dans un colonne de recherche désignée par un numéro de décalage qui correspond ou dépasse celui que vous recherchez. Bien qu'extrêmement utile pour localiser des éléments particuliers dans une longue liste ou colonne d'une table de données dans votre feuille de calcul, la fonction RECHERCHEV a plusieurs limitations non partagées par cette nouvelle fonction de recherche, comme RECHERCHEX :

• Par défaut, la recherche de correspondances exactes pour votre valeur de recherche dans la plage de recherche
• Peut rechercher à la fois verticalement (par ligne) et horizontalement (par colonne) dans un tableau, supprimant ainsi le besoin d'utiliser la fonction RECHERCHEH lors d'une recherche horizontale par colonne
• Peut rechercher à gauche ou à droite afin que la plage de recherche dans votre table de recherche n'ait pas à se trouver dans une colonne à gauche de celle désignée comme plage de retour pour que la fonction fonctionne
• Lorsque la valeur par défaut de correspondance exacte est utilisée, fonctionne même lorsque les valeurs de la plage de recherche ne sont pas triées dans un ordre particulier
• Peut rechercher de la ligne du bas vers le haut dans la plage du tableau de recherche, en utilisant un argument de mode de recherche facultatif

La fonction XLOOKUP a cinq arguments possibles, dont les trois premiers sont obligatoires et les deux derniers facultatifs, en utilisant la syntaxe suivante :

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

L' argument obligatoire lookup_value désigne la valeur ou l'élément que vous recherchez. L' argument de tableau look_up requis désigne la plage de cellules à rechercher pour cette valeur de recherche, et l' argument return_array désigne la plage de cellules contenant la valeur que vous souhaitez renvoyer lorsqu'Excel trouve une correspondance exacte.

* Gardez à l'esprit lors de la désignation des arguments lookup_array et return_array dans votre fonction XLOOKUP, les deux plages doivent être de longueur égale, sinon Excel renverra la #VALUE ! erreur dans ta formule. C'est d'autant plus la raison pour laquelle vous utilisez des noms de plage ou des noms de colonne d'une table de données désignée lors de la définition de ces arguments plutôt que de les signaler ou de saisir leurs références de cellule .

L' argument facultatif match_mode peut contenir l'une des quatre valeurs suivantes :

• 0 pour une correspondance exacte (la valeur par défaut, comme lorsqu'aucun argument match_mode n'est désigné)
• -1 pour la correspondance exacte ou la valeur immédiatement inférieure
• 1 pour la correspondance exacte ou la valeur immédiatement supérieure
• 2 pour une correspondance partielle utilisant des caractères génériques joints à la référence de cellule dans l' argument lookup_value

L' argument facultatif search_mode peut contenir l'une des quatre valeurs suivantes :

• 1 pour rechercher du premier au dernier, c'est-à-dire de haut en bas (valeur par défaut, comme lorsqu'aucun argument search_mode n'est désigné)
• -1 pour rechercher du dernier au premier, c'est-à-dire de bas en haut
• 2 pour une recherche binaire par ordre croissant
• -2 pour la recherche binaire par ordre décroissant

La meilleure façon de comprendre la puissance et la polyvalence de la nouvelle fonction XLOOKUP est de la voir en action dans une feuille de calcul Excel. Dans la figure suivante, j'ai une feuille de calcul avec un simple tableau de données de vente 2019 organisé par pays. Pour utiliser XLOOKUP pour renvoyer le total des ventes de ce tableau dans la cellule E4 en fonction du pays que vous entrez dans la cellule D4 de la feuille de calcul, procédez comme suit :

Positionnez le curseur de cellule dans la cellule E4 de la feuille de calcul

Cliquez sur l'option Recherche et référence dans l'onglet Formules, puis sur RECHERCHEX près du bas du menu déroulant pour ouvrir sa boîte de dialogue Arguments de fonction.

Cliquez sur la cellule D4 de la feuille de calcul pour entrer sa référence de cellule dans la zone de texte de l'argument Lookup_value.

Appuyez sur Tab pour sélectionner la zone de texte de l'argument Lookup_array, puis cliquez sur la cellule A4 et maintenez la touche Maj enfoncée tout en appuyant sur Ctrl-flèche vers le bas pour sélectionner A4:A8 comme plage à rechercher (car la plage A3:B8 est définie comme un tableau de données Excel, Table1[Pays] apparaît dans la zone de texte à la place de la plage A4:A8).

Appuyez sur Tab pour sélectionner la zone de texte de l'argument Return_array, puis cliquez sur la cellule B4 et maintenez la touche Maj enfoncée tout en appuyant sur Ctrl-flèche vers le bas pour sélectionner B4:B8 comme plage contenant les valeurs à renvoyer en fonction des résultats de la recherche (qui apparaît comme Table1[Total Sales] dans la zone de texte).

Cliquez sur OK pour entrer la formule XLOOKUP dans la cellule E4.

Création d'une formule avec XLOOKUP dans la cellule E4 qui renvoie les ventes en fonction du pays entré dans la cellule D4.

Excel entre la formule XLOOKUP dans la cellule E4 de la feuille de calcul et renvoie 4900 comme résultat car le Costa Rica est actuellement entré dans la cellule de recherche D4 et comme vous pouvez le voir dans le tableau des ventes 2019, il s'agit bien du total des ventes réalisées pour ce pays.

Étant donné que XLOOKUP fonctionne de droite à gauche aussi bien que de gauche à droite, vous pouvez également utiliser cette fonction pour renvoyer le pays à partir de cette table de ventes en fonction d'un chiffre de ventes particulier. La figure suivante vous montre comment procéder. Cette fois, vous créez la formule XLOOKUP dans la cellule D4 et désignez la valeur entrée dans la cellule E4 (11 000, dans ce cas) comme argument lookup_value.

De plus, vous entrez -1 comme argument match_mode pour remplacer la valeur par défaut de la correspondance exacte de la fonction afin qu'Excel renvoie le pays avec une correspondance exacte avec la valeur des ventes saisie dans la cellule de recherche E4 ou celle avec les ventes totales immédiatement inférieures (Mexique avec 10 000 $ dans ce cas car il n'y a pas de pays dans ce tableau avec 11 000 $ de ventes totales). Sans désigner un argument match_mode pour cette formule, Excel renverrait #NA comme résultat, car il n'y a pas de correspondance exacte à 11 000 $ dans cette table de ventes.

Création d'une formule avec XLOOKUP dans la cellule D4 qui renvoie le pays en fonction des ventes saisies dans la cellule E4

Parce que la fonction XLOOKUP est tout aussi confortable pour la recherche horizontale par colonne que pour la recherche verticale par ligne, vous pouvez l'utiliser pour créer une formule qui effectue une recherche bidirectionnelle (remplaçant la nécessité de créer une formule qui combine les fonctions INDEX et MATCH comme autrefois). La figure suivante, contenant le tableau du calendrier de production 2019 pour les numéros de pièce, AB-100 à AB-103 pour les mois d'avril à décembre, vous montre comment procéder.

Création d'une formule avec des fonctions XLOOKUP imbriquées pour renvoyer le nombre d'unités produites pour une pièce au cours d'un mois particulier

Dans la cellule B12, j'ai créé la formule suivante :

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

Cette formule commence par définir une fonction XLOOKUP qui recherche verticalement par ligne une correspondance exacte avec l'entrée de pièce faite dans la cellule nommée part_lookup (cellule B10, dans ce cas) dans la plage de cellules $A$3:$A$6 de la table de production . Notez, cependant, que l'argument return_array pour cette fonction LOOKUP d'origine est lui-même une deuxième fonction XLOOKUP.

Cette deuxième fonction XLOOKUP imbriquée recherche dans la plage de cellules $B$2:$J$2 horizontalement par colonne une correspondance exacte avec l'entrée de date effectuée dans la cellule nommée date_lookup (cellule B11, dans ce cas). L'argument return_array pour cette seconde fonction XLOOKUP imbriquée est $B$3:$J$6, la plage de cellules de toutes les valeurs de production dans la table.

La façon dont cette formule fonctionne est qu'Excel calcule d'abord le résultat de la deuxième fonction XLOOKUP imbriquée en effectuant une recherche horizontale qui, dans ce cas, renvoie le tableau dans la plage de cellules D3: D6 de la colonne Jun-19 (avec les valeurs : 438, 153, 306 et 779) comme résultat. Ce résultat, à son tour, devient l'argument return_array pour la fonction XLOOKUP d'origine qui effectue une recherche verticale par ligne pour une correspondance exacte avec l'entrée de numéro de pièce effectuée dans la cellule B11 (nommée part_lookup). Étant donné que, dans cet exemple, cette cellule part_lookup contient AB-102, la formule renvoie uniquement la valeur de production du 19 juin, 306, à partir du résultat de la deuxième fonction XLOOKUP suivante.

Voilà! Un premier aperçu de XLOOKUP, une nouvelle fonction de recherche puissante, polyvalente et assez facile à utiliser qui peut non seulement effectuer les recherches à valeur unique effectuées par les fonctions RECHERCHEV et RECHERCHEH, mais également les recherches de valeurs bidirectionnelles effectuées en combinant les INDEX et MATCH fonctionnent également.

* Malheureusement, la fonction XLOOKUP n'est pas rétrocompatible avec les versions antérieures de Microsoft Excel qui ne prennent en charge que les fonctions VLOOKUP et HLOOKUP ou compatible avec les versions actuelles qui ne l'incluent pas encore comme l'une de leurs fonctions de recherche, telles que Excel 2019 et Excel Online . Cela signifie que si vous partagez un classeur contenant des formules XLOOKUP avec des collègues ou des clients qui utilisent une version d'Excel qui n'inclut pas cette nouvelle fonction de recherche, toutes ces formules renverront #NAME ? valeurs d'erreur lorsqu'ils ouvrent sa feuille de calcul.