La fonction VLOOKUP a toujours été l'une des fonctions les plus puissantes d'Excel. Il vous permet de rechercher des valeurs dans la première colonne d'un tableau et de renvoyer des valeurs à partir des champs de droite. Mais Excel dispose également d'une fonction appelée XLOOKUP, qui vous permet de rechercher une valeur dans n'importe quelle colonne ou ligne et de renvoyer des données à partir de n'importe quelle autre colonne.
Comment XLOOKUP fonctionne
La fonction XLOOKUP est beaucoup plus facile à utiliser que la fonction VLOOKUP, car au lieu de spécifier une valeur pour la colonne de résultats, vous pouvez spécifier toute la plage.
La fonction vous permet également de rechercher à la fois une colonne et une ligne, en localisant la valeur à la cellule d'intersection.
Les paramètres de la fonction XLOOKUP sont les suivants:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: La valeur que vous souhaitez rechercher
- lookup_array: Le tableau (colonne) que vous souhaitez rechercher
- return_array: Le résultat (colonne) dont vous souhaitez récupérer une valeur
- match_mode (facultatif): sélectionnez une correspondance exacte (0), une correspondance exacte ou la plus petite valeur suivante (-1) ou une correspondance générique (2).
- search_mode (facultatif): sélectionnez si vous souhaitez rechercher en commençant par le premier élément de la colonne (1), le dernier élément de la colonne (-1), la recherche binaire ascendante (2) ou recherche binaire descendante (-2).
Voici quelques-unes des recherches les plus courantes que vous pouvez effectuer avec la fonction XLOOKUP.
Comment rechercher un seul résultat à l'aide de XLOOKUP
La façon la plus simple d'utiliser XLOOKUP est de rechercher un seul résultat en utilisant un point de données d'une colonne.
-
Cet exemple de feuille de calcul est une liste de commandes envoyées par les commerciaux, y compris l'article, le nombre d'unités, le coût et le total des ventes.
Image -
Si vous souhaitez trouver la première vente dans la liste soumise par un commercial spécifique, vous pouvez créer une fonction XLOOKUP qui recherche un nom dans la colonne Rep. La fonction renverra le résultat de la colonne Total. La fonction XLOOKUP pour cela est:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Pointe vers la Rep Name search cell
- C2:C44: il s'agit de la colonne Rep, qui est le tableau de recherche
- G2:G33: il s'agit de la colonne Total, qui est le tableau de retour
- 0: sélectionne une correspondance exacte
- 1: Sélectionne le premier match dans les résultats
-
Lorsque vous appuyez sur Entrée et saisissez le nom d'un commercial, la cellule Résultat total affiche le premier résultat du tableau pour ce commercial.
Image -
Si vous souhaitez rechercher la vente la plus récente (puisque le tableau est trié par date dans l'ordre inverse), remplacez le dernier argument XLOOKUP par - 1, qui commencera la recherche à partir de la dernière cellule du tableau de recherche et vous fournit ce résultat à la place.
Image -
Cet exemple montre une recherche similaire que vous pourriez effectuer avec une fonction VLOOKUP en utilisant la colonne Rep comme première colonne de la table de recherche. Cependant, XLOOKUP vous permet de rechercher n'importe quelle colonne dans les deux sens. Par exemple, si vous voulez trouver le commercial qui a vendu la première commande de classeur de l'année, vous utiliserez la fonction RECHERCHEX suivante:
=XRECHERCHE(I2, D2:D44, C2:C44, 0, 1)
- D2: pointe vers la cellule de recherche d'articles
- D2:D44: il s'agit de la colonne Item, qui est le tableau de recherche
- C2:C44: il s'agit de la colonne Rep, qui est le tableau de retour à gauche du tableau de recherche
- 0: sélectionne une correspondance exacte
- 1: Sélectionne le premier match dans les résultats
-
Cette fois, le résultat sera le nom du commercial qui a vendu la première commande de classeur de l'année.
Image
Effectuer une correspondance verticale et horizontale avec XLOOKUP
Une autre capacité de XLOOKUP dont VLOOKUP n'est pas capable est la possibilité d'effectuer une recherche verticale et horizontale, ce qui signifie que vous pouvez rechercher un élément dans une colonne et sur une ligne également.
Cette fonction de double recherche remplace efficacement les autres fonctions Excel telles que INDEX, MATCH ou HLOOKUP.
-
Dans l'exemple de feuille de calcul suivant, les ventes de chaque commercial sont réparties par trimestre. Si vous vouliez voir les ventes du troisième trimestre d'un commercial spécifique, sans la fonction XLOOKUP, ce type de recherche serait difficile.
Image -
Avec la fonction XLOOKUP, ce type de recherche est facile. À l'aide de la fonction XLOOKUP suivante, vous pouvez rechercher les ventes du troisième trimestre d'un commercial spécifique:
=RECHERCHEX(J2, B2:B42, RECHERCHEX(K2, C1:H1, C2:H42))
- J2: pointe vers la cellule de recherche Rep
- B2:B42: il s'agit de la colonne Item, qui est le tableau de recherche de colonne
- K2: pointe vers la cellule de recherche Trimestre
- C1:H1: Ceci est le tableau de recherche de lignes
- C2:H42: il s'agit du tableau de recherche pour le montant en dollars de chaque trimestre
Cette fonction XLOOKUP imbriquée identifie d'abord le commercial, et la fonction XLOOKUP suivante identifie le trimestre souhaité. La valeur de retour sera la cellule où ces deux interceptent.
-
Le résultat de cette formule est le premier trimestre des revenus du représentant portant le nom de Thompson.
Image
Utilisation de la fonction XLOOKUP
La fonction XLOOKUP n'est disponible que pour les abonnés Office Insider, mais sera bientôt déployée pour tous les abonnés Microsoft 365.
Si vous souhaitez tester la fonction vous-même, vous pouvez devenir un Office Insider. Sélectionnez File > Account, puis sélectionnez le menu déroulant Office Insider pour vous abonner.
Une fois que vous aurez rejoint le programme Office Insider, votre version installée d'Excel recevra toutes les dernières mises à jour et vous pourrez commencer à utiliser la fonction XLOOKUP.
