Comment utiliser XLOOKUP avec plusieurs critères dans Excel

La fonction XLOOKUP d’Excel permet d’effectuer des recherches complexes avec plusieurs critères, offrant une flexibilité supérieure aux fonctions de recherche traditionnelles. Cette fonction révolutionnaire transforme la manière dont on traite les données dans Excel, particulièrement lorsqu’il s’agit de croiser plusieurs conditions de recherche.

Matériel nécessaire

Pour suivre ce guide, l’utilisateur aura besoin de :

• Microsoft Excel 2021 ou version ultérieure (incluant Excel pour Microsoft 365)
• Excel pour le web, tablette ou application mobile
• Un jeu de données avec plusieurs colonnes de critères
• Connaissance de base des formules Excel
• Compréhension des références de cellules et des tableaux structurés

Comprendre la syntaxe XLOOKUP

Structure de base de la fonction

La syntaxe XLOOKUP suit une logique claire et méthodique :

=XLOOKUP(valeur_recherche, tableau_recherche, tableau_retour, si_non_trouvé, mode_correspondance, mode_recherche)

Les arguments se décomposent ainsi :

1. valeur_recherche (obligatoire) : la valeur à rechercher
2. tableau_recherche (obligatoire) : la plage où chercher
3. tableau_retour (obligatoire) : la plage des valeurs à retourner
4. si_non_trouvé (optionnel) : texte à afficher si aucune correspondance
5. mode_correspondance (optionnel) : 0 = correspondance exacte (défaut)
6. mode_recherche (optionnel) : 1 = du premier au dernier (défaut)

Exemple avec un critère unique

Avant d’aborder les critères multiples, il convient de maîtriser l’utilisation basique. Dans un tableau de scores d’étudiants, la formule suivante recherche un ID spécifique :

=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],”Aucune correspondance”,0,1)

Cette formule recherche la valeur de la cellule E1 dans la colonne ID du tableau T_Scores et retourne la valeur correspondante de la colonne Score.

Méthode 1 : Critères multiples avec logique booléenne

Principe de fonctionnement

La logique booléenne permet de combiner plusieurs conditions en créant un tableau de recherche temporaire. Cette méthode utilise la multiplication pour créer des conditions ET entre les critères.

Construction de la formule

Pour rechercher selon plusieurs critères, la formule prend cette forme :

=XLOOKUP(1, (T_Données[Critère1]=Valeur1)*(T_Données[Critère2]=Valeur2), T_Données[Résultat], “Aucun résultat”)

Étapes détaillées d’implémentation

1. Préparer les données source

   Organiser les données dans un tableau structuré avec des colonnes clairement définies pour chaque critère de recherche.

2. Identifier les cellules de critères

   Définir les cellules contenant les valeurs de recherche pour chaque critère.

3. Construire la condition booléenne

   Créer l’expression (T_Données[Critère1]=Valeur1) qui génère un tableau de VRAI/FAUX.

4. Multiplier les conditions

   Utiliser l’opérateur * pour combiner les conditions : seules les lignes satisfaisant tous les critères retourneront 1.

5. Rechercher la valeur 1

   XLOOKUP cherche la première occurrence de 1 dans le tableau résultant.

Exemple pratique : Gestion des ventes

Dans un contexte de gestion des ventes par boisson et pays, la formule suivante permet de trouver le responsable et les ventes :

=XLOOKUP(1, (T_Responsables[Boisson]=G1)*(T_Responsables[Pays]=G2), T_Responsables[[Responsable]:[Ventes]], “Aucun résultat”)

Le processus interne fonctionne ainsi :

• Excel vérifie chaque ligne de la colonne Boisson contre G1
• Excel vérifie chaque ligne de la colonne Pays contre G2
• La multiplication ne retourne 1 que pour les lignes satisfaisant les deux conditions
• XLOOKUP retourne les valeurs correspondantes des colonnes Responsable et Ventes

Variantes des opérateurs logiques

Condition OU : Remplacer * par + pour trouver les lignes satisfaisant au moins un critère.

Opérateurs de comparaison : Utiliser >, =, <= pour les critères numériques.

Méthode 2 : Critères multiples avec concaténation

Principe de la concaténation

Cette méthode combine les valeurs de recherche et les colonnes de critères en utilisant l’opérateur &. Elle crée une chaîne unique pour la recherche.

Structure de la formule

=XLOOKUP(Valeur1&Valeur2, T_Données[Critère1]&T_Données[Critère2], T_Données[Résultat], “Aucun résultat”)

Étapes d’implémentation

1. Concaténer les valeurs de recherche

   Combiner G1&G2 pour créer une chaîne de recherche unique comme “CaféEspagne”.

2. Concaténer les colonnes de données

   Joindre T_Données[Boisson]&T_Données[Pays] pour créer un tableau de recherche combiné.

3. Effectuer la recherche

   XLOOKUP compare la chaîne concaténée avec chaque entrée du tableau combiné.

4. Retourner le résultat

   La fonction retourne les valeurs des colonnes spécifiées pour la première correspondance trouvée.

Exemple détaillé

Pour un système de gestion des ventes :

=XLOOKUP(G1&G2, T_Responsables[Boisson]&T_Responsables[Pays], T_Responsables[[Responsable]:[Ventes]], “Aucun résultat”)

Si G1 contient “Café” et G2 contient “Espagne”, Excel recherche “CaféEspagne” dans le tableau concaténé des colonnes Boisson et Pays.

Optimisation et bonnes pratiques

Choix de la méthode appropriée

Logique booléenne : Recommandée pour :

• Critères numériques avec opérateurs de comparaison
• Conditions complexes (ET, OU)
• Flexibilité dans les types de critères

Concaténation : Idéale pour :

• Critères textuels simples
• Performance optimisée sur de gros volumes
• Simplicité de formulation

Gestion des erreurs

1. Valeurs manquantes

   Inclure systématiquement l’argument “si_non_trouvé” pour gérer les cas sans correspondance.

2. Espaces indésirables

   Utiliser les fonctions SUPPRESPACE() pour nettoyer les données avant la recherche.

3. Casse des caractères

   Appliquer MAJUSCULE() ou MINUSCULE() pour standardiser la casse.

Optimisation des performances

Tableaux structurés : Utiliser des tableaux Excel pour améliorer la lisibilité et les performances.

Validation des données : Implémenter des listes déroulantes pour éviter les erreurs de saisie.

Documentation : Commenter les formules complexes pour faciliter la maintenance.

Cas d’usage avancés

Recherche avec plages de dates

Pour rechercher des données dans une période spécifique :

=XLOOKUP(1, (T_Données[Date]>=Date_Début)(T_Données[Date]<=Date_Fin)(T_Données[Produit]=Produit_Recherché), T_Données[Montant], 0)

Critères numériques avec seuils

Pour des recherches basées sur des valeurs minimales :

=XLOOKUP(1, (T_Données[Montant]>Seuil_Min)*(T_Données[Catégorie]=Catégorie_Recherchée), T_Données[Client], “Aucun client”)

Combinaison de critères textuels et numériques

Mélanger différents types de critères dans une même formule :

=XLOOKUP(1, (T_Données[Région]=”Nord”)(T_Données[Chiffre_Affaires]>10000)(T_Données[Année]=2025), T_Données[[Vendeur]:[Commission]], “Aucune donnée”)

Dépannage et résolution de problèmes

Erreurs courantes

Erreur

N/A : Aucune correspondance trouvée. Vérifier les critères et les données source.

Erreur

VALEUR! : Types de données incompatibles. S’assurer de la cohérence des formats.

Résultats inattendus : Contrôler les espaces, la casse et les formats de cellules.

Techniques de débogage

1. Tester les critères individuellement

   Vérifier chaque condition séparément avant de les combiner.

2. Utiliser des colonnes auxiliaires

   Créer des colonnes temporaires pour visualiser les résultats intermédiaires.

3. Fonction EVALUER

   Utiliser cette fonction pour examiner les tableaux générés par les conditions booléennes.

Conclusion

La maîtrise de XLOOKUP avec critères multiples transforme l’efficacité du traitement des données dans Excel. Les deux méthodes présentées – logique booléenne et concaténation – offrent des solutions adaptées à différents contextes d’utilisation. La logique booléenne excelle dans la flexibilité et la gestion de critères complexes, tandis que la concaténation privilégie la simplicité et les performances pour des critères textuels.

L’implémentation réussie de ces techniques nécessite une compréhension approfondie des données source, une planification minutieuse des critères de recherche, et une attention particulière à la gestion des erreurs. Avec la pratique, ces formules deviennent des outils puissants pour l’analyse et la manipulation de données complexes, permettant aux utilisateurs d’Excel de créer des solutions robustes et efficaces pour leurs besoins professionnels.

Sandrine Marcos

Rédactrice spécialisée en édition de site. Formation de journaliste et passionnée par les nouvelles technologies, l’intelligence artificielle et la rédaction web.