Vous pouvez transformer la fonction Match en Vlookup ou Hlookup dans Google Sheets. Cela signifie utiliser Match comme une alternative aux fonctions Vlookup ou Hlookup.
Je suis probablement le premier à partager cette astuce en ligne. Dans mes recherches, je n’ai trouvé personne utilisant cette astuce jusqu’à présent.
Vous avez peut-être déjà utilisé la combinaison Index-Match. Mais ici, je n’utilise pas la fonction Index. Donc ma formule est probablement unique.
Est-ce que cela fonctionne dans Excel aussi?
Oui! Vous pouvez utiliser Match comme une alternative à Vlookup ou Hlookup dans Excel également. Dans les deux cas, la procédure est la même.
En tant qu’utilisateur de Google Sheets, je décris les étapes à l’aide de captures d’écran prises dans Google Sheets, mais elles sont également applicables aux utilisateurs d’Excel.
Normalement, la fonction Match renvoie la position relative d’une clé de recherche dans une ligne horizontale ou une colonne verticale.
Mais en utilisant les fonctions Indirect, pas Index, et Char, vous pouvez transformer la fonction Match en Vlookup ou Hlookup.
En fait, dans Vlookup, je veux dire Math comme Vlookup, l’utilisation de la fonction Char n’est pas nécessaire.
Match peut rechercher une clé et renvoyer non seulement sa position relative, mais il peut également extraire la valeur de la ligne (Vlookup) ou de la colonne (Hlookup) où la clé de recherche est trouvée.
Y a-t-il des avantages à utiliser la fonction Match comme alternative à Vlookup ou Hlookup?
Oui! Il est bénéfique de deux manières.
-
La combinaison Match + Indirect peut rechercher à gauche ou à droite d’une clé de recherche.
-
Utile dans la mise en forme conditionnelle (où Vlookup peut échouer lamentablement).
Voyons comment transformer la fonction Match en Vlookup ou Hlookup dans Google Sheets ainsi que dans Excel.
Après cela, je vous expliquerai les deux points ci-dessus (avantages) de l’utilisation de Match comme alternative à Vlookup.
Comment transformer la fonction Match en Vlookup ou Hlookup dans Google Sheets / Excel
Voici les instructions étape par étape pour utiliser la combinaison Match comme alternative à Vlookup.
Données d’exemple: Production de pommes par pays
Plage de données: B4: C18
Source: Wiki
Ces données sont importées dans Google Sheets à l’aide de la fonction IMPORTHTML pour tester la fonction Match.
Cela ne fonctionnera pas dans Excel. Donc, si vous utilisez Excel, veuillez taper les données ou importez-les dans Google Sheets, puis copiez-les dans Excel.
Voici la formule que j’ai utilisée dans la cellule B4 de mes feuilles de calcul Google pour importer ces données à des fins de test.
=IMPORTHTML(« https://fr.wikipedia.org/wiki/Liste_des_pays_par_production_de_pommes », »table »,1)
Capture d’écran: lien vers l’image
Voici les instructions étape par étape pour transformer la fonction Match en Vlookup. Ensuite, je vous expliquerai les étapes liées à Hlookup. Ce n’est pas compliqué, alors continuez à lire.
Étapes pour transformer la fonction Match en Vlookup dans Google Sheets / Excel
Objectif: Rechercher la clé de recherche « France » dans la colonne B et renvoyer sa « Production de pommes (tonnes) » de la colonne C. Le résultat doit être la valeur 1,819,762.
Voici la formule Vlookup conventionnelle:
= VLOOKUP (E4, B5: C18, 2, 0)
La formule Match transformée:
= INDIRECT (« C »& MATCH (E4, B1: B18, 0))
Comme je l’ai mentionné ci-dessus, cette formule de combinaison Match présente certains avantages par rapport à la Vlookup.
Sans aucun doute, Vlookup est plus puissant et sa capacité à renvoyer un résultat de tableau est inégalée.
Explication de la formule Match et étapes:
Étape 1: Utilisation de la fonction Match seule dans Google Sheets dans l’ensemble de données ci-dessus.
Voici un exemple de formule Match ci-dessous qui renvoie la position relative de la clé de recherche « France » dans la plage B5: B18.
Voir la formule Match ci-dessous qui renvoie la position relative de la clé de recherche « France » dans la plage B5: B18.
Pour transformer la fonction Match en Vlookup, la première étape consiste à trouver le numéro de ligne, pas la position relative de la clé de recherche.
Comment convertir la position relative en numéro de ligne réel?
Dans la formule Match, la plage est B5: B8. Changer simplement la plage en B1: B8 convertira la position relative renvoyée par le Match en numéro de ligne.
= MATCH (E4, B1: B18, 0)
Cette formule renverra 13, qui est le numéro de ligne de la clé de recherche « France » dans la colonne B. C’est l’étape 1.
Étape 2: Transformation de Match en Vlookup.
Nous voulons que la formule recherche « France » et renvoie sa « Production de pommes (tonnes) » dans la colonne C.
En fait, ce que nous voulons, c’est extraire le contenu de la cellule C13. Veuillez vous référer à mes exemples de données ci-dessus.
Il suffit donc de préfixer cette lettre de colonne à la sortie Match, qui est le numéro de ligne 13.
= »C »& MATCH (E4, B5: B18, 0)
Résultat: C13
Étape 3: Indirect cette adresse de cellule.
= INDIRECT (« C »& MATCH (E4, B1: B18, 0))
Voilà!
Étapes pour transformer la fonction Match en Hlookup dans Google Sheets / Excel
Ici, les étapes sont un peu différentes car nous utilisons Match dans une ligne.
La formule Match ci-dessous renverra la position relative de la clé de recherche « France » dans la ligne.
Ici encore, nous pouvons modifier la plage de données dans la formule Match pour renvoyer le numéro de colonne de la clé de recherche.
Ici, la formule Match renvoie 10, qui est la position relative. J’ai déjà partagé avec vous comment convertir la position relative en numéro de ligne ci-dessus.
Ici, nous pouvons convertir la position relative en numéro de colonne car c’est un match horizontal.
Voici cette formule. Changez simplement la plage de recherche de B4: P4 à A4: P4.
=mach(« France »,A4:P4,0)
Cette formule renvoie 11, qui est le numéro de colonne. Vous pouvez convertir un numéro de colonne en lettre de colonne dans Google Sheets en utilisant la fonction Char comme ci-dessous.
Utilisez simplement =Char(64+1) pour renvoyer la lettre de colonne A. Si vous remplacez 1 par 26, cela renverra Z.
Cela signifie que nous pouvons ajouter le numéro de colonne pour obtenir la lettre de colonne. Donc, la formule serait.
=Char(64+mach(« France »,A4:P4,0))
Résultat: K
Remarque: Cette formule a une limitation. Elle ne fonctionnera que jusqu’à la colonne Z. Si votre plage de données est plus grande, vous pouvez utiliser cette formule alternative. Cela ne fonctionne que dans Google Sheets.
=regexreplace(address(1,mach(« France »,A4:P4,0)), »[^A-Z] », » »)
Dans cette formule, la formule Match est entourée d’autres éléments qui remplissent le rôle de la fonction Char.
Vous souhaitez extraire la valeur de la ligne 5. Cela signifie que vous voulez la valeur de la cellule K5. Donc, joignez 5 à la formule ci-dessus.
=char(64+mach(« France »,A4:P4,0))&5
Ensuite, Indirect cela.
=indirect(char(64+mach(« France »,A4:P4,0))&5)
Vous avez appris deux astuces intéressantes pour transformer la fonction Match en Vlookup et Hlookup dans Google Sheets / Excel. Avez-vous déjà vu cela être utilisé auparavant?
Avantages de transformer la fonction Match en Vlookup
Ici, je ne vais pas aborder Hlookup car Vlookup est le plus couramment utilisé.
J’ai déjà mentionné les avantages de l’utilisation de la fonction Match pour la recherche verticale/horizontale ci-dessus. Il y a deux avantages visibles.
Recherche à gauche de la clé de recherche en utilisant Match
Dans ce cas, je veux trouver le rang de « Étudiant 3 ». La clé de recherche « Étudiant 3 » est dans la colonne E et le rang est dans la colonne D, qui est évidemment à gauche de la colonne de recherche.
Bien sûr, vous pouvez utiliser Vlookup lui-même ou Indirect-Match dans ce cas. Je ne le nie pas. Mais cette utilisation de Match est assez cool.
Mise en forme conditionnelle
Étant donné que nous générons l’adresse de la cellule, puis l’indirect, cela sera utile dans la mise en forme conditionnelle.
Consultez ce tutoriel avancé qui éclaire cette direction – Mettre en évidence une valeur croisée dans Google Sheets dans une recherche bidirectionnelle.
J’espère que vous avez appris comment transformer la fonction Match en Vlookup/Hlookup dans Google Sheets et Excel.
Apprenez d’abord cette astuce intéressante vous-même, puis partagez cette nouvelle information avec vos amis. Amusez-vous bien!
