Utiliser XLOOKUP avec plusieurs critères dans Excel

Liens rapides

• La syntaxe de XLOOKUP et un exemple à critère unique
• Exemple 1 : plusieurs critères avec logique booléenne
• Exemple 2 : plusieurs critères avec concaténation

Une idée reçue sur la fonction XLOOKUP dans Microsoft Excel est que vous ne pouvez utiliser qu’une condition pour rechercher une valeur. En réalité, vous pouvez rechercher en fonction de plusieurs critères. Deux méthodes courantes existent : la logique booléenne (création d’un tableau temporaire de 1/0) et la concaténation des clés de recherche. Le choix dépend de la granularité des critères et de la manière dont les données sont structurées.

Important : XLOOKUP est disponible dans Excel 2021 et versions ultérieures, dans Excel pour Microsoft 365, ainsi que dans Excel pour le web, tablette et mobile.

Microsoft 365 Personal

Microsoft 365 inclut l’accès aux applications Office (Word, Excel, PowerPoint) sur plusieurs appareils et 1 To de stockage OneDrive.

$100 at Microsoft

$100 at Amazon

Expand

Collapse

La syntaxe de XLOOKUP et un exemple à critère unique

La syntaxe suit un ordre logique :

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

où

• a (obligatoire) est la valeur recherchée (lookup value),
• b (obligatoire) est le tableau de recherche (lookup array),
• c (obligatoire) est le tableau de retour (return array),
• d (optionnel) est le texte renvoyé si la valeur n’est pas trouvée,
• e (optionnel) est le mode de correspondance (0 = correspondance exacte (par défaut), -1 = exacte ou plus petit, 1 = exacte ou plus grand, 2 = correspondance avec caractères génériques),
• f (optionnel) est le mode de recherche (1 = du premier au dernier (par défaut), -1 = du dernier au premier, 2 = recherche binaire si b est trié ascendant, -2 = recherche binaire si b est trié descendant).

Exemple simple : après avoir choisi un ID dans une liste déroulante (cellule E1), la formule suivante dans E2 retourne le score correspondant :

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

Définitions rapides : lookup array = colonne ou tableau où Excel cherche la valeur ; return array = colonne ou plage dont Excel renvoie la valeur correspondante.

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

Scénario : vous vendez six boissons dans six pays. Chaque ligne du tableau T_Managers associe Boisson, Pays, Manager et Ventes. L’objectif : saisir une boisson et un pays pour obtenir le manager et les ventes.

XLOOKUP attend un seul tableau de recherche. Pour l’utiliser avec plusieurs critères, créez un tableau temporaire qui combine les tests sur chaque colonne et renvoie 1 pour les lignes satisfaisant tous les critères. Formule utile :

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

Étapes de calcul (décomposées) :

1. (T_Managers[Drink]=G1) renvoie un tableau de TRUE/FALSE selon l’égalité avec le contenu de G1.

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

2. (T_Managers[Country]=G2) renvoie un autre tableau de TRUE/FALSE pour le pays :

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

3. En multipliant ces deux tableaux, Excel convertit TRUE en 1 et FALSE en 0 et multiplie élément par élément : 1×1 = 1, sinon 0.

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

4. XLOOKUP recherche la valeur 1 dans ce tableau combiné. Le premier 1 trouvé désigne la ligne correspondante dans le tableau de retour.

Dans l’exemple, la première ligne qui renvoie 1 se situe à la 11e position. XLOOKUP renvoie donc le 11e Manager et le 11e chiffre de Ventes.

Remarques pratiques :

• Utilisez la multiplication (*) entre tests pour un comportement logique ET (toutes les conditions doivent être vraies).
• Utilisez l’addition (+) entre tests pour un comportement logique OU (au moins une condition vraie). Dans ce cas, cherchez la valeur 1 également, mais préparez-vous à l’ordre d’apparition des lignes.
• Pour les comparaisons non-égales, remplacez ‘=’ par ‘>’ ou ‘<’ selon le besoin (ex. T_Managers[Sales]>1000).

Exemple 2 : plusieurs critères avec concaténation

La concaténation crée une clé composite. Elle est souvent plus simple si chaque combinaison des colonnes recherche est unique.

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

Explication :

• G1&G2 produit une chaîne comme “CoffeeSpain”.
• T_Managers[Drink]&T_Managers[Country] crée un tableau de chaînes concaténées pour chaque ligne (ex. “Apple juiceAustralia”, “CoffeeSpain”, etc.).
• XLOOKUP cherche la chaîne exacte et renvoie les colonnes demandées.

Avantages et limites :

• Avantage : simple, lisible et performant pour des données dont les clés composites sont propres (pas d’espaces variables, majuscules cohérentes).
• Limite : sensible aux différences d’espacement, casse et format. Si vous avez des valeurs manquantes (BLANK), la concaténation peut produire des doublons ou des chaînes inattendues.

Conseil : normalisez les valeurs avant concaténation, p.ex. TRIM(UPPER(…)) pour éviter les écarts liés aux espaces et à la casse.

Comparaison rapide des deux méthodes

• Concaténation : simple, lisible, idéal si chaque combinaison est unique.
• Booléen : plus flexible ; permet d’utiliser >, <, OR (+) et des conditions complexes.

Tableau heuristique :

• Besoin d’égalité sur plusieurs colonnes et données propres : concaténation.
• Besoin de comparaisons numériques, de conditions mixtes ou d’OR/AND complexes : logique booléenne.
• Données non uniques par combinaison : réfléchissez à INDEX/MATCH + AGGREGATE ou FILTER selon le besoin.

Alternatives à XLOOKUP multi-critères

• FILTER (Excel Microsoft 365) : renvoie toutes les lignes qui correspondent aux critères. Utile si vous voulez toutes les correspondances plutôt que la première.
• INDEX + MATCH (avec une clé composite) : méthode historique compatible avec plus de versions.
• SUMPRODUCT : peut servir à obtenir des sommes pondérées ou trouver la première ligne (en combinant conditions) mais est plus lente sur de larges jeux de données.
• Power Query : meilleure option pour des transformées répétées et des jointures plus complexes.

Mini-exemple FILTER :

=FILTER(T_Managers[[Manager]:[Sales]], (T_Managers[Drink]=G1)*(T_Managers[Country]=G2), "No result")

Cette formule renvoie toutes les lignes correspondant aux deux critères.

Méthodologie pour choisir la bonne approche

1. Déterminez si vous voulez la première correspondance ou toutes les correspondances.
2. Vérifiez l’unicité des combinaisons de colonnes.
3. Choisissez concaténation si les combinaisons sont uniques et propres.
4. Choisissez la logique booléenne si vous avez besoin de comparaisons, d’OR/AND mixtes ou de tests numériques.
5. Envisagez FILTER ou Power Query pour des extractions multiples ou des transformations répétitives.

Mermaid pour guider le choix :

flowchart TD
  A[Commencez] --> B{Voulez-vous toutes les correspondances ?}
  B -- Oui --> C[FILTER ou Power Query]
  B -- Non --> D{Les combinaisons sont-elles uniques et propres ?}
  D -- Oui --> E[Concaténation + XLOOKUP]
  D -- Non --> F[Booléen + XLOOKUP]
  E --> G[Terminé]
  F --> G
  C --> G

Bonnes pratiques et dépannage

• Normalisez les données : TRIM et UPPER réduisent les erreurs dues aux espaces et à la casse.
• Évitez les références volatiles dans de grandes feuilles (ex. INDIRECT) qui ralentissent le recalcul.
• Si XLOOKUP renvoie #N/A : vérifiez la cellule de recherche, le type de données et les espaces invisibles.
• Utilisez l’argument d’erreur (d) pour contrôler le texte affiché quand il n’y a pas de correspondance.
• Si vous attendez plusieurs résultats, préférez FILTER ou une solution basée sur Power Query.

Exemples d’erreurs fréquentes :

• Chercher une chaîne alors que la colonne contient des nombres stockés comme texte.
• Espaces invisibles (utilisez LEN, CODE ou CLEAN pour diagnostiquer).
• Doubles correspondances inattendues quand vous utilisez + au lieu de *.

Critères d’acceptation pour une solution de recherche multi-critères

• La formule renvoie la bonne ligne pour un jeu de tests représentatif.
• Le traitement gère les cellules vides et les valeurs non trouvées sans erreur.
• Les performances restent acceptables sur le volume de données prévu.
• Les résultats sont reproductibles après normalisation des entrées.

Checklist rôle par rôle

Analyste :

• Normaliser les données sources (TRIM, UPPER).
• Tester avec cas positifs et négatifs.
• Mesurer le temps de recalcul sur un échantillon.

Responsable :

• Valider les critères métiers.
• Confirmer le formatage des rapports de sortie.

Développeur / Automatisation :

• Documenter la logique.
• Envisager Power Query ou macros si les transformations sont répétées.

Cas de test et critères d’acceptation

1. Cas simple : Boisson=Coffee, Pays=Spain → Manager=Olivia, Sales=346.
2. Cas non trouvé : Boisson inconnue → renvoie “No result”.
3. Cas multiples (avec FILTER) : vérifier que toutes les lignes attendues sont retournées.
4. Cas espaces/casse : valeurs avec espaces en trop doivent toujours matcher après TRIM+UPPER.

Sécurité, confidentialité et localisation

• Données personnelles : si les tableaux contiennent des noms ou identifiants personnels, appliquez la politique GDPR locale pour anonymiser ou limiter l’accès.
• Localisation Excel : en France, les séparateurs décimaux et de liste peuvent différer (virgule vs point-virgule). Vérifiez la syntaxe locale de formule si votre Excel est configuré pour une autre langue.

Note : ne modifiez pas les chemins d’image ni les noms de fichiers lors de l’export.

Exemples pratiques complémentaires

1. Utiliser > dans une condition booléenne :

=XLOOKUP(
  1,
  (T_Managers[Sales]>200)*(T_Managers[Country]=G2),
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Cela renvoie la première ligne où Sales > 200 ET Country = G2.

2. Concaténation sécurisée (normalisation) :

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

Maturité et limites

• Niveau basique : concaténation pour correspondances simples.
• Niveau intermédiaire : logique booléenne pour conditions mixtes et comparaisons.
• Niveau avancé : FILTER, Power Query, ou modèles relationnels lorsque les transformations deviennent complexes.

Modèle mental rapide

Pensez à la recherche comme à une passe de filtrage : vous marquez chaque ligne “vraie” ou “fausse” pour chaque test. Ensuite, combinez ces marques (ET/OU) et prenez la première ligne “vraie” (XLOOKUP) ou toutes les lignes vraies (FILTER).

Modèle de TCO/Impact × Effort (qualitatif)

• Concaténation : faible effort, impact rapide.
• Booléen : effort modéré, impact élevé pour complexité métier.
• Power Query : effort élevé, impact élevé pour traitements récurrents et volumes.

Conclusion

XLOOKUP gère très bien la recherche multi-critères quand on adapte la logique : concaténation si les clés sont propres et uniques ; logique booléenne pour des conditions plus riches (comparaisons, OR, AND). Pour des besoins d’export ou de plusieurs correspondances, privilégiez FILTER ou Power Query. Normalisez toujours les données pour éviter les erreurs liées aux espaces et à la casse.

Important : testez vos formules sur jeux de données représentatifs et documentez la logique choisie.

Résumé des points clés :

• XLOOKUP accepte une matrice de recherche temporaire composée via * ou +, ou une clé concaténée.
• Utilisez TRIM et UPPER pour fiabiliser la concaténation.
• FILTER et Power Query sont des alternatives puissantes selon le besoin.