Comment utiliser XLOOKUP() avec plusieurs critères dans Excel

XLOOKUP() dans Excel est actuellement la plus récente des fonctions de recherche (LOOKUP(), VLOOKUP(), HLOOKUP() et XLOOKUP()). Elle offre de nombreux avantages, des fonctionnalités étendues et une plus grande flexibilité.

Dans ce tutoriel, nous allons d'abord voir à quoi sert la fonction XLOOKUP() dans Excel et en quoi elle dépasse les anciennes fonctions de recherche ; ensuite, nous examinerons sa syntaxe de base, puis nous passerons au cœur du sujet : l'utilisation de XLOOKUP() avec plusieurs critères.

Si vous devez apprendre les fondamentaux d'Excel, le cours pour débutants Introduction to Excel est le bon point de départ.

Pourquoi utiliser XLOOKUP() dans Excel

La fonction XLOOKUP() parcourt une plage ou un tableau de données et renvoie l'élément correspondant à la première correspondance. Si aucune correspondance n'est trouvée, XLOOKUP() peut renvoyer une correspondance approximative si un type de correspondance spécifique est fourni. À bien des égards, XLOOKUP() surpasse ses prédécesseurs (VLOOKUP(), HLOOKUP() et LOOKUP()).

En particulier, elle permet :

• de rechercher des données à l'horizontale comme à la verticale, et dans n'importe quel sens
• d'utiliser plusieurs critères de recherche
• d'obtenir une correspondance approximative, tout en privilégiant par défaut la correspondance exacte
• d'effectuer une correspondance partielle
• de renvoyer plusieurs colonnes et lignes
• de renvoyer un texte personnalisé lorsqu'aucune correspondance n'est trouvée.

De plus, XLOOKUP() est plus rapide que les anciennes fonctions de recherche d'Excel, ce qui compte lorsque l'on interroge de grands volumes de données.

Comment utiliser XLOOKUP() avec une seule condition

Passons rapidement en revue la syntaxe de base de XLOOKUP() :

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

où les paramètres obligatoires sont :

• lookup_value : la valeur à rechercher

• lookup_array : le tableau ou la plage dans laquelle chercher

• return_array : le tableau ou la plage à partir de laquelle renvoyer la valeur

et les paramètres optionnels sont :

• [if_not_found] : le texte à renvoyer lorsqu'aucune correspondance n'est trouvée

• [match_mode] : le type de correspondance (exacte ou approximative) et quoi renvoyer si aucune correspondance n'est trouvée

• [search_mode] : le mode de recherche à utiliser (recherche directe ou inversée, recherche binaire sur une plage triée par ordre croissant ou décroissant).

Nous verrons plus loin des exemples d'utilisation de ces paramètres optionnels.

Pour découvrir ou réviser d'autres fonctions et formules utiles d'Excel, consultez le tutoriel The 15 Basic Excel Formulas Everyone Needs to Know et la feuille de route des formules Excel.

Comment utiliser XLOOKUP() avec plusieurs critères

Comme le montre sa syntaxe de base, la fonction XLOOKUP() est conçue par défaut pour travailler avec une seule valeur de recherche pour une variable donnée.

Cependant, nous pouvons l'adapter pour l'utiliser simultanément sur plusieurs variables, en recherchant une valeur différente dans chaque variable. Autrement dit, nous pouvons effectuer notre recherche avec XLOOKUP() en appliquant plusieurs critères. Pour cela, deux approches principales : la concaténation et les expressions booléennes. Nous allons passer les deux en revue.

Avant d'entrer dans les détails techniques, regardons le tableau Excel sur lequel nous allons travailler. Ce tableau fournit des informations sur 10 chats : leurs noms, leurs couleurs et leurs âges :

Dans nos exemples, nous utiliserons XLOOKUP() pour retrouver l'une des trois caractéristiques d'un chat en utilisant les deux autres.

Peu importe que notre tableau soit très simple et que nous puissions retrouver visuellement l'information. L'objectif est de comprendre les principes essentiels d'utilisation de XLOOKUP() avec plusieurs critères. Une fois ces principes acquis, vous pourrez les transposer à des cas réels plus complexes, par exemple avec plus de deux critères ou des exigences spécifiques.

XLOOKUP() avec plusieurs critères via la concaténation

L'approche par concaténation est très directe : il suffit de concaténer entre eux les valeurs et les plages de recherche correspondantes. Voyons cela.

Supposons que nous voulions trouver l'âge d'une chatte blanche et grise appelée Nala. Par souci de clarté, nous avons ajouté ces deux critères sur la même feuille que le tableau :

Dans ce cas, la formule XLOOKUP() sera la suivante :

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Elle renverra 2,5, l'âge de la première Nala blanche et grise (mon chat 😺). Rappelez-vous que XLOOKUP() renvoie l'élément correspondant à la première correspondance.

Pour comprendre ce que représentent les deux premiers composants de la formule ci-dessus — ceux avec esperluette — nous pouvons les exécuter séparément dans des cellules Excel :

=B2:B11&C2:C11

Le résultat est NalaWhite et Grey.

=B2:B11&C2:C11

Le résultat est :

Oui, c'est aussi simple que cela : nous avons simplement concaténé entre elles les valeurs et les plages de recherche correspondantes, conformément à la syntaxe de base de XLOOKUP() vue plus haut.

XLOOKUP() avec plusieurs critères via des expressions booléennes

Reprenons la même tâche — trouver l'âge d'une Nala blanche et grise — mais avec la seconde approche : les expressions booléennes. La formule XLOOKUP() sera cette fois :

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Sans surprise, elle renverra aussi 2,5, l'âge de la première Nala blanche et grise.

Analysons les composants de cette formule. Ici, 1 représente TRUE, c'est-à-dire que nous recherchons la valeur TRUE dans le tableau de recherche représenté par le second composant — (B2:B11=G2)*(C2:C11=G3).

Ce composant contient deux facteurs, chacun testant une condition : le premier vérifie si le nom correspond à celui recherché, le second si la couleur correspond à celle recherchée. Exécutons chaque facteur séparément dans une cellule Excel :

=B2:B11=G2

Le résultat est :

=C2:C11=G3

Le résultat est :

À présent, exécutons l'ensemble du second composant de la formule :

=(B2:B11=G2)*(C2:C11=G3)

Le résultat est :

Même si nous voyons deux valeurs à 1 (donc deux TRUE), XLOOKUP() renvoie l'élément correspondant à la première correspondance.

À première vue, l'approche par expressions booléennes pour XLOOKUP() avec plusieurs critères paraît plus complexe et moins intuitive que la concaténation — et ce serait encore plus vrai avec plus de deux critères. Pourquoi ne pas toujours utiliser la concaténation alors ?

Parce que les expressions booléennes offrent bien plus de flexibilité avec plusieurs critères. Voyons leurs avantages par rapport à la concaténation.

Tester la valeur FALSE

Dans la formule ci-dessus, nous testions si l'expression booléenne était TRUE, en passant la valeur 1 :

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Dans certains cas, nous pouvons vouloir tester si l'expression est FALSE. Par exemple, trouver l'âge du premier chat du tableau qui n'est pas une Nala blanche et grise. Nous modifions ainsi la formule :

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Le résultat sera 1, l'âge du premier chat qui n'est pas une Nala blanche et grise (plus précisément, une Nala tricolore, mais pas blanche et grise).

Utiliser des opérateurs logiques

Avec les expressions booléennes, nous ne sommes pas limités à l'égalité. Disons que nous voulons trouver la couleur d'une Nala âgée de moins de 2 ans :

La formule XLOOKUP() sera :

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

Le résultat sera Tricolor.

Satisfaire au moins un critère

Jusqu'ici, nous testions la satisfaction de tous les critères. Dans d'autres scénarios, il peut suffire d'en satisfaire au moins un.

Pour l'illustrer, reprenons notre tâche initiale — trouver l'âge d'une Nala blanche et grise :

Cette fois, nous voulons l'âge d'un chat qui est soit appelé Nala, soit blanc et gris. La formule XLOOKUP() devient :

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Elle renvoie 1, l'âge d'une Nala tricolore.

Pour rappel, lorsque nous recherchions l'âge d'un chat à la fois appelé Nala et blanc et gris, la formule était :

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Remplacer * par + fait toute la différence.

Si vous souhaitez exploiter tout le potentiel d'Excel, découvrez le parcours de compétences complet, pratique et structuré Excel Fundamentals.

Paramètres optionnels pour XLOOKUP() avec plusieurs critères

Comme pour l'usage de XLOOKUP() avec un seul critère, vous pouvez activer des options supplémentaires lorsque vous l'utilisez avec plusieurs critères. C'est là qu'interviennent les paramètres optionnels [if_not_found], [match_mode] et [search_mode].

Voyons rapidement des exemples pour chacun de ces paramètres avec XLOOKUP() et plusieurs critères. Pour simplifier, nous utiliserons à chaque fois l'approche par concaténation.

Renvoyer un texte fourni lorsqu'aucune correspondance n'est trouvée

Ici, nous recherchons l'âge d'une Nala noire — un chat inexistant dans notre tableau :

Dans ce cas, nous ajoutons à la formule XLOOKUP() le paramètre optionnel [if_not_found] avec le texte à renvoyer si aucune correspondance n'est trouvée :

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Effectivement, la fonction renvoie le texte fourni : No cat is found. Sans texte fourni, elle aurait renvoyé #N/A.

Renvoyer une correspondance approximative

Supposons que nous cherchions l'âge d'une Nala tricolore, mais sans certitude que sa couleur soit saisie "Tricolor" ou "Tricolour" dans le tableau. Il faut alors recourir à une correspondance approximative avec joker et passer à XLOOKUP() l'argument [match_mode] égal à 2.

Dans le tableau ci-dessus, nous avons utilisé un astérisque (*), qui représente n'importe quel nombre de caractères, y compris 0. D'autres jokers sont listés dans la documentation Microsoft Office.

La formule XLOOKUP() sera :

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

Elle renverra 1, l'âge de la première (et unique) Nala tricolore du tableau.

Notez que l'argument 2 dans la formule signifie que nous voulons une correspondance approximative, et non pas rechercher l'âge d'une Nala de couleur "Tricolo*r". Dans notre cas, l'astérisque a capturé le mot "Tricolor", mais il aurait fait de même avec "Tricolour" ou, par exemple, "Tricolooor".

Effectuer une recherche inversée

Reprenons l'exercice du début — trouver l'âge d'une Nala blanche et grise — mais en commençant la recherche par la fin de la liste.

Pour cela, nous ajoutons à XLOOKUP() le paramètre optionnel [search_mode] défini à -1, comme ci-dessous :

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

La fonction renvoie 7, l'âge de la première Nala blanche et grise à partir de la fin de la plage de recherche.

XLOOKUP() vs. INDEX() et MATCH()

Dans les anciennes versions d'Excel, pour reproduire une fonctionnalité similaire à XLOOKUP() avec plusieurs critères, il fallait combiner les fonctions INDEX() et MATCH(). Sans entrer dans la syntaxe de ces fonctions, voyons comment trouver l'âge de la première Nala blanche et grise avec l'ancienne méthode INDEX() + MATCH() et avec la nouvelle fonction XLOOKUP() (reportez-vous au tableau précédent pour les références de cellules) :

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Même si les arguments se ressemblent un peu, voici les principaux avantages de XLOOKUP() par rapport à l'ancienne méthode :

• Une seule fonction suffit — inutile de combiner des fonctions.

• Possibilité d'appliquer l'approche par concaténation lorsque c'est pertinent.

• Accès aux paramètres optionnels (absents de MATCH()).