Comment utiliser la fonction MATCH dans Google Sheets ?

Dans Google Sheets, la fonction MATCH vous donne la fonction position relative d'un élément dans une plage de cellules. En fait, si vous souhaitez connaître la position d'une valeur spécifique dans une plage ou un tableau, MATCH vous dira où elle se trouve. MATCH est pratique mais assez basique, mais lorsque vous le combinez avec INDEX, il devient très puissant. La fonction Fonction INDEX MATCH est une alternative brillante et plus dynamique à la fonction VLOOKUP. Elle résout de nombreux problèmes associés à VLOOKUP.

MATCH

Tout d'abord, examinons la fonction MATCH et son fonctionnement dans Google Sheets.

Rappelez-vous que MATCH renvoie un position mais pas une valeur, donc si vous voulez renvoyer une valeur elle-même, utilisez INDEX, HLOOKUP, ou VLOOKUP.

Dans la capture d'écran ci-dessous, vous pouvez voir que la position de Evan est 5 dans l'intervalle des cellules A1 à A6.

Que se passe-t-il si vous placez les cellules comme indiqué dans la capture d'écran ci-dessous (B4 à B9) ?

La position relative de Evan reste encore 5.

C'est exactement ce que la fonction MATCH est conçue pour faire - renvoyer la position relative d'un élément (‘Evan’) dans une plage de cellules (A1:A6 ou B4:B9).

Syntaxe

• clé_de_recherche - est le article que la fonction MATCH recherche dans le gamme de cellules. Il peut s'agir d'un texte pur (‘Evan’), ou une référence à une cellule (comme A7), ou même une fonction qui renvoie une chaîne de caractères ou un nombre (comme LEFT(“Mike Johnson”,8) ou DATE(2017,1,1))
• gamme - est le groupe de cellules dans lequel la fonction MATCH recherche l'élément (clé_de_recherche). Il doit s'agir d'un tableau unidimensionnel, c'est-à-dire d'une plage comportant une seule colonne ou une seule ligne.
• type_de_recherche - est une entrée facultative qui indique comment la fonction MATCH doit rechercher la valeur clé_de_recherche dans le gamme. Elle prend trois valeurs différentes :
  • 1 est la valeur par défaut (c'est-à-dire lorsqu'aucune donnée n'est fournie à l'encontre de type_de_recherche). Avec cette option, Google Sheets suppose que la plage de cellules est triée par ordre croissant et renvoie donc la plus grande valeur inférieure ou égale à clé_de_recherche.
  • 0 précise à Google Sheets qu'il doit trouver une correspondance exacte. Il s'agit de l'option idéale si le fichier gamme de cellules n'est triée dans aucun ordre.
  • -1, Comme on peut s'en douter, c'est exactement le contraire de 1. Cette option suppose que la plage de cellules est triée par ordre décroissant et renvoie la plus petite valeur supérieure ou égale à clé_de_recherche.

Comment utiliser MATCH dans Google Sheets

Regardez la capture d'écran ci-dessous. Dans la colonne A, j'ai un ensemble de données classées par ordre croissant (par exemple, de la plus petite à la plus grande). J'ai essayé plusieurs variantes de la fonction MATCH sur ces données.

Dans le premier exemple, à la ligne 2, j'ai demandé à la fonction MATCH de rechercher l'ID #1400 en tapant =Match(1400,A2:A15,1).

La deuxième formule, à la ligne 3, fait la même chose que la première, et les deux m'indiquent que l'ID #1400 se trouve à la 7e ligne.

La différence est que dans le premier exemple, j'ai utilisé le type de recherche 1, qui est utilisé lorsque les données sont classées par ordre croissant (ce qui est le cas).

La deuxième fois, j'ai utilisé le type de recherche 0, qui est utilisé lorsque les données ne sont pas triées ou lorsque vous souhaitez uniquement une correspondance exacte. Comme les données étaient triées et qu'il y avait une correspondance exacte pour 1400, les deux fonctions m'ont donné le même résultat.

Dans le troisième exemple, à la ligne 4, je demande à la fonction MATCH de me trouver l'ID #1300 en tapant =Match(1300,A2:A15,0). J'utilise le type de recherche 0 pour spécifier que je veux une correspondance exacte. Comme il n'y a pas d'ID #1300, j'obtiens une erreur.

Dans le quatrième exemple de la ligne 5, cependant, je pose la même question mais j'utilise le type de recherche 1. Ici, la fonction me donne un résultat, indiquant que la réponse se trouve à la ligne 4.

Note concernant MATCH

Comme vous pouvez le constater, la ligne 4 correspond à l'ID #1125.

La ligne suivante, ID #1313, est beaucoup plus proche de l'ID #1300 que je cherchais. Toutefois, lors de la recherche de données par ordre croissant, la fonction renvoie la valeur la plus proche qui n'est pas supérieure à la clé de recherche. C'est pourquoi elle renvoie le résultat de la ligne 4, et non de la ligne 5.

Dans la dernière paire d'exemples, aux lignes 6 et 7, au lieu de taper l'expression clé_de_recherche directement, j'indique une cellule de référence qui contient la clé_de_recherche. Pour ce faire, j'utilise la fonction =Match(D2,A2:A15,1).

Dans l'exemple suivant, je montrerai les données triées par ordre décroissant.

Les exemples ici sont les mêmes que dans la première image, sauf que j'utilise maintenant un clé_de_recherche de -1 au lieu de 1 pour refléter le fait que les données sont triées par ordre décroissant.

Remarquez l'exemple de la recherche de l'ID #1300 - cette fois, la fonction donne un résultat de la ligne 10, qui correspond à l'ID #1313, beaucoup plus proche de 1300 que lorsque la même chose a été faite avec les données triées par ordre croissant.

Utilisation de MATCH avec du texte

Les exemples présentés concernaient des valeurs numériques. Que diriez-vous d'utiliser la fonction MATCH avec des valeurs textuelles ?

Comme il n'est pas possible de définir exactement les valeurs ‘inférieur à’ et ‘supérieur à’ pour les formulaires de texte, j'utilise généralement l'option type_de_recherche 0, qui indique à Google Sheets de rechercher une correspondance exacte. Voici quelques exemples :

Remarquez que le clé_de_recherche doit être entre guillemets lorsqu'il s'agit d'une chaîne de texte, comme dans le premier exemple : =Match(“Eric”,A2:A14,0). Une autre chose à retenir est que lors de l'utilisation de clé_de_recherche 0 pour rechercher une correspondance exacte. S'il n'y a pas de correspondance exacte, la fonction donnera une erreur (comme dans le premier exemple ci-dessus).

J'ai utilisé la fonction MATCH dans une présentation verticale, car c'est ce qui est le plus courant. Mais il y a certainement des cas où vous voudrez l'utiliser pour un ensemble de données horizontales. C'est ce que je fais dans l'exemple ci-dessous.

Ici, la fonction MATCH vous indique le numéro de la colonne qui contient l'élément clé_de_recherche.

Comment utiliser la combinaison de fonctions INDEX MATCH ?

L'utilisation la plus puissante de la fonction MATCH dans Google Sheets est peut-être celle qui consiste à l'utiliser en combinaison avec la fonction INDEX, afin de rechercher des valeurs. Mais il existe déjà une fonction VLOOKUP dans Google Sheets pour cela, n'est-ce pas ?

C'est vrai, mais la combinaison de MATCH et d'INDEX permet de résoudre plusieurs problèmes liés à l'utilisation de VLOOKUP.

Examinez l'exemple VLOOKUP ci-dessous :

La fonction VLOOKUP de D2 recherche 161 dans la colonne ‘Emp ID #’ (car c'est la plus à gauche dans la plage A2:B11), et à partir de la ligne où elle trouve la valeur 161, elle récupère la valeur située dans la deuxième colonne (c'est-à-dire la colonne B), tout en supposant que les données ne sont pas triées. Jusqu'ici, tout va bien. Mais l'utilisation de VLOOKUP dans Google Sheets pose deux problèmes majeurs.

Problème 1 : Référencement statique de cellules

Que se passe-t-il si vous insérez une nouvelle colonne entre la première et la deuxième colonne ? Essayons-le.

Vous remarquerez que la valeur retournée n'est pas Ethan plus. En effet, VLOOKUP est une fonction semi-statique.

Google Sheets a mis à jour le deuxième paramètre pour refléter la nouvelle plage, mais n'a pas modifié en conséquence l'indice de la colonne (troisième paramètre) lorsqu'une nouvelle colonne a été ajoutée avant le paramètre Vendeur colonne.

Problème 2 : La colonne de consultation est toujours la plus à gauche

Vous pouvez vous trouver dans une situation où vous devez rechercher des valeurs dans une colonne (Emp ID #) qui n'est pas la plus à gauche, comme indiqué ci-dessous.

Dans ce cas, il suffirait de déplacer la colonne Emp ID # pour qu'elle soit la plus à gauche. Mais ce n'est pas l'approche idéale - il peut y avoir des spécifications de mise en page ou de présentation des données qui ne vous permettent pas de réorganiser les colonnes. Dans ce cas, que pouvez-vous faire ? La combinaison des fonctions MATCH et INDEX vient à la rescousse. Voici la syntaxe de la combinaison, suivie de quelques exemples.

INDEX(reference, MATCH(search_key, range, search_type))

La clé de cette combinaison est que les plages sélectionnées pour les fonctions INDEX et MATCH, respectivement, doivent être une seule colonne. Vous utilisez essentiellement une fonction VLOOKUP, mais en spécifiant la colonne dans laquelle rechercher et la colonne à partir de laquelle renvoyer la valeur, dans des plages distinctes au sein des fonctions MATCH et INDEX. Comme vous le verrez, cette méthode vous permet d'éviter les erreurs qui peuvent survenir lors de l'utilisation de la fonction VLOOKUP.

Dans l'exemple ci-dessus, j'ai utilisé la fonction MATCH pour me dire quelle ligne contient l'ID Emp #161 dans la colonne C. La fonction renvoie un nombre qui indique à la fonction INDEX dans quelle ligne de la colonne A chercher le vendeur. Le résultat est que les deux fonctions se combinent pour m'indiquer le nom correct (Ethan) pour l'ID employé #161. La fonction que j'ai utilisée pour ce faire est la suivante =INDEX(A2:A11,MATCH(161,C2:C11,0)).

Comme vous pouvez le constater, contrairement à VLOOKUP, la combinaison fonctionne même si la colonne de consultation n'est pas la plus à gauche (voir les trois premiers exemples de la capture d'écran ci-dessus). Il n'est pas surprenant qu'elle fonctionne également comme VLOOKUP lorsque la colonne de consultation est la plus à gauche.

Voyons également si les fonctions se maintiennent lorsque l'on introduit une nouvelle colonne entre les deux.

Heureusement, elles fonctionnent toujours. Dès que j'ai introduit une nouvelle colonne (État), Google Sheets a automatiquement mis à jour les références pour tenir compte de ce changement.

Comme vous l'avez vu ci-dessus, la combinaison des fonctions MATCH et INDEX est beaucoup plus flexible et polyvalente que la fonction VLOOKUP, déjà populaire et puissante. Mais la combinaison de ces fonctions peut être encore plus puissante lorsque vous utilisez deux fonctions MATCH au lieu d'une seule.

Comment utiliser la fonction INDEX avec 2 fonctions MATCH ?

Vous avez vu à quel point la combinaison INDEX et MATCH peut être utile. Mais qu'en est-il si vous disposez d'un tableau bidimensionnel dont vous souhaitez obtenir une valeur ? Dans ce cas, vous pouvez utiliser la fonction MATCH deux fois dans la fonction INDEX, comme le montre l'exemple ci-dessous.

Dans cet exemple, vous pouvez voir des données sur la température moyenne de chaque mois, pour les années 2015-2020. Si vous souhaitez extraire de ce tableau la température moyenne d'un mois spécifique d'une année spécifique, vous pouvez utiliser la combinaison INDEX et MATCH pour y parvenir. La fonction utilisée ici est la suivante :
=INDEX(B2:M7, Match(B11, A2:A7, false), Match(B10, B1:M1, false))

Comme vous pouvez le voir, j'utilise la fonction MATCH pour obtenir l'emplacement de la ligne correspondant à l'année recherchée (dans ce cas, il s'agit de 2018, qui se trouve dans la 4e ligne de données) ainsi que la colonne correspondant au mois recherché (septembre, dans la 9e colonne de données). La fonction INDEX prend ensuite ces coordonnées et renvoie la température moyenne pour septembre 2018.

En utilisant les fonctions INDEX et MATCH, vous pouvez non seulement obtenir une fonction de recherche plus polyvalente que VLOOKUP, mais vous pouvez également effectuer des recherches dans des tableaux bidimensionnels. Pour en savoir plus sur la fonction INDEX et son utilisation, consultez l'article de blog suivant : Comment utiliser la fonction INDEX dans Google Sheets.