Vous pouvez utiliser la fonction Excel MATCH pour rechercher la position relative d'une valeur spécifique dans une plage de cellules ou un tableau.
La fonction MATCH est similaire à la fonction RECHERCHEV car elles sont toutes deux classées dans les fonctions de recherche/référence Excel. RECHERCHEV recherche une valeur spécifique dans une colonne et renvoie une valeur dans la même ligne tandis que la fonction MATCH recherche une certaine valeur dans une plage et renvoie la position de cette valeur.
La fonction Excel MATCH recherche une valeur spécifiée dans une plage de cellules ou un tableau et renvoie la position relative de la première apparition de cette valeur dans la plage. La fonction MATCH peut également être utilisée pour rechercher une certaine valeur et renvoyer sa valeur correspondante à l'aide de la fonction INDEX (tout comme Vlookup). Voyons comment utiliser la fonction Excel MATCH pour trouver la position d'une valeur de recherche dans une plage de cellules.
Fonction MATCH Excel
La fonction MATCH est une fonction intégrée dans Excel et elle est principalement utilisée pour localiser la position relative d'une valeur de recherche dans une colonne ou une ligne.
Syntaxe de la fonction MATCH :
= MATCH(lookup_value, lookup_array, [match_type})Où:
valeur_recherche - La valeur que vous souhaitez rechercher dans une plage de cellules spécifiée ou dans un tableau. Il peut s'agir d'une valeur numérique, d'une valeur textuelle, d'une valeur logique ou d'une référence de cellule qui a une valeur.
tableau_recherche – Les tableaux de cellules dans lesquels vous recherchez une valeur. Il doit s'agir d'une seule colonne ou d'une seule ligne.
Type de match – Il s'agit d'un paramètre facultatif qui peut être défini sur 0,1 ou -1 et la valeur par défaut est 1.
- 0 cherche une correspondance exacte, quand elle n'est pas trouvée, renvoie une erreur.
- -1 recherche la plus petite valeur supérieure ou égale à lookup_value lorsque le tableau de recherche dans l'ordre croissant.
- 1 recherche la plus grande valeur inférieure ou égale à la valeur look_up lorsque le tableau de recherche par ordre décroissant.
Trouver la position d'une correspondance exacte
Supposons que nous ayons l'ensemble de données suivant où nous voulons trouver la position d'une certaine valeur.
Dans ce tableau, nous voulons trouver la position d'un nom de ville (Memphis) dans la colonne (A2:A23), nous utilisons donc cette formule :
= MATCH("memphis",A2:A23,0)Le troisième argument est défini sur « 0 » car nous voulons trouver une correspondance exacte du nom de la ville. Comme vous pouvez le constater, le nom de la ville « memphis » dans la formule est en minuscule tandis que dans le tableau, la première lettre du nom de la ville est en majuscule (Memphis). Pourtant, la formule est capable de trouver la position de la valeur spécifiée dans la plage donnée. C'est parce que la fonction MATCH est insensible à la casse.
Noter: Si la valeur lookup_value n'est pas trouvée dans la plage de recherche ou si vous spécifiez la mauvaise plage de recherche, la fonction renverra l'erreur #N/A.
Vous pouvez utiliser une référence de cellule dans le premier argument de la fonction au lieu d'une valeur directe. La formule ci-dessous trouve la position de la valeur dans la cellule F2 et renvoie le résultat dans la cellule F3.
Trouver la position d'une correspondance approximative
Il existe deux manières de rechercher une correspondance approximative ou exacte de la valeur de recherche et de renvoyer sa position.
- Une façon consiste à trouver la plus petite valeur supérieure ou égale (prochaine plus grande correspondance) à la valeur spécifiée. Cela peut être réalisé en définissant le dernier argument (match_type) de la fonction comme '-1'
- Une autre façon est la plus grande valeur qui est inférieure ou égale (prochaine plus petite correspondance) à la valeur donnée. Cela peut être réalisé en définissant le match_type de la fonction sur '1'
Plus petite correspondance suivante
Si la fonction ne peut pas trouver une correspondance exacte avec la valeur spécifiée lorsque le type de correspondance est défini sur « 1 », elle localise la plus grande valeur légèrement inférieure à la valeur spécifiée (ce qui signifie la prochaine plus petite valeur) et renvoie sa position . Pour que cela fonctionne, vous devez trier le tableau dans l'ordre croissant, sinon cela entraînera une erreur.
Dans l'exemple, nous utilisons la formule ci-dessous pour trouver la prochaine plus petite correspondance :
= CORRESPONDANCE(F2,D2:D23,1)Lorsque cette formule n'a pas pu trouver la correspondance exacte pour la valeur dans la cellule F2, elle pointe vers la position (16) de la prochaine plus petite valeur, c'est-à-dire 98.
Suivant Plus grand match
Lorsque le type de correspondance est défini sur « -1 » et que la fonction MATCH ne peut pas trouver de correspondance exacte, elle trouve la plus petite valeur supérieure à la valeur spécifiée (ce qui signifie la valeur la plus grande suivante) et renvoie sa position. Le tableau de recherche doit être trié par ordre décroissant pour cette méthode, sinon il renverra une erreur.
Par exemple, entrez la formule suivante pour trouver la prochaine plus grande correspondance avec la valeur de recherche :
= CORRESPONDANCE(F2,D2:D23,-1)Cette fonction MATCH recherche la valeur dans F2 (55) dans la plage de recherche D2:D23, et lorsqu'elle ne peut pas trouver la correspondance exacte, elle renvoie la position (16) de la plus grande valeur suivante, c'est-à-dire 58.
Correspondance avec caractères génériques
Les caractères génériques ne peuvent être utilisés dans la fonction MATCH que lorsque match_type est défini sur « 0 » et que la valeur de recherche est une chaîne de texte. Il existe des caractères génériques que vous pouvez utiliser dans la fonction MATCH : un astérisque (*) et un point d'interrogation (?).
- Point d'interrogation (?) est utilisé pour faire correspondre n'importe quel caractère ou lettre avec la chaîne de texte.
- Astérisque (*) est utilisé pour faire correspondre un nombre quelconque de caractères avec la chaîne.
Par exemple, nous avons utilisé deux caractères génériques « ? » dans la valeur lookup_value (Lo??n) de la fonction MATCH pour trouver une valeur qui correspond à la chaîne de texte avec deux caractères quelconques (aux emplacements des caractères génériques). Et la fonction renvoie la position relative de la valeur correspondante dans la cellule E5.
=MATCH("Lo??n",A2:A22,0)
Vous pouvez utiliser le caractère générique (*) de la même manière que (?), mais un astérisque est utilisé pour correspondre à n'importe quel nombre de caractères tandis qu'un point d'interrogation est utilisé pour correspondre à n'importe quel caractère.
Par exemple, si vous utilisez « sp* », la fonction peut correspondre au haut-parleur, à la vitesse ou au spielberg, etc. Mais si la fonction trouve des valeurs multiples/en double correspondant à la valeur de recherche, elle ne renverra que la position de la première valeur.
Dans l'exemple, nous avons entré « Kil*o » dans l'argument lookup_value. Ainsi, la fonction MATCH() recherche un texte qui contient « Kil » au début, « o » à la fin et un nombre quelconque de caractères entre les deux. « Kil*o » correspond au Kilimandjaro dans le tableau et, par conséquent, la fonction renvoie la position relative du Kilimandjaro, qui est 16.
INDEX et MATCH
Les fonctions MATCH sont rarement utilisées seules. Ils se sont souvent associés à d'autres fonctions pour créer des formules puissantes. Lorsque la fonction MATCH est combinée avec la fonction INDEX, elle peut effectuer des recherches avancées. Beaucoup de gens préfèrent toujours utiliser RECHERCHEV pour rechercher une valeur, car c'est plus simple mais INDEX MATCH est plus flexible et plus rapide que RECHERCHEV.
RECHERCHEV ne peut rechercher une valeur que verticalement, c'est-à-dire des colonnes, tandis que le combo INDEX MATCH peut effectuer des recherches à la fois verticales et horizontales.
Fonction INDEX utilisée pour récupérer une valeur à un emplacement spécifique dans une table ou une plage. La fonction MATCH renvoie la position relative d'une valeur dans une colonne ou une ligne. Lorsqu'il est combiné, le MATCH trouve le numéro de ligne ou de colonne (emplacement) d'une valeur spécifique, et la fonction INDEX récupère une valeur basée sur ce numéro de ligne et de colonne.
Syntaxe de la fonction INDEX :
=INDEX(tableau,num_ligne,[num_col],)Quoi qu'il en soit, voyons comment INDEX MATCH fonctionne avec un exemple.
Dans l'exemple ci-dessous, nous souhaitons récupérer le score « Quiz2 » pour l'élève « Anne ». Pour ce faire, nous utiliserons la formule ci-dessous :
=INDICE(B2:F20,MATCH(H2,A2:A20,0),3)INDEX a besoin d'un numéro de ligne et de colonne pour récupérer une valeur. Dans la formule ci-dessus, la fonction MATCH imbriquée trouve le numéro de ligne (position) de la valeur « Anne » (H2). Ensuite, nous fournissons ce numéro de ligne à la fonction INDEX avec une plage B2:F20 et un numéro de colonne (3), que nous spécifions. Et la fonction INDEX renvoie le score '91'.
Recherche bidirectionnelle avec INDEX et MATCH
Vous pouvez également utiliser les fonctions INDEX et MATCH pour rechercher une valeur dans une plage à deux dimensions (recherche bidirectionnelle). Dans l'exemple ci-dessus, nous avons utilisé la fonction MATCH pour localiser le numéro de ligne d'une valeur, mais nous avons entré le numéro de colonne manuellement. Mais nous pouvons trouver à la fois la ligne et la colonne en imbriquant deux fonctions MATCH, une dans l'argument row_num et une autre dans l'argument column_num de la fonction INDEX.
Utilisez cette formule pour une recherche bidirectionnelle avec INDEX et MATCH :
=INDICE(A1:F20,ASSOCIATION(H2,A2:A20,0),ASSOCIATION(H3,A1:F1,0))Comme nous le savons, la fonction MATCH peut rechercher une valeur à la fois horizontalement et verticalement. Dans cette formule, la deuxième fonction MATCH dans l'argument num_colonne trouve la position de Quiz2 (4) et la fournit à la fonction INDEX. Et l'INDEX récupère le score.
Maintenant, vous savez comment utiliser la fonction Match dans Excel.