Comment utiliser xlookup avec plusieurs critères dans Excel

Une idée fausse sur la fonction xlookup dans Microsoft Excel est que vous ne pouvez saisir une condition unique que lorsque vous recherchez une valeur dans votre ensemble de données, où, en fait, vous pouvez rechercher des valeurs en fonction de plusieurs critères. De plus, il existe deux méthodes que vous pouvez utiliser, chacune différant en complexité et en polyvalence.

Si vous travaillez dans l'application Excel Desktop sur un PC ou un Mac, vous devez utiliser Excel 2021 ou version ultérieure (y compris Excel pour Microsoft 365) pour utiliser la fonction XLookup. Il est également facilement disponible dans Excel pour le Web et sur la tablette Excel et les applications mobiles.

La syntaxe Xlookup et l'exemple de critère à un seul critère

Bien que la syntaxe de la fonction xlookup semble initialement assez compliquée, elle suit en fait un ordre logique:

=XLOOKUP(a,b,c,d,e,f)

où

• un (requis) est la valeur de recherche,
• b (requis) est le tableau de recherche,
• c (requis) est le tableau de retour,
• d (Facultatif) est le texte à retourner si la valeur de recherche (un) ne se trouve pas dans le tableau de recherche (b),
• e (Facultatif) est le mode de correspondance (0 = correspondance exacte (par défaut), -1 = correspondance exacte ou un élément plus petit suivant, 1 = correspondance exacte ou un élément plus grand suivant, 2 = correspondance générique), et
• f (Facultatif) est le mode de recherche (1 = d'abord pour durer (par défaut), -1 = dernier à la première, 2 = recherche binaire où b est dans l'ordre croissant, -2 = recherche binaire où b est en ordre décroissant).

Ainsi, dans l'exemple ci-dessous, après avoir choisi un ID dans la liste déroulante des données de validation dans la cellule E1, tapant:

=XLOOKUP(E1,T_Scores(ID),T_Scores(Score),"No match",0,1)

dans la cellule E2 renvoie un score de 51.

C'est parce que:

• E1 La cellule contenant la valeur (1323) à rechercher dans le tableau,
• T_scores (id) dit à Excel de rechercher la valeur dans la colonne ID de la table T_Score,
• T_score (score) dit à Excel de renvoyer la valeur correspondante de la colonne de score du même tableau,
• « Pas de match » est ce qu'il faut retourner si la valeur de recherche n'est pas n'importe où dans le tableau de recherche,
• 0 dit à Excel de retourner un match exact, et
• 1 Indique à Excel de rechercher la valeur de recherche en haut de la colonne ID vers le bas.

J'aurais pu omettre les deux derniers arguments de la formule ci-dessus, en tant que correspondance exacte et une recherche de première à la première à défaut.

Exemple 1: plusieurs critères xlookup avec logique booléenne

La façon dont vous utilisez la fonction xlookup change lorsque vous avez plus d'un critère.

Imaginez que vous possédez une marque qui vend six boissons dans six pays différents, et vous avez attribué un gestionnaire à chaque boisson dans chaque pays.

Votre objectif est de créer une recherche qui vous permet de prendre une boisson et un pays, et renvoie le nom du manager et le nombre de ventes.

Cependant, comme je vous l'ai montré ci-dessus, Xlookup fonctionne généralement avec une seule valeur de recherche, donc cela semble initialement une tâche impossible. Pour surmonter cette limitation apparente, vous devez utiliser la logique booléenne pour créer un tableau de recherche temporaire.

Voici la formule qui fera l'affaire. Je l'ai divisé en lignes distinctes, il est donc plus facile de lire:

=XLOOKUP(
1,
(T_Managers(Drink)=G1)*(T_Managers(Country)=G2),
T_Managers((Manager):(Sales)),
"No result"
)

Regardons d'abord l'argument ble tableau de recherche:

(T_Managers(Drink)=G1)*(T_Managers(Country)=G2)

Tout d'abord, la formule vérifie la colonne de boisson de la table T_Managers pour une correspondance contre la valeur en G1, qui est le café. En conséquence, il renvoie le tableau temporaire suivant:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE...}

Parce que les six premières valeurs de la colonne de boissons sont le jus de pomme (faux), les six boissons suivantes sont du café (vrai), et les lignes restantes de la colonne sont d'autres boissons (fausse). Cette capture d'écran visualise le processus en cours dans les coulisses:

Ensuite, il vérifie la colonne country du même tableau pour une correspondance contre la valeur en G2, qui est l'Espagne, retournant temporairement:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE...}

car les cinquième et onzième valeurs correspondent au critère du pays. Voici ce que fait Excel en arrière-plan:

Maintenant, parce que nous avons multiplié les deux tableaux temporaires, cette opération transforme les valeurs réelles en 1 et fausses valeurs en 0:

{0;0;0;0;0;0;1;1;1;1;1;1;0...}

et

{0;0;0;0;1;0;0;0;0;0;1;0...}

Cela crée un seul tableau de recherche, où les premiers nombres de chaque tableau sont multipliés, les seconds nombres de chaque tableau sont multipliés, et ainsi de suite.

{0;0;0;0;0;0;0;0;0;0;1;0...}

Ainsi, le onzième appariement est le premier qui retourne 1.

Maintenant, revenons à l'argument unla valeur de recherche:

=XLOOKUP(1

Ici, nous disons à Excel de trouver le numéro 1 dans le tableau de recherche. Comme nous l'avons vu précédemment, la onzième valeur dans le tableau combiné est la première qui correspond à cette valeur de recherche, donc xlookup trouve la onzième valeur dans chaque colonne du tableau de retour (argument c):

T_Managers((Manager):(Sales))

Dans cet exemple, la onzième valeur dans la colonne Manager est Olivia, et la onzième valeur dans la colonne de vente est de 346.

L'argument final (d), qui est le texte à retourner si la valeur de recherche n'est pas trouvée dans le tableau, n'est pas en jeu car il y a un match. En fait, si vous utilisez la validation des données pour créer une liste déroulante de choix, vous pouvez omettre complètement l'argument sans match.

De plus, puisque vous vouliez retourner une correspondance exacte tout en regardant du haut en bas, vous n'aviez pas besoin d'inclure des arguments e ou f dans la formule.

Dans l'exemple ci-dessus, nous avons utilisé le symbole égal (=) pour générer les critères. Cependant, si vous recherchez des valeurs numériques, vous pouvez également utiliser des opérateurs de comparaison, tels que> (supérieur à). De plus, au lieu de créer une multiplication entre chaque tableau temporaire pour renvoyer un résultat qui correspond tous Les critères, vous pouvez utiliser le symbole d'addition (+) pour retourner le premier résultat qui se réunit au moins un

des critères.

Exemple 2: plusieurs critères xlookup avec concaténation

Une autre façon de saisir plusieurs critères dans votre formule xlookup consiste à concaténer (combiner) toutes les valeurs de recherche en utilisant le symbole ampersand (&), et à faire de même avec tous les tableaux de recherche.

En utilisant le même scénario que l'exemple 1, votre objectif est de retourner le nom d'un gestionnaire et le total des ventes lorsque vous prenez une boisson et un pays.

=XLOOKUP(
G1&G2,
T_Managers(Drink)&T_Managers(Country),
T_Managers((Manager):(Sales)),
"No result"
)

Voici la formule que vous visez, divisé en lignes séparées pour une compréhension plus facile: Tout d'abord, l'argumentun

G1&G2

la valeur de recherche, contient deux références cellulaires, séparées par un ampère et:

En effet, cela génère la chaîne de recherche de cafés, car le café et l'Espagne sont les valeurs dans les cellules référencées, et l'ampère et les relie sans aucun espace. Ensuite, pour l'argumentb

T_Managers(Drink)&T_Managers(Country)

le tableau de recherche, la même logique s'applique, chaque tableau étant rejoint par le symbole AmperSand:

Ainsi, nous disons à Excel de trouver la chaîne Coffeespain dans le tableau DrinkCountry de la table T_Managers, où la première chaîne est Apple Juiceaustralia, la deuxième chaîne est Apple Juicecanada, etc. Ensuite, argumentc

T_Managers((Manager):(Sales))

le tableau de retour, fonctionne de la même manière que dans n'importe quelle formule xlookup, indiquant à Excel de renvoyer les valeurs correspondantes du gestionnaire et des colonnes de vente. Enfin, si la chaîne de recherche n'existe pas dans la table T_Managers, argument d

=XLOOKUP(G1&G2,T_Managers(Drink)&T_Managers(Country),T_Managers((Manager):(Sales)),"No result")

dit à Excel de renvoyer les mots «pas de résultat».

Cependant, dans ce cas, il existe, donc Excel renvoie Olivia de la colonne Manager et 346 de la colonne de vente. Étant donné que chaque combinaison de country de boissons n'apparaît qu'une seule fois dans la table, vous n'avez pas besoin d'inclure des arguments e ouf

---

. En effet, la fonction xlookup par défaut est une correspondance exacte, et peu importe que vous recherchiez en haut ou en bas de la table.