Fonctions XLOOKUP et FILTRE dans Excel : pourquoi elles transforment vos recherches de données
Les fonctions XLOOKUP et FILTRE font partie des nouveautés les plus puissantes d’Excel pour effectuer des recherches dynamiques et créer des tableaux interactifs. Elles remplacent efficacement des classiques comme RECHERCHEV, INDEX et EQUIV, tout en offrant plus de souplesse et une meilleure lisibilité des formules.
Que vous gériez des bases de données clients, des tableaux de suivi de ventes, des rapports RH ou des analyses financières, maîtriser XLOOKUP et FILTRE permet de gagner du temps et de réduire le risque d’erreurs. Ce guide détaille leur fonctionnement, leurs arguments, ainsi que des exemples concrets pour automatiser vos feuilles de calcul Excel.
Fonction XLOOKUP dans Excel : principe et syntaxe détaillée
La fonction XLOOKUP (ou RECHERCHEX dans certaines versions françaises récentes) est une fonction de recherche verticale et horizontale moderne. Elle remplace plusieurs fonctions plus anciennes et corrige leurs limitations.
Sa syntaxe de base est la suivante :
=XLOOKUP(valeur_cherchée; plage_recherche; plage_retour; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])
Les principaux arguments de XLOOKUP :
- valeur_cherchée : la valeur que vous recherchez (par exemple un code produit, un ID client, une date).
- plage_recherche : la colonne ou la ligne dans laquelle se trouve la valeur recherchée.
- plage_retour : la colonne ou la ligne d’où Excel doit renvoyer le résultat, une fois la correspondance trouvée.
- [si_non_trouvé] (optionnel) : le texte ou la valeur à afficher si aucune correspondance n’est trouvée (par exemple « Non trouvé »).
- [mode_correspondance] (optionnel) : précise le type de recherche (exacte, approximative, etc.).
- [mode_recherche] (optionnel) : indique le sens de la recherche (du premier au dernier, du dernier au premier, etc.).
Pourquoi XLOOKUP est plus puissant que RECHERCHEV dans Excel
La fonction RECHERCHEV a longtemps été la référence pour les recherches dans un tableau Excel. Cependant, elle présente plusieurs limites que XLOOKUP corrige directement.
Parmi les avantages clés de XLOOKUP pour des recherches dynamiques :
- Recherche à gauche et à droite : XLOOKUP peut retourner une valeur située à gauche ou à droite de la colonne de recherche. RECHERCHEV, elle, est limitée à une recherche vers la droite uniquement.
- Indexation flexible : au lieu de devoir indiquer un numéro de colonne, vous sélectionnez simplement la plage_retour. Cela rend les formules plus lisibles et plus faciles à modifier.
- Gestion des erreurs intégrée : l’argument
[si_non_trouvé]remplace souvent l’usage combiné deSIERREUR+RECHERCHEV. - Fonctionne en lignes et en colonnes : XLOOKUP peut effectuer des recherches verticales (colonnes) et horizontales (lignes) dans un même tableau.
- Compatibilité avec les plages dynamiques : XLOOKUP se combine parfaitement avec les plages dynamiques et d’autres fonctions modernes comme FILTRE, SÉQUENCE ou SORT.
Exemples pratiques de XLOOKUP pour des recherches dynamiques
Voici quelques scénarios concrets où la fonction XLOOKUP améliore vos modèles Excel et vos tableaux interactifs.
1. Rechercher un prix produit à partir d’un code
Supposons un tableau avec les codes produits en colonne A et les prix en colonne C. Vous souhaitez récupérer le prix correspondant à un code saisi en cellule E2 :
=XLOOKUP(E2; A:A; C:C; "Code inconnu")
Si le code produit n’est pas trouvé dans la colonne A, Excel affichera « Code inconnu » grâce à l’argument [si_non_trouvé].
2. Renvoyer plusieurs informations avec XLOOKUP
Avec XLOOKUP, vous pouvez renvoyer plusieurs colonnes en une seule formule si vous utilisez une plage_retour multi-colonnes. Par exemple, pour renvoyer le nom et la ville d’un client à partir de son ID :
=XLOOKUP(G2; TableClients[ID]; TableClients[[Nom]:[Ville]]; "Client inexistant")
Si vous utilisez une version d’Excel compatible avec les plages dynamiques, cette formule se déversera automatiquement sur plusieurs colonnes.
3. Rechercher la dernière occurrence avec XLOOKUP
Grâce à l’argument [mode_recherche], vous pouvez rechercher la dernière valeur correspondante dans une liste, pratique pour des historiques ou des journaux de ventes.
Exemple pour trouver le dernier prix saisi pour un produit :
=XLOOKUP(E2; A:A; C:C; "Aucune donnée"; 0; -1)
Le paramètre -1 indique que la recherche se fait de la fin vers le début.
Fonction FILTRE dans Excel : créer des tableaux interactifs et dynamiques
La fonction FILTRE est au cœur de la création de tableaux interactifs dans Excel. Elle permet d’extraire automatiquement les lignes d’un tableau qui répondent à un ou plusieurs critères, et de mettre à jour ces résultats en temps réel lorsque les données de source changent.
Sa syntaxe générale est :
=FILTRE(plage; critère; [si_vide])
Les principaux éléments de la fonction FILTRE :
- plage : le tableau ou la plage de cellules à filtrer (par exemple une table structurée Excel).
- critère : l’expression logique qui définit les lignes à conserver (par exemple
(Table[Ventes]>1000)*(Table[Pays]="France")). - [si_vide] (optionnel) : la valeur renvoyée si aucun enregistrement ne répond aux critères.
Créer des tableaux interactifs avec la fonction FILTRE
La force de FILTRE réside dans sa capacité à produire un résultat qui se redimensionne automatiquement : le nombre de lignes renvoyées varie selon les données de base. Cela en fait un outil idéal pour des tableaux dynamiques basés sur des critères saisis par l’utilisateur.
1. Filtrer un tableau par pays
Imaginez un tableau nommé Ventes contenant une colonne Pays, une colonne Commercial et une colonne Montant. Pour renvoyer toutes les lignes correspondant au pays choisi en cellule G2 :
=FILTRE(Ventes; Ventes[Pays]=G2; "Aucun résultat")
En changeant simplement la valeur de G2, l’utilisateur obtient un tableau de résultats interactif sans devoir appliquer un filtre manuel.
2. Appliquer plusieurs critères dans FILTRE
La fonction FILTRE permet de combiner plusieurs critères logiques en AND (ET) ou en OR (OU). Par exemple, pour afficher les ventes en France supérieures à 1 000 :
=FILTRE(Ventes; (Ventes[Pays]="France")*(Ventes[Montant]>1000); "Aucune vente éligible")
Les parenthèses et le symbole * (multiplication) servent à combiner les conditions avec un ET logique.
3. Créer des listes déroulantes dynamiques avec FILTRE
La fonction FILTRE peut aussi servir à alimenter des listes déroulantes dépendantes dans Excel. Par exemple, une première liste déroulante pour choisir une catégorie de produit, puis une seconde liste qui n’affiche que les produits de cette catégorie.
En combinant FILTRE avec les fonctions UNIQUE et SORT, vous créez des listes de valeurs mises à jour automatiquement lorsqu’un nouveau produit ou une nouvelle catégorie est ajoutée aux données sources.
Combiner XLOOKUP et FILTRE pour des analyses avancées dans Excel
C’est en combinant XLOOKUP et FILTRE que vous obtenez des recherches dynamiques vraiment puissantes et des tableaux de bord interactifs dans Excel.
1. Rechercher dans un sous-ensemble filtré
Vous pouvez commencer par filtrer un tableau avec FILTRE, puis utiliser XLOOKUP pour interroger ce résultat. Par exemple :
=XLOOKUP(I2; FILTRE(Ventes[Client]; Ventes[Pays]=G2); FILTRE(Ventes[Montant]; Ventes[Pays]=G2); "Client non trouvé")
Dans cet exemple, la recherche porte uniquement sur les clients du pays indiqué en G2, ce qui permet des analyses plus ciblées.
2. Générer des fiches clients ou fiches produits dynamiques
En saisissant un identifiant ou un code produit dans une cellule, vous pouvez alimenter automatiquement une fiche de synthèse avec plusieurs informations : nom, coordonnées, total d’achats, dernière commande, etc.
La combinaison de :
- XLOOKUP pour les informations uniques (nom, ville, catégorie).
- FILTRE pour les listes (historique des commandes, interventions, paiements).
permet de construire rapidement des interfaces d’analyse au sein même d’Excel, sans macro VBA.
Bonnes pratiques pour utiliser XLOOKUP et FILTRE efficacement
Pour tirer le meilleur parti de ces deux fonctions et optimiser vos fichiers Excel, quelques recommandations sont utiles.
- Utiliser des tableaux structurés : convertissez vos plages de données en tables Excel (Ctrl + T). Vous pourrez ainsi utiliser des références structurées (comme
Ventes[Montant]) plus stables lorsque vous ajoutez des lignes ou colonnes. - Nommer clairement vos plages : des noms explicites (par exemple
tbl_Clients,tbl_Ventes) facilitent la lecture et la maintenance des formules XLOOKUP et FILTRE. - Prévoir les cas sans résultat : utilisez systématiquement les arguments
[si_non_trouvé]dans XLOOKUP et[si_vide]dans FILTRE pour éviter les messages d’erreur peu lisibles. - Limiter les plages aux colonnes utiles : pour des classeurs plus rapides, limitez les plages et tables au nombre de lignes nécessaire, surtout sur de gros fichiers.
- Documenter vos formules complexes : lorsque vous combinez XLOOKUP, FILTRE et d’autres fonctions, ajoutez des commentaires ou des cellules d’explication pour faciliter la reprise du fichier par un collègue ou par vous-même plus tard.
Préparer ses fichiers Excel pour profiter de XLOOKUP et FILTRE
Les fonctions XLOOKUP et FILTRE sont disponibles dans les versions récentes d’Excel, notamment Microsoft 365 et Excel 2021. Si vous travaillez encore avec une version plus ancienne, il peut être pertinent d’envisager une mise à jour pour bénéficier de ces fonctions modernes, en particulier si vous manipulez régulièrement de grands volumes de données ou des reportings complexes.
Pour aller plus loin, certains compléments et formations spécialisées peuvent vous aider à structurer vos bases de données, automatiser vos tableaux de bord et optimiser vos modèles financiers. Investir du temps dans la maîtrise de ces fonctions avancées vous permettra d’exploiter pleinement le potentiel d’Excel comme outil d’analyse et de reporting professionnel.