Tutoriel XMATCH dans Excel : Vue d'ensemble, syntaxe et applications pratiques

Au cours de vos analyses de données, vous devrez souvent trouver la position relative d'un élément dans un tableau ou une plage de cellules, compte tenu d'une certaine valeur ou d'un certain modèle. 

La fonction XMATCH est le moyen le plus efficace d'effectuer des opérations de recherche de motifs dans Excel. Fonction simple mais puissante, XMATCH possède plusieurs caractéristiques qui permettent une mise en correspondance précise et flexible. 

Dans ce tutoriel, nous aborderons les bases de la fonction Excel XMATCH, nous analyserons sa syntaxe et ses différents paramètres, et nous fournirons des exemples pratiques pour illustrer ses capacités. Nous comparerons également XMATCH à MATCH, une autre fonction populaire utilisée pour des tâches similaires.  Enfin, nous examinerons les pièges les plus courants lors de l'utilisation de la fonction XMATCH et définirons les meilleures pratiques pour garantir une utilisation saine et efficace.

Si vous êtes sur la voie de l'apprentissage d'Excel et que vous souhaitez en savoir plus, consultez notre parcours de compétences cursus de compétences Excel Fundamentals pour apprendre l'essentiel.

Qu'est-ce que XMATCH ?

XMATCH est une fonction flexible et robuste pour les opérations de mise en correspondance. XMATCH() vous permet d'effectuer toutes sortes d'opérations de mise en correspondance, depuis les recherches verticales et horizontales jusqu'aux correspondances exactes, approximatives et partielles. 

Pour ceux qui utilisent Excel depuis longtemps, la fonction MATCH vous est probablement familière. Avant la mise à jour d'Excel introduisant XMATCH, MATCH était la fonction de référence pour les tâches de mise en correspondance. Vous pouvez en apprendre davantage sur ces fonctions dans notre Didacticiel sur les fonctions de recherche Excel.

Cependant, comme nous le verrons plus loin, MATCH peut s'avérer insuffisant dans certains cas. À cet égard, XMATCH peut être considéré comme une mise à jour améliorée et attendue depuis longtemps de la fonction MATCH traditionnelle, offrant une recherche dans toutes les directions et plusieurs types de correspondances, ce qui facilite la recherche et l'adapte à un plus grand nombre de cas d'utilisation.

Notez que la fonction n'est disponible que dans Excel pour Microsoft 365 et Excel 2021. Pour les utilisateurs de versions antérieures d'Excel, MATCH est toujours la seule fonction disponible.

Examinons maintenant la syntaxe et les paramètres de XMATCH. 

Syntaxe et paramètres XMATCH

Voici la syntaxe de base de la fonction XMATCH :

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

La fonction est accompagnée de deux arguments obligatoires (c'est-à-dire, lookup_value et lookup_array) et deux facultatifs (match_mode et search_mode) qui permettent d'effectuer des opérations plus précises et plus souples.

Vous trouverez ci-dessous une description des quatre paramètres :

• Lookup_value: C'est la valeur à rechercher.
• Lookup_array: Il s'agit du tableau ou de la liste dans lequel il faut chercher. Il peut s'agir d'un tableau de consultation vertical ou horizontal.
• Match_mode: vous permet de choisir le type de correspondance. Il existe quatre modes, dont
• 0: Correspondance exacte (mode par défaut)
• -1: Correspondance exacte ou élément le plus petit suivant
• 1: Correspondance exacte ou article suivant le plus grand
• 2: Une correspondance de caractères génériques, tels que * et ?
• Search_modeLe mode de recherche : vous permet de spécifier le mode de recherche, y compris la direction de recherche et l'algorithme. Quatre valeurs sont possibles :
• 1: Recherche du premier au dernier (valeur par défaut)
• -1: Recherche du dernier au premier (recherche inversée).
• 2:  Recherche binaire ascendante. La recherche est effectuée en supposant que le tableau de consultation est trié par ordre croissant, sinon elle renvoie des résultats non valides.
• -2:  Recherche binaire descendante. La recherche est effectuée en supposant que le tableau de consultation est trié par ordre décroissant, sinon elle renvoie des résultats non valides.

Modes de match : Précision et flexibilité

XMATCH comporte un paramètre optionnel match_mode qui vous permet de choisir le type de correspondance. Ces options sont très utiles, car elles permettent d'effectuer des rapprochements précis et des rapprochements approximatifs. Analysons en détail les modes de jeu disponibles :

Correspondance exacte

Correspondance exacte (match_mode = 0 )est le mode de correspondance par défaut de XMATCH. Cela signifie qu'Excel trouvera une correspondance exacte de la valeur de recherche dans le tableau de recherche, sinon il renverra une erreur NA. La correspondance exacte est essentielle lorsque l'exactitude et la précision sont primordiales, par exemple pour trouver des entrées spécifiques dans une base de données.

Voici un exemple de base sur la façon d'utiliser la fonction XMATCH pour trouver la position exacte de "Excel" dans une liste de technologies disponibles dans le catalogue de cours DataCamp. catalogue de cours DataCamp. Nous fournissons la valeur de référence en D3 et le tableau de référence (A3:A14), et Excel donne correctement la position 6. 

Correspondance approximative

Il peut arriver que vous ayez besoin d'une correspondance souple, soit parce que vous ne connaissez pas la valeur exacte de la recherche, soit parce que vous recherchez des résultats approximatifs. 

Heureusement, XMATCH propose deux modes de correspondance qui vous permettent d'obtenir des valeurs exactes ou les plus petites suivantes et des valeurs exactes ou les plus grandes suivantes. 

Dans l'exemple suivant, nous utilisons la correspondance approximative pour trouver les nombres de cours les plus proches de la valeur de recherche 15. 

Lorsque vous utilisez le plus petit suivant (match_mode = -1), Excel renvoie la position 6, car 12 cours est le plus petit nombre le plus proche dans la liste. Dans le cas du plus grand nombre suivant, Excel renvoie 5, car 16 cours est le plus grand nombre le plus proche.

Nous utilisons également la correspondance exacte pour illustrer ce qui se passe lorsqu'Excel ne trouve pas de correspondance exacte.

Caractères génériques et correspondances d'expressions rationnelles

Une caractéristique innovante de XMATCH est la possibilité d'utiliser des caractères génériques de base lorsque vous définissez match_mode sur 2, ainsi que des expressions régulières plus avancées si vous définissez match_mode sur 3. Ces modes de correspondance ne fonctionnent qu'avec des données textuelles.

XMATCH vous permet d'utiliser deux caractères génériques courants :

• ?: pour trouver n'importe quel caractère.
• *: pour correspondre à n'importe quelle séquence de caractères.

Vous trouverez ci-dessous plusieurs exemples d'utilisation des correspondances d'expressions rationnelles. Dans le premier exemple, nous recherchons les technologies qui commencent par un E. Dans le second, nous recherchons les technologies dont le nom contient un A. Enfin, nous recherchons des technologies comportant trois lettres.

Notez que XMATCH renvoie toujours la première occurrence dans le tableau de recherche, sauf indication contraire (voir la section suivante).

Si vous souhaitez utiliser des expressions régulières plus complexes, réglez match_mode sur 3. Les expressions régulières, souvent abrégées en "regex" ou "regexp", permettent de définir un modèle de recherche qui peut être utilisé pour diverses tâches de manipulation de texte telles que la recherche, l'analyse et/ou le remplacement de texte. 

Pour plus de détails sur la recherche de motifs, consultez notre Excel Regex Tutorial : Mastering Pattern Matching with Regular Expressions est un excellent point de départ.

Voici un exemple assez simple : Nous voulons trouver la position d'un cours dont le code comprend trois lettres au début, trois à la fin et deux chiffres au milieu (31).

Modes de recherche XMATCH : Direction et efficacité

XMATCH introduit un nouveau paramètre qui vous permet de contrôler la direction de la recherche. Cette fonction est très utile lorsque vous souhaitez trouver la dernière occurrence plutôt que la première, par exemple pour certaines tâches d'analyse de séries temporelles. 

Examinons en détail les différents modes de recherche.

Recherche inversée

Pour effectuer une recherche inversée, vous pouvez définir le paramètre search_mode sur -1. Par exemple, vous pouvez voir ci-dessous comment trouver la première et la dernière formation suivie par l'utilisateur Pierre.

Recherche binaire

Enfin, le paramètre search_mode offre également la possibilité d'utiliser un algorithme de recherche binaire pour effectuer l'opération de mise en correspondance. La recherche binaire est un algorithme de tri qui fonctionne en divisant de façon répétée l'intervalle de recherche en deux. Il s'agit de l'un des algorithmes de tri les plus efficaces, mais il nécessite que les données d'entrée soient préalablement triées. 

XMATCH vous permet d'utiliser la recherche binaire à partir d'un tableau trié à la fois en ordre croissant (search_mode = 2) et en ordre décroissant (search_mode = -2). Si le tableau de consultation n'est pas trié, la fonction génère une erreur.

Applications pratiques et exemples de XMATCH

XMATCH est bien adapté à un large éventail de tâches Excel, des plus simples aux plus complexes. Dans cette section, nous abordons quelques-unes des applications pratiques les plus convaincantes.

Recherches bidimensionnelles avec INDEX/XMATCH

Les fonctions INDEX et XMATCH peuvent fonctionner en tandem pour effectuer des recherches bidimensionnelles, c'est-à-dire des recherches simultanées en lignes et en colonnes. En combinant ces deux fonctions, nous pouvons créer une recherche dynamique qui fonctionne très bien dans des domaines tels que l'analyse financière, où il est très courant de comparer des variables entre catégories. 

Par exemple, dans l'image ci-dessous, nous combinons INDEX et MATCH pour extraire le nombre d'utilisateurs inscrits à des cours d'Excel au cours du deuxième trimestre. INDEX prend ici trois arguments : le tableau où trouver la valeur, le numéro de ligne et le numéro de colonne. Les valeurs des deux derniers arguments sont fournies par plusieurs fonctions XMATCH.

 Si vous souhaitez changer de technologie ou de trimestre, il vous suffit de modifier les valeurs de recherche dans les fonctions XMATCH, et le résultat de l'INDEX changera en cours de route.

L'INDEX/XMATCH fonctionne de manière très similaire à l'INDEX/MATCH. Si vous souhaitez en savoir plus sur ce puissant tandem avec des exemples pratiques, consultez notre article intitulé comment faire une correspondance d'indexation entre plusieurs critères dans Excel Tutoriel.

Recherches multi-colonnes

Une autre utilisation intéressante de XMATCH consiste à combiner plusieurs critères pour créer des recherches multi-colonnes. Cela peut ne pas sembler intuitif, car XMATCH look nécessite une valeur de recherche et un tableau de recherche pour fonctionner. Mais nous pouvons utiliser la logique booléenne pour créer un tableau temporaire dans la formule XMATCH qui contient des zéros et des uns pour représenter les lignes correspondant aux critères que vous spécifiez. 

Par exemple, supposons que vous souhaitiez trouver la première technologie de la liste qui compte moins de 1 000 utilisateurs inscrits chaque trimestre. Vous pouvez combiner les conditions en combinant les résultats de chaque colonne à l'aide du symbole *. Excel trouvera sous le capot le tableau contenant tous les uns, dans notre cas, Tableau, d'où la position 5.

Filtrage dynamique des tableaux

XMATCH peut également fonctionner avec les fonctions FILTER et ISNUMBER pour améliorer le filtrage dynamique des tableaux.

Vous trouverez ci-dessous comment cela fonctionne. Nous utiliserons ces fonctions pour filtrer les informations sur les cours suivis par les utilisateurs en fonction de leur nom d'utilisateur. 

Tout d'abord, nous utilisons XMATCH pour calculer la position relative de chaque utilisateur dans le tableau en fonction du tableau de recherche contenant les noms que nous utiliserons pour filtrer. Cela nous donnera un tableau avec la position relative de chaque nom, y compris les valeurs nulles pour les utilisateurs non inclus dans la liste de filtrage.

Ensuite, nous convertissons la colonne avec des positions relatives et des valeurs nulles en TRUE ou FALSE à l'aide de la fonction ISNUMBER.

Enfin, nous utilisons FILTER pour sélectionner toutes les colonnes que nous voulons afficher, et la colonne avec TRUE et FALSE pour filtrer les lignes.

Aborder les valeurs nulles avec l'ISNA

Si XMATCH ne trouve aucune correspondance, il renvoie une erreur NA. Bien que cela soit déjà instructif, vous pouvez embellir les résultats et éviter les erreurs d'interprétation en utilisant les fonctions ISNA et IF.

Imaginez que nous disposions d'un tableau contenant les cours suivis par Sam et Peter, et que nous souhaitions savoir quels cours ils ont en commun. Si nous utilisons simplement XMATCH, Excel renverra la position relative des cours communs, et des valeurs nulles dans le cas contraire. Si nous passons XMATCH comme argument de ISNA, la fonction renverra TRUE ou FALSE à la place. Ensuite, en utilisant la fonction IF, nous pouvons modifier les valeurs booléennes pour d'autres messages.

Recherche binaire pour les grands ensembles de données

Nous avons déjà mentionné que XMATCH vous permet d'utiliser un algorithme de recherche binaire lors des opérations de mise en correspondance. Cette fonction peut changer la donne lorsqu'il s'agit de traiter de grands ensembles de données. À condition que le tableau de recherche soit trié, l'utilisation du moteur de recherche binaire peut accélérer considérablement vos opérations de recherche.

XMATCH vs MATCH : Principales différences

Comme nous l'avons déjà mentionné, XMATCH a été conçu pour remplacer la fonction traditionnelle MATCH. Les deux ont le même objectif, mais XMATCH est doté d'un ensemble de nouvelles capacités qui le rendent plus puissant que MATCH. Vous trouverez ci-dessous une liste des principales différences entre XMATCH et MATCH :

Pièges courants et solutions

Le XMATCH peut être utilisé à de nombreuses fins. Toutefois, il est important de mentionner les problèmes courants que vous pouvez rencontrer en l'utilisant. Voici une liste de quelques pièges habituels et des conseils pour y remédier.

• Vérifiez les types de données : XMATCH aura un comportement inattendu si vous l'appliquez à une plage dont le type de données est incorrect. Par conséquent, avant d'appliquer la fonction, assurez-vous que vous avez défini les bons types de données pour vos colonnes.
• Cohérence de la gamme : Bien que XMATCH permette des plages de consultation horizontales et verticales, le fait de les mélanger peut entraîner des problèmes inattendus et des problèmes de performance. Dans la mesure du possible, essayez d'être cohérent et choisissez des plages horizontales ou verticales.
• Tirez parti des modes de correspondance : XMATCH propose plusieurs modes de jeu. Le mode par défaut est la correspondance exacte, mais vous pouvez également affiner vos recherches en utilisant des recherches approximatives et des opérations regex.
• Meilleur avec les tableaux triés : Globalement, les performances de XMATCH seront toujours supérieures si vous l'appliquez à des tableaux triés. C'est vrai pour la fonction par défaut, et c'est une condition préalable obligatoire si vous voulez utiliser la recherche binaire. 
• Traitement des erreurs N/A :  Comme nous l'avons déjà mentionné, XMATCH génère une erreur NA s'il n'y a pas de correspondance dans vos recherches. La meilleure façon de résoudre ce problème et d'améliorer la lisibilité est de transmettre les résultats à une combinaison de fonctions IF ET ISNA, ou à une fonction IFNA.

Considérations sur les performances

XMATCH n'est pas la seule fonction permettant d'effectuer des opérations de recherche dans Excel. Par exemple, vous pouvez envisager d'utiliser VLOOKUP, HLOOKUPou même d'anciennes fonctions comme MATCH. Cependant, en termes de vitesse de recherche, XMATCH est sans doute le plus rapide, en particulier si vous l'appliquez à des tableaux ordonnés, où vous pouvez également tirer parti de la puissance de la recherche binaire.

Il convient également de mentionner que XMATCH est conçu comme une formule de tableau dynamique, ce qui signifie qu'il peut renvoyer des tableaux de taille variable en fonction des données d'entrée. Les formules de tableaux dynamiques sont particulièrement bien adaptées lorsque vous travaillez avec de grands ensembles de données ou des calculs complexes. 

Conclusion

Félicitations pour avoir réussi à aller jusqu'au bout. XMATCH est une fonction puissante, et vous ne pouvez pas vous en passer dans vos analyses de données Excel. Que ce soit en tant que fonction autonome ou en tandem avec d'autres fonctions Excel, XMATCH portera vos opérations de recherche à un niveau supérieur. 

Mais il y a beaucoup plus à savoir dans le monde merveilleux d'Excel. Consultez nos cours, blogs et tutoriels dédiés pour devenir un magicien d'Excel :

• XLOOKUP() vs. VLOOKUP() : Une comparaison pour les utilisateurs d'Excel
• Manipulation de données dans Excel - Aide-mémoire 
• Excel | Cours sur les techniques avancées
• Intermédiaire Power Query dans Excel 
• Aide-mémoire des raccourcis Excel 
• Formules Excel - Aide-mémoire 
• Comment faire une correspondance d'indexation entre plusieurs critères dans Excel ? 
• Traitement des données avec INDEX-MATCH dans les tableurs 
• Tutoriel Excel Regex : Maîtriser la correspondance des motifs avec les expressions régulières