La fonction VLOOKUP a toujours été l’une des fonctions les plus puissantes d’Excel. Elle 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 de n’importe quelle autre colonne.
Contenu
Comment fonctionne XLOOKUP
La fonction XLOOKUP est beaucoup plus facile à utiliser que la fonction VLOOKUP, car au lieu de spécifier une valeur pour la colonne des résultats, vous pouvez spécifier la gamme entière.
La fonction permet également de rechercher à la fois dans une colonne et une ligne, en situant la valeur à l’intersection des cellules.
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 voulez extraire une valeur
- match_mode (facultatif): Sélectionnez une correspondance exacte (0), une correspondance exacte ou la plus petite valeur suivante (-1), ou une correspondance avec un joker (2).
- search_mode (facultatif): Choisissez si vous souhaitez effectuer une recherche en commençant par le premier élément de la colonne (1), le dernier élément de la colonne (-1), une recherche binaire ascendante (2) ou une 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 soumises par les représentants commerciaux, comprenant l’article, le nombre d’unités, le coût et la vente totale.
- Si vous souhaitez trouver la première vente de la liste soumise par un représentant spécifique, vous pouvez créer une fonction XLOOKUP qui recherche un nom dans la colonne Rep. La fonction retournera le résultat de la colonne Total. La fonction XLOOKUP pour cela est :
=XLOOKUP(I2,C2:C44,G2:G44,0,1)
- I2: Points à l’ordre du jour Nom du représentant cellule de recherche
- C2:C44: C’est la colonne Rep, qui est le tableau de recherche
- G2:G33: C’est 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 Saisissez et tapez le nom d’un commercial, la cellule Résultat total vous montrera le premier résultat dans le tableau pour ce commercial.
- Si vous voulez rechercher la vente la plus récente (puisque le tableau est ordonné par date dans l’ordre inverse), changez le dernier argument XLOOKUP en -1qui lancera la recherche à partir de la dernière cellule du tableau de recherche et vous fournira le résultat à la place.
- Cet exemple montre une recherche similaire que vous pourriez effectuer avec une fonction VLOOKUP en utilisant la colonne Rep comme première colonne du tableau de recherche. Cependant, XLOOKUP vous permet de rechercher n’importe quelle colonne dans les deux sens. Par exemple, si vous voulez trouver le représentant qui a vendu la première commande de classeur de l’année, vous devez utiliser la fonction XLOOKUP suivante :
=XLOOKUP(I2,D2:D44,C2:C44,0,1)
- D2: Pointer vers la cellule de recherche d’articles
- D2:D44: C’est la colonne Item, qui est le tableau de recherche
- C2:C44: C’est 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 représentant qui a vendu la première commande de classeur de l’année.
Effectuer un jumelage vertical et horizontal avec XLOOKUP
Une autre capacité de XLOOKUP dont VLOOKUP n’est pas capable est la possibilité d’effectuer une recherche à la fois verticale et horizontale, ce qui signifie que vous pouvez rechercher un article dans une colonne, et aussi sur une ligne.
Cette double fonction de recherche remplace efficacement les autres fonctions d’Excel comme INDEX, MATCH ou HLOOKUP.
- Dans l’exemple de feuille de calcul suivant, les ventes de chaque représentant sont réparties par trimestre. Si vous vouliez voir le chiffre d’affaires du troisième trimestre pour un commercial spécifique, sans la fonction XLOOKUP, ce type de recherche serait difficile.
- Avec la fonction XLOOKUP, ce type de recherche est facile. Grâce à la fonction XLOOKUP suivante, vous pouvez rechercher les ventes du troisième trimestre pour un représentant spécifique :
=XLOOKUP(J2,B2:B42,XLOOKUP(K2,C1:H1,C2:H42))
- J2: Pointer vers la cellule de recherche Rep
- B2:B42: C’est la colonne Item, qui est le tableau de recherche des colonnes
- K2: Points vers la cellule de recherche du quartier
- C1:H1: Voici le tableau de recherche par rangée
- C2:H42: Voici le tableau de recherche du montant en dollars pour chaque trimestre
Cette fonction XLOOKUP imbriquée identifie d’abord le représentant des ventes, et la fonction XLOOKUP imbriquée identifie le trimestre souhaité. La valeur de retour sera la cellule où ces deux éléments s’interceptent.
- Le résultat de cette formule est le quart de la rémunération du représentant portant le nom de Thompson.
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 voulez tester la fonction vous-même, vous pouvez devenir un initié du bureau. Sélectionnez Dossier > Comptepuis sélectionnez le Initié au bureau pour vous inscrire.
Une fois que vous aurez rejoint le programme Office Insider, votre version d’Excel installée recevra toutes les dernières mises à jour, et vous pourrez commencer à utiliser la fonction XLOOKUP.
