# Utiliser la fonction VBA Match pour rechercher des valeurs
La fonction Match en VBA représente un outil puissant pour localiser rapidement des données dans des plages Excel, offrant une alternative performante aux boucles traditionnelles. Dans un contexte où l’automatisation des traitements de données devient essentielle, maîtriser cette méthode vous permet d’optimiser significativement vos macros. Contrairement aux recherches manuelles ou aux itérations complexes, Match retourne directement la position d’un élément dans un tableau, facilitant ainsi les opérations de recherche et de récupération de données. Cette fonction s’avère particulièrement précieuse lorsque vous travaillez avec de grands volumes d’informations nécessitant des recherches répétées ou des validations croisées. Comprendre ses subtilités, ses paramètres et ses limites constitue un investissement qui transformera votre approche du développement VBA.
Syntaxe et paramètres de la fonction WorksheetFunction.Match en VBA
La fonction Match en VBA s’utilise via l’objet Application.WorksheetFunction, permettant d’accéder aux fonctions Excel natives directement depuis votre code. Cette approche garantit une compatibilité optimale avec les calculs de feuille de calcul tout en bénéficiant de la puissance du langage VBA. La syntaxe complète nécessite trois arguments principaux, dont seuls les deux premiers sont obligatoires pour effectuer une recherche basique.
Structure de la méthode Application.WorksheetFunction.Match
La structure formelle s’écrit Application.WorksheetFunction.Match(Arg1, Arg2, Arg3), où chaque argument possède une fonction spécifique. L’expression complète retourne un nombre de type Double représentant la position relative de l’élément recherché dans la plage spécifiée. Cette position commence toujours à 1, conformément à la logique Excel, et non à 0 comme dans certains langages de programmation. Lorsque vous intégrez cette fonction dans vos procédures, vous devez systématiquement gérer le risque d’erreur si aucune correspondance n’est trouvée, car Match génère alors une erreur d’exécution qui interrompt votre code sans gestion appropriée.
Paramètre lookup_value : types de données acceptés et conversion
Le paramètre Lookup_value (Arg1) accepte différents types de données variant, incluant les nombres, les chaînes de caractères et les valeurs logiques. Cette flexibilité vous permet de rechercher aussi bien des références numériques que des libellés textuels ou des états booléens. Cependant, une attention particulière s’impose concernant les conversions implicites de types qui peuvent survenir. Par exemple, rechercher la valeur numérique 123 dans une plage contenant « 123 » en format texte échouera sans correspondance exacte, sauf configuration spécifique du paramètre Match_type.
Les dates représentent un cas particulier nécessitant une vigilance accrue. Excel stocke les dates comme des valeurs numériques sérielles, ce qui signifie que vous devez vous assurer de la cohérence entre le format de votre variable de recherche et celui des données dans la plage cible. Une date formatée comme chaîne « 15/06/2023 » ne correspondra pas à une date Excel stockée numériquement, générant une erreur de correspondance frustrante si cette distinction n’est pas anticipée.
Paramètre lookup_array : plages range et tableaux variant
Le paramètre Lookup_array (Arg2) définit la zone de recherche et accepte deux formats principaux : une référence Range pointant vers des cellules Excel, ou un tableau Variant stocké en mémoire. L’utilisation d’un objet Range directement, comme Worksheets("
Le cas le plus courant consiste à passer directement une colonne ou une plage contiguë, par exemple Worksheets("Feuil1").Range("B:B") ou Range("A2:A1000"). Dans ce scénario, Excel parcourt les cellules visibles du Range pour y rechercher la valeur spécifiée. Une autre approche, souvent plus performante, est de charger la plage dans un tableau Variant via Dim arr As Variant: arr = Range("A2:A1000").Value, puis d’utiliser Application.Match sur ce tableau en mémoire. Cette technique réduit les accès à la feuille, ce qui peut faire une grande différence sur des dizaines de milliers de lignes.
Notez que lorsque vous utilisez un tableau Variant bidimensionnel issu d’un Range (arr(ligne, colonne)), la fonction Match ne s’applique qu’à une dimension. En pratique, il faut donc lui passer un « vecteur » (une seule colonne ou une seule ligne du tableau), par exemple en utilisant une fonction intermédiaire qui extrait la colonne souhaitée. Enfin, assurez-vous que Lookup_array ne contient pas de types hétérogènes (mélange de dates, textes, erreurs) si vous souhaitez des résultats prévisibles, car les conversions implicites peuvent provoquer des résultats inattendus ou des erreurs d’exécution.
Paramètre match_type : différences entre -1, 0 et 1
Le troisième argument, Match_type, contrôle la logique de comparaison utilisée par la fonction Match. Trois valeurs sont possibles : -1, 0 et 1. Si vous omettez ce paramètre, Excel considère par défaut la valeur 1, ce qui correspond à une recherche approximative dans une plage triée en ordre croissant. Choisir le bon Match_type est donc crucial pour éviter des résultats erronés, en particulier lorsque la plage n’est pas triée.
Avec Match_type = 0, la fonction effectue une recherche exacte : elle renvoie la position de la première valeur strictement égale à Lookup_value, quel que soit l’ordre de la plage. Avec Match_type = 1, Match renvoie la plus grande valeur inférieure ou égale à la valeur recherchée, à condition que Lookup_array soit trié par ordre croissant. Enfin, avec Match_type = -1, Match renvoie la plus petite valeur supérieure ou égale à la valeur recherchée, mais cette fois pour une plage triée par ordre décroissant. En pratique, pour des macros robustes et prévisibles, on privilégie souvent Match_type = 0, sauf cas d’usage bien maîtrisés.
Gestion des erreurs error 2042 et IsError dans match
Lorsqu’aucune correspondance n’est trouvée, la fonction Match côté feuille renvoie l’erreur #N/A, qui correspond en VBA au code d’erreur interne Error 2042. Si vous utilisez Application.WorksheetFunction.Match et que la recherche échoue, vous obtiendrez une erreur d’exécution (souvent 1004) qui interrompt votre macro. Pour éviter cela, il est préférable d’utiliser la version Application.Match seule, sans WorksheetFunction. Dans ce cas, le résultat sera un Variant pouvant contenir soit un nombre (position trouvée), soit une valeur d’erreur VBA.
Vous pouvez alors tester cette valeur avec la fonction IsError ou plus précisément IsNumeric pour savoir si une correspondance a été trouvée. Par exemple : res = Application.Match(lookupVal, rng, 0) puis If IsError(res) Then .... Cette approche évite le recours systématique à On Error et rend votre code plus lisible et plus maintenable. De plus, elle simplifie la gestion de cas particuliers comme les dates ou les valeurs nulles, que vous pouvez traiter de manière conditionnelle avant ou après l’appel à Match.
Implémentation de match pour la recherche exacte avec match_type 0
Recherche de valeurs textuelles dans des colonnes non triées
La situation la plus fréquente en entreprise consiste à rechercher un identifiant, un nom de client ou un code produit dans une colonne non triée. Dans ce contexte, Match_type = 0 s’impose. Vous pouvez, par exemple, chercher la valeur contenue en B4 dans la colonne B complète : ligne = Application.Match(Range("B4").Value, Columns(2), 0). Si la valeur existe, vous obtiendrez sa position relative dans la colonne ; sinon, le résultat sera une erreur que vous pourrez intercepter avec IsError.
Un piège classique concerne les espaces superflus et les différences de casse. Même si Match est insensible à la casse, un espace en fin de cellule suffit à faire échouer une correspondance. Dans un scénario de nettoyage de données, il peut être judicieux de normaliser les textes avec Trim et éventuellement UCase ou LCase avant la recherche. Vous pouvez, par exemple, préparer une variable critere = Trim$(UCase$(Range("B4").Value)) et parcourir une plage nettoyée ou prétraitée en conséquence.
Utilisation de match avec des critères numériques stricts
Pour les valeurs numériques (montants, quantités, numéros de projet), Match_type = 0 garantit une égalité stricte sans interprétation approximative. Dans l’exemple suivant, nous cherchons un numéro de projet dans la colonne B : res = Application.Match(TLogProjectNum, Worksheets("Projects").Columns("B"), 0). Ici, TLogProjectNum doit être du même type que les données de la colonne, idéalement un Long ou un Double selon la nature des nombres. Si la colonne contient des nombres stockés en texte, il faudra soit convertir la colonne, soit convertir le critère en chaîne.
Une bonne pratique consiste à typer explicitement vos variables numériques, par exemple Dim TLogProjectNum As Long plutôt que Integer, plus limité. Vous pouvez également vérifier au préalable que la valeur existe grâce à Application.CountIf pour éviter de lancer une recherche inutile. Cette validation préalable réduit le risque d’erreur et permet d’afficher des messages plus clairs à l’utilisateur, par exemple « Projet introuvable, vérifiez le numéro saisi ».
Combinaison match et index pour créer des RECHERCHEV dynamiques
La combinaison INDEX + MATCH en VBA reproduit, et dépasse souvent, la célèbre fonction RECHERCHEV (VLOOKUP) d’Excel. L’idée est simple : Match renvoie la position de la ligne (ou de la colonne), et Index renvoie ensuite la valeur située à cette position dans une autre plage. Par exemple, pour récupérer le nom d’un client à partir de son ID situé en A2, vous pouvez écrire : ligne = Application.Match(Range("A2").Value, Range("Clients!A:A"), 0) puis nom = Application.Index(Range("Clients!B:B"), ligne).
Cette approche présente deux avantages majeurs. D’abord, vous n’êtes plus limité à une recherche vers la droite comme avec RECHERCHEV : vous pouvez chercher dans une colonne et retourner une valeur située à gauche ou à droite sans contrainte. Ensuite, vous découplez complètement la logique de recherche et la plage de retour, ce qui rend votre macro beaucoup plus flexible et plus facile à maintenir. Dans des projets VBA complexes, cette stratégie peut remplacer avantageusement des dizaines de formules RECHERCHEV dispersées dans les feuilles.
Recherche insensible à la casse avec LCase et UCase
Par défaut, la fonction Match en VBA ne tient pas compte de la casse : « Dupont » et « DUPONT » sont considérés comme identiques. Mais que faire si vous souhaitez, au contraire, réaliser une recherche insensible à la casse sur des données qui ne le sont pas encore, ou au contraire forcer une logique précise ? Une technique consiste à standardiser les valeurs avant la comparaison, en appliquant LCase ou UCase sur les textes.
Concrètement, vous pouvez créer une colonne auxiliaire (ou un tableau en mémoire) contenant les valeurs converties en majuscules, puis utiliser Match sur cette colonne standardisée. Par exemple, critere = UCase$(Range("B4").Value), puis ligne = Application.Match(critere, plageStandardisee, 0). Cette approche fonctionne particulièrement bien lorsque l’on doit fiabiliser des imports de données provenant de sources multiples, où la casse et les accents varient. Vous évitez ainsi de parcourir chaque cellule manuellement avec des boucles, tout en gardant un contrôle précis sur la façon dont les comparaisons sont effectuées.
Recherche approximative avec match_type 1 et -1 dans des plages triées
Configuration match_type 1 pour les valeurs inférieures ou égales
Les recherches approximatives avec Match_type = 1 sont très utiles pour travailler avec des barèmes, des tranches de prix ou des paliers de commission. Dans ce mode, la fonction Match renvoie la position de la plus grande valeur inférieure ou égale à la valeur recherchée. Par exemple, si votre tableau contient les seuils {0, 1000, 5000, 10000} et que vous cherchez 3200, Match renverra la position de 1000, à condition que la plage soit triée par ordre croissant.
Vous pouvez utiliser ce comportement pour déterminer rapidement la tranche applicable à un montant donné, sans écrire de longues structures Select Case. Il suffit de vérifier ensuite la valeur renvoyée, puis de l’utiliser comme index pour un autre tableau de résultats (taux, catégories, etc.). N’oubliez jamais, cependant, que si la plage n’est pas triée correctement, les résultats risquent d’être complètement erronés sans générer d’erreur explicite, ce qui peut rendre le débogage délicat.
Utilisation de match_type -1 pour les recherches descendantes
Moins connue, la valeur Match_type = -1 permet de travailler avec des données triées par ordre décroissant, par exemple des dates les plus récentes en haut ou des scores classés du plus élevé au plus faible. Dans ce cas, Match renvoie la position de la plus petite valeur supérieure ou égale à la valeur recherchée. Si vous avez une liste de dates triées du plus récent au plus ancien et que vous cherchez une date de référence, ce mode vous permet par exemple de trouver la première date postérieure ou égale à un seuil.
Ce type de recherche est particulièrement pratique pour analyser des séries temporelles ou des historiques de prix triés par ordre décroissant. Comme pour le mode 1, la condition impérative est que votre plage soit correctement triée dans le bon sens. En pratique, il est souvent utile de documenter cette contrainte directement dans le code ou les commentaires, voire d’ajouter une étape de tri systématique avant l’appel à Match pour éviter toute dérive dans le temps.
Tri préalable avec Range.Sort avant l’application de match
Étant donné que les modes approximatifs dépendent fortement de l’ordre des données, automatiser le tri préalable dans vos macros est une bonne habitude. Vous pouvez, par exemple, utiliser Range.Sort pour trier la plage de recherche avant d’appeler Match. Un exemple typique : With Range("A2:A1000"): .Sort Key1:=.Cells(1,1), Order1:=xlAscending, Header:=xlNo: End With suivi d’un Application.Match(valeur, Range("A2:A1000"), 1). De cette façon, vous vous assurez que les conditions requises par Match sont toujours respectées.
Ce tri automatique est d’autant plus important dans des classeurs collaboratifs, où plusieurs utilisateurs peuvent insérer ou modifier des lignes sans se soucier de l’ordre global. Sans cette étape, une mise à jour anodine peut fausser silencieusement vos recherches approximatives. Vous pouvez aussi implémenter un contrôle simple, par exemple en vérifiant si la première valeur est bien inférieure ou égale à la dernière (ou l’inverse) avant de lancer la recherche et, en cas d’anomalie, déclencher un tri ou afficher un message d’avertissement.
Techniques avancées : recherches bidimensionnelles et multicritères
Méthode de concaténation pour recherches multicritères avec match
Dans de nombreux cas métier, un seul critère ne suffit pas : vous devez par exemple trouver une ligne correspondant à la fois à un client et à une date, ou à un produit et à une région. La fonction Match ne gère pas nativement les recherches multicritères, mais une astuce consiste à concaténer les critères dans une colonne auxiliaire (ou un tableau en mémoire) et à faire la recherche sur cette concaténation. Par analogie, on fabrique une « clé composite » unique, comme une clé de serrure combinant plusieurs crans.
Concrètement, vous pouvez créer dans une colonne cachée une formule du type =Client & "|" & Date, puis en VBA construire la même clé avec cle = client & "|" & Format$(maDate,"yyyy-mm-dd"). Il suffit alors d’appeler Application.Match(cle, plageCles, 0). Cette approche est simple, efficace et évite les boucles lourdes. Elle fonctionne tout aussi bien en chargeant les données dans un tableau Variant, puis en construisant un second tableau de clés en mémoire pour y rechercher avec Match, ce qui est souvent plus rapide sur de gros volumes.
Combinaison match imbriqués pour coordonnées lignes-colonnes
Pour des recherches bidimensionnelles (ligne/colonne), la combinaison de deux Match imbriqués est particulièrement élégante. Vous pouvez, par exemple, chercher le nom d’un mois dans une ligne d’en-têtes et le nom d’un produit dans une colonne de libellés, puis utiliser Index avec ces deux indices pour récupérer la valeur en croisement. La structure générale ressemble à ceci : ligne = Application.Match(produit, plageProduits, 0), col = Application.Match(mois, plageMois, 0), puis valeur = Application.Index(plageDonnees, ligne, col).
Cette technique s’apparente à une RECHERCHEH + RECHERCHEV combinées, mais totalement pilotée par VBA. Elle se prête particulièrement bien à la construction de tableaux de bord dynamiques, où l’utilisateur choisit des critères dans des listes déroulantes et la macro renvoie instantanément la valeur correspondante dans un cube de données à deux dimensions. En évitant les boucles imbriquées, vous gagnez en performance et en lisibilité, tout en gardant la puissance expressive des fonctions Excel classiques.
Alternative avec filter et find pour structures complexes
Dans certains cas, les structures sont trop complexes pour être traitées efficacement par Match seul : tableaux multidimensionnels, critères partiels, recherches sur plusieurs colonnes non adjacentes, etc. Deux alternatives puissantes se présentent alors : la fonction Filter pour les tableaux unidimensionnels et la méthode Range.Find pour les recherches dans la feuille. Filter permet de créer un sous-tableau ne contenant que les éléments correspondant à une chaîne donnée, ce qui est pratique pour compter les occurrences ou vérifier rapidement la présence d’une valeur.
La méthode Range.Find, quant à elle, offre une flexibilité accrue pour rechercher dans une plage en spécifiant divers paramètres (valeur, formules, casse, partie de texte, etc.). Vous pouvez, par exemple, l’utiliser pour repérer toutes les cellules contenant une sous-chaîne donnée, puis parcourir les résultats avec FindNext. En pratique, vous choisirez Match pour des recherches simples et rapides sur une dimension, et basculerez vers Filter ou Find dès que la logique devient plus riche (recherches partielles, multiples colonnes, conditions sur le formatage, etc.).
Optimisation avec dictionary object versus match répétitif
Lorsque vous devez effectuer un très grand nombre de recherches sur la même plage (par exemple, vérifier des milliers d’identifiants dans une liste de référence), appeler Match de manière répétitive peut devenir coûteux en temps de calcul. Une approche plus performante consiste à charger les données dans un Scripting.Dictionary, qui fonctionne comme un dictionnaire de clés/valeurs en mémoire. Vous parcourez la plage une seule fois, ajoutez chaque valeur comme clé, puis utilisez la méthode Exists du Dictionary pour tester instantanément la présence d’une valeur.
Cette technique s’apparente à la création d’un index en base de données : l’investissement initial en temps de construction est compensé par des recherches quasi instantanées ensuite. Pour des scénarios de rapprochement de fichiers, de contrôles de doublons ou de validation d’import massifs, l’utilisation d’un Dictionary peut diviser par 5 ou 10 le temps d’exécution par rapport à des appels Match en boucle. Vous pouvez toujours conserver Match comme solution de repli pour les cas ponctuels, mais pour des volumes importants, le Dictionary devient très vite votre meilleur allié.
Gestion robuste des erreurs et validation dans les procédures match
Implémentation on error resume next et récupération Err.Number
Certains développeurs VBA choisissent d’encapsuler l’appel à Match dans un bloc On Error Resume Next pour intercepter les erreurs sans interrompre l’exécution. Cette approche peut être efficace si elle est utilisée avec parcimonie et immédiatement suivie de contrôles explicites du numéro d’erreur. Par exemple, vous pouvez écrire : On Error Resume Next: res = Application.WorksheetFunction.Match(...): If Err.Number <> 0 Then ...: Err.Clear: On Error GoTo 0.
Cela dit, cette technique présente deux inconvénients principaux. D’une part, elle masque potentiellement d’autres erreurs inattendues si vous oubliez de réinitialiser la gestion des erreurs avec On Error GoTo 0. D’autre part, elle rend parfois plus difficile le débogage, car le flux d’exécution ne s’arrête plus naturellement sur l’erreur. C’est pourquoi beaucoup de développeurs expérimentés préfèrent la version Application.Match combinée à IsError, qui permet de traiter l’absence de correspondance comme un cas normal de logique métier plutôt que comme une exception technique.
Fonction personnalisée SafeMatch avec valeurs de retour conditionnelles
Pour centraliser la gestion des erreurs autour de la fonction Match, une bonne pratique consiste à créer une fonction utilitaire, par exemple SafeMatch. Cette fonction encapsule l’appel à Match, gère les erreurs et renvoie systématiquement un type cohérent (par exemple Long ou Variant), avec une convention claire lorsqu’aucune correspondance n’est trouvée (par exemple 0 ou -1). Vous pouvez ainsi simplifier le code de vos macros, qui n’ont plus à se soucier des détails de la gestion des erreurs à chaque appel.
Une signature typique pourrait être Function SafeMatch(valeur As Variant, plage As Range, Optional matchType As Long = 0, Optional defaultValue As Long = 0) As Long. À l’intérieur, vous appelez Application.Match, testez IsError, et renvoyez soit la position trouvée, soit defaultValue. En centralisant cette logique, vous vous offrez aussi la possibilité d’ajouter ultérieurement des traces de journalisation, des compteurs de performance ou des validations supplémentaires sans modifier chaque macro consommatrice.
Validation préalable avec CountIf avant exécution de match
Une autre stratégie très simple pour sécuriser l’utilisation de Match consiste à valider la présence de la valeur avec Application.CountIf avant de lancer la recherche. Si CountIf renvoie zéro, vous savez immédiatement que Match échouerait, et vous pouvez adapter le comportement de la macro (message informatif, traitement alternatif, etc.). Cette étape préventive améliore l’expérience utilisateur, car vous pouvez fournir des messages clairs plutôt que de laisser remonter une erreur générique.
Par exemple, vous pouvez écrire : If Application.CountIf(plage, critere) = 0 Then MsgBox "Valeur introuvable" Else pos = Application.Match(critere, plage, 0). Sur des plages de taille modérée, le surcoût de ce contrôle est négligeable par rapport à la robustesse gagnée. Sur de très grandes plages, vous pouvez préférer la gestion par IsError pour éviter de parcourir deux fois les mêmes données, ou combiner CountIf avec un Dictionary si vous devez effectuer des centaines de recherches successives.
Optimisation des performances et alternatives à WorksheetFunction.Match
Comparaison benchmarks entre match, find et dictionary
Sur des petits volumes (quelques centaines de lignes), la différence de performance entre Match, Find et Dictionary est souvent imperceptible. Mais dès que vous travaillez sur des dizaines ou centaines de milliers de lignes, le choix de la méthode peut multiplier par 2, 5, voire 10 le temps d’exécution. Des tests réalisés sur des tableaux de 100 000 lignes montrent généralement que Match sur un tableau en mémoire est très rapide pour une recherche unique, que Range.Find reste compétitif pour des recherches ponctuelles avec critères plus complexes, et que le Dictionary prend l’avantage dès que vous multipliez les recherches sur la même base.
En résumé, on peut tracer une analogie avec les moyens de transport : pour un court trajet ponctuel, marcher (Match ou Find) suffit largement ; pour une série de trajets répétés entre les mêmes points, il devient plus rentable d’investir dans un vélo ou une voiture (Dictionary). L’essentiel est d’adapter l’outil au besoin concret : si votre macro ne fait que quelques recherches dans un petit tableau, ne compliquez pas inutilement le code ; mais si vous traitez des rapports mensuels massifs, quelques optimisations bien ciblées peuvent faire gagner plusieurs minutes par exécution.
Utilisation de tableaux variant en mémoire versus range directe
Une optimisation simple et très efficace consiste à travailler autant que possible sur des tableaux Variant en mémoire plutôt que directement sur des objets Range. Chaque accès à la feuille de calcul implique une communication avec le moteur Excel, ce qui est beaucoup plus lent qu’une opération en mémoire. Charger une colonne dans un tableau avec arr = Range("A2:A100000").Value, puis utiliser Application.Match sur ce tableau réduit drastiquement le nombre d’allers-retours avec la feuille.
Vous pouvez pousser cette logique plus loin en effectuant plusieurs opérations de recherche ou de transformation sur les données du tableau, puis en écrivant les résultats en une seule fois dans la feuille. Cette approche « batch » est particulièrement recommandée lorsque vous traitez des données importées ou générées par d’autres systèmes. Elle s’apparente au fait de travailler d’abord sur une copie locale d’un fichier volumineux, puis de ne le renvoyer sur le serveur qu’une fois toutes les modifications terminées.
Méthode Range.Find comme alternative rapide à match
Enfin, la méthode Range.Find constitue une alternative puissante à Match, notamment lorsque vous avez besoin de fonctionnalités supplémentaires comme la recherche partielle (LookAt:=xlPart), la prise en compte de la casse ou la recherche dans les formules plutôt que dans les valeurs. Pour une recherche simple, Set c = plage.Find(what:=valeur, LookIn:=xlValues, LookAt:=xlWhole) vous renverra un objet Range représentant la cellule trouvée, ou Nothing si aucune correspondance n’existe.
Dans certains scénarios, Find peut être plus rapide que Match, en particulier lorsqu’il s’agit de répéter la recherche avec FindNext pour récupérer plusieurs occurrences. Il offre également une interface plus riche pour restreindre ou affiner la recherche (par lignes, par colonnes, sens de recherche, etc.). En pratique, vous pouvez considérer Match comme l’outil de base pour les recherches indexées à une dimension, et Find comme un couteau suisse plus souple lorsque vos besoins sortent du cadre strict de la correspondance exacte ou lorsque vous avez besoin d’explorer l’ensemble des occurrences dans une plage donnée.