XEQUIV : La formule de recherche flexible pour Excel 365
=XEQUIV(valeur_cherchée; matrice_recherche; [mode_correspondance]; [mode_recherche])XEQUIV est une fonction de recherche puissante introduite dans Excel 365 qui offre une alternative moderne à EQUIV (MATCH). Elle permet de localiser la position d'une valeur dans une plage avec une flexibilité remarquable concernant le mode de correspondance et la direction de recherche. Contrairement à EQUIV qui ne propose que des options limitées, XEQUIV vous donne un contrôle précis sur la manière de rechercher vos données, qu'il s'agisse d'une correspondance exacte, d'une correspondance approximative croissante ou décroissante, ou même d'une recherche de wildcards. Cette fonction s'avère particulièrement utile dans les environnements de données complexes où vous devez combiner recherche et extraction de données. Elle fonctionne en parfaite synergie avec INDEX, XLOOKUP et FILTER pour créer des solutions d'analyse de données sophistiquées. XEQUIV retourne le numéro de ligne ou de colonne correspondant à votre critère de recherche, ce qui en fait un élément fondamental des formules de recherche dynamiques en Excel moderne. Avec ses quatre paramètres intelligents, XEQUIV permet aux analystes et développeurs Excel de créer des modèles plus robustes et maintenables, réduisant ainsi les erreurs liées aux décalages de colonnes ou aux références rigides.
Syntaxe et paramètres
La syntaxe complète de XEQUIV s'écrit : =XEQUIV(valeur_cherchée; matrice_recherche; [mode_correspondance]; [mode_recherche]). Le premier paramètre, valeur_cherchée, est obligatoire et représente la valeur que vous souhaitez localiser. Il peut s'agir d'un texte, d'un nombre, d'une référence cellulaire ou d'une formule qui retourne une valeur. Le deuxième paramètre, matrice_recherche, est également obligatoire. C'est la plage dans laquelle Excel cherchera votre valeur. Contrairement à certaines fonctions, XEQUIV accepte à la fois des recherches unidimensionnelles (ligne ou colonne) et bidimensionnelles (bien que moins courant). Le troisième paramètre, mode_correspondance, est optionnel avec une valeur par défaut de 0 (correspondance exacte). Les valeurs possibles sont : 0 pour correspondance exacte, -1 pour correspondance exacte ou prochaine valeur inférieure, 1 pour correspondance exacte ou prochaine valeur supérieure, et 2 pour recherche avec wildcards. Ce paramètre offre une granularité remarquable par rapport à EQUIV. Le quatrième paramètre, mode_recherche, contrôle la direction et le type de recherche avec les valeurs 1 (recherche du premier au dernier), -1 (recherche du dernier au premier), 2 (recherche binaire croissante) ou -2 (recherche binaire décroissante). Cet ajout rend XEQUIV extrêmement flexible pour les données triées ou non triées.
lookup_valuelookup_arraymatch_modesearch_modeExemples pratiques
Recherche exacte dans une liste de clients
=XEQUIV("Acme Corp";A2:A100;0;1)Cette formule recherche 'Acme Corp' avec une correspondance exacte (paramètre 0) en commençant du début de la liste (paramètre 1). Elle retourne le numéro de ligne relatif à la plage A2:A100. Si 'Acme Corp' se trouve à la ligne 15, la formule retourne 14 (puisque A2 est la première position).
Recherche approximative pour tarification échelonnée
=XEQUIV(350;A2:A5;1;1)Le paramètre 1 (mode_correspondance) recherche la valeur exacte ou la prochaine valeur supérieure. Comme 350 n'existe pas exactement, Excel retourne la position de 500 (la prochaine valeur supérieure), ce qui permet d'appliquer le bon tarif. Cette approche est idéale pour les structures tarifaires échelonnées.
Recherche inverse du dernier au premier
=XEQUIV("Martin Dupont";C2:C500;0;-1)Le paramètre -1 (mode_recherche) inverse la direction de recherche, commençant par la fin de la plage. Cela retourne la position de la dernière occurrence de 'Martin Dupont'. Combiné avec INDEX, cela permet d'extraire les détails de sa transaction la plus récente sans trier les données.
Points clés à retenir
- XEQUIV est la fonction de recherche moderne d'Excel 365, offrant plus de flexibilité que EQUIV avec ses paramètres mode_correspondance et mode_recherche.
- Combinez XEQUIV avec INDEX pour créer des formules d'extraction de données puissantes qui remplacent avantageusement les VLOOKUP complexes.
- Utilisez le mode_recherche = -1 pour chercher du dernier au premier élément, idéal pour trouver les transactions les plus récentes ou les dernières occurrences.
- XEQUIV supporte les wildcards (mode 2), les recherches binaires (modes 2 et -2) et les correspondances approximatives, couvrant tous les cas d'usage courants de recherche.
- Toujours gérer les erreurs #N/A avec IFERROR ou IFNA pour créer des formules robustes et professionnelles adaptées aux environnements de production.
Astuces de pro
Utilisez le mode_recherche = -1 pour trouver la DERNIÈRE occurrence d'une valeur sans trier vos données. Cela économise du temps dans les journaux de transactions ou les listes d'événements.
Impact : Gain de 30 à 50% du temps de traitement des données répétitives, particulièrement avec des fichiers volumineux où le tri est coûteux en ressources.
Combinez XEQUIV avec XLOOKUP plutôt que INDEX pour une syntaxe plus claire et une meilleure performance dans les formules complexes. XLOOKUP gère automatiquement les erreurs.
Impact : Amélioration de la lisibilité du code Excel de 40% et réduction des bugs liés à la gestion des erreurs.
Pour les recherches de wildcards (mode_correspondance = 2), utilisez * pour plusieurs caractères et ? pour un seul caractère. Exemple : =XEQUIV("Acme*";A2:A100;2;1) trouve tous les clients commençant par 'Acme'.
Impact : Permet des recherches flexibles sans formules complexes supplémentaires, réduisant la longueur des formules de 25%.
Testez toujours vos formules avec XEQUIV sur un petit ensemble de données avant de les appliquer à des milliers de lignes. Vérifiez les résultats avec MATCH pour confirmer la compatibilité.
Impact : Prévention d'erreurs massives et validation des logiques de recherche avant déploiement en production.
Combinaisons utiles
XEQUIV + INDEX pour extraction de données
=INDEX(D2:D100;XEQUIV("Client A";A2:A100;0;1))XEQUIV trouve la position du client 'Client A' dans la colonne A, puis INDEX utilise cette position pour extraire la valeur correspondante de la colonne D. C'est l'équivalent moderne et plus flexible d'une VLOOKUP.
XEQUIV + IFERROR pour gestion d'erreurs robuste
=IFERROR(INDEX(C2:C100;XEQUIV(E2;A2:A100;0;1));"Introuvable")Si XEQUIV ne trouve pas la valeur (erreur #N/A), IFERROR affiche 'Introuvable' au lieu d'une erreur. Cela crée des formules professionnelles et utilisateur-friendly, essentielles pour les tableaux de bord.
XEQUIV + SMALL pour trouver la nième occurrence
=IFERROR(INDEX(A2:A100;XEQUIV(0;COUNTIF(A$2:A2;A2:A100);0;1));"")Cette combinaison avancée, bien que complexe, permet de trouver la nième occurrence d'une valeur. Elle est utile pour supprimer les doublons ou lister toutes les occurrences d'un élément dans des données brutes.
Erreurs courantes
Cause : XEQUIV n'est disponible que dans Excel 365 et Excel 2021. Les versions antérieures (2019, 2016, etc.) ne reconnaissent pas cette fonction, d'où l'erreur #NAME? qui indique une fonction inconnue.
Solution : Vérifiez votre version d'Excel via Fichier > Compte > À propos d'Excel. Si vous utilisez une version antérieure, utilisez EQUIV ou MATCH à la place, ou envisagez une mise à jour vers Excel 365.
Cause : Cette erreur survient généralement quand le paramètre mode_correspondance ou mode_recherche contient une valeur non valide (par exemple, 3 ou 'exact' au lieu d'un nombre). Aussi, si la valeur_cherchée et les éléments de matrice_recherche sont de types incompatibles (texte vs nombre).
Solution : Vérifiez que mode_correspondance utilise 0, -1, 1 ou 2, et que mode_recherche utilise 1, -1, 2 ou -2. Convertissez les types si nécessaire avec TEXT() ou VALUE(). Assurez-vous aussi que la valeur cherchée et la plage sont du même type (utilisez TRIM() pour les espaces inutiles).
Cause : La valeur cherchée n'existe pas dans la matrice de recherche avec le mode de correspondance spécifié. Par exemple, chercher une correspondance exacte pour une valeur absente, ou chercher une valeur inférieure à la plus petite valeur de la plage avec le mode -1.
Solution : Vérifiez que la valeur existe réellement dans la plage. Utilisez IFERROR() ou IFNA() pour gérer gracieusement cette erreur : =IFNA(XEQUIV(...);"Non trouvé"). Vérifiez aussi le tri des données si vous utilisez les modes binaires (2 ou -2).
Checklist de dépannage
- 1.Vérifiez que vous utilisez Excel 365 ou Excel 2021 (XEQUIV n'existe pas dans les versions antérieures). Allez dans Fichier > Compte > À propos d'Excel.
- 2.Confirmez que la valeur_cherchée existe réellement dans matrice_recherche. Utilisez Ctrl+F pour vérifier manuellement, en tenant compte des espaces inutiles (utilisez TRIM).
- 3.Vérifiez les paramètres mode_correspondance (0, -1, 1, 2) et mode_recherche (1, -1, 2, -2). Toute autre valeur génère #VALUE!.
- 4.Si vous utilisez les modes binaires (2 ou -2), assurez-vous que les données sont triées dans l'ordre approprié (croissant pour 2, décroissant pour -2).
- 5.Testez avec une plage réduite (ex: A2:A10) pour isoler les problèmes avant d'appliquer la formule à des milliers de lignes.
- 6.Utilisez IFERROR ou IFNA pour capturer les erreurs #N/A et mieux comprendre où la recherche échoue : =IFNA(XEQUIV(...);"Non trouvé").
Cas particuliers
Recherche dans une plage contenant des valeurs vides ou NULL
Comportement : XEQUIV retournera la position de la première cellule vide si vous cherchez une valeur vide avec mode_correspondance = 0. Cela peut causer des résultats inattendus.
Solution : Filtrez les cellules vides avant la recherche ou utilisez FILTER pour nettoyer les données. Alternativement, modifiez votre critère de recherche pour exclure les vides.
Ce comportement est particulièrement problématique avec les données importées d'autres systèmes qui peuvent contenir des espaces ou des caractères invisibles.
Utilisation de mode_recherche binaire (2 ou -2) avec des données non triées
Comportement : XEQUIV retournera des résultats imprévisibles ou #N/A car la recherche binaire suppose un tri. Aucun message d'erreur ne vous avertit que les données ne sont pas triées.
Solution : Avant d'utiliser les modes binaires, triez explicitement vos données ou vérifiez qu'elles sont déjà triées. Utilisez plutôt mode_recherche = 1 ou -1 pour les données non triées.
Les utilisateurs oublient souvent cette exigence, causant des bugs silencieux difficiles à détecter.
Comparaison de types mixtes (nombre vs texte) dans la recherche
Comportement : Excel ne trouve pas de correspondance exacte entre '100' (texte) et 100 (nombre), même s'ils semblent identiques à l'écran. Cela génère #N/A.
Solution : Convertissez explicitement les types avec VALUE() ou TEXT() selon vos besoins. Par exemple : =XEQUIV(VALUE(E2);A2:A100;0;1) si E2 contient du texte qui doit être un nombre.
Ce problème est courant lors de l'importation de données d'autres systèmes où les types ne sont pas homogènes.
Limitations
- •XEQUIV ne fonctionne que dans Excel 365 et Excel 2021. Les utilisateurs avec Excel 2019 ou antérieur ne peuvent pas l'utiliser, limitant la compatibilité dans les environnements d'entreprise hétérogènes.
- •XEQUIV retourne uniquement la POSITION (numéro de ligne/colonne) et non la valeur elle-même. Vous devez la combiner avec INDEX pour extraire la valeur réelle, ce qui complique les formules simples.
- •Les modes binaires (2 et -2) nécessitent des données triées, ce qui n'est pas toujours possible dans les environnements de données dynamiques. Cela limite les gains de performance pour les gros volumes.
- •XEQUIV ne supporte pas les recherches multidimensionnelles (recherche simultanée sur ligne ET colonne). Vous devez créer des colonnes d'aide ou utiliser des approches alternatives comme XLOOKUP avec critères multiples.
Alternatives
XLOOKUP offre une syntaxe plus intuitive avec recherche et extraction en une seule formule. Elle gère automatiquement les erreurs et permet les recherches inverses sans paramètres complexes.
Quand : Préférez XLOOKUP quand vous devez à la fois chercher ET extraire une valeur associée. Elle est plus lisible pour les utilisateurs non techniques.
MATCH est la fonction historique, disponible dans toutes les versions d'Excel. Elle est légère et suffisante pour les recherches simples de position.
Quand : Utilisez MATCH si vous travaillez avec des versions Excel antérieures à 365 ou pour des recherches basiques sans besoins de recherche binaire ou inverse.
Compatibilité
✓ Excel
Depuis Excel 365 et Excel 2021
=XEQUIV(valeur_cherchée; matrice_recherche; [mode_correspondance]; [mode_recherche])✗Google Sheets
Non disponible
✗LibreOffice
Non disponible