Google Sheets permet de créer un lien hypertexte vers une cellule de sortie d’Index-Match. Ainsi, la valeur retournée (valeur Index-Match) sera un lien cliquable dans Google Sheets.
Veuillez noter une chose. L’argument de ligne/colonne dans Index ne doit pas être égal à 0 car cela provoque l’apparition de plusieurs valeurs en sortie.
En résumé, selon mon tutoriel, vous ne pouvez créer un lien hypertexte que vers une cellule unique en sortie de l’Index-Match dans Google Sheets.
Introduction à l’hyperlien Index et Match dans Google Sheets
Index-Match est une alternative populaire utilisée par les utilisateurs d’Excel à la place de la recherche V, mais semble maintenant remplacée par une nouvelle fonction appelée XLOOKUP.
Pour autant que je sache, il n’y a pas encore de fonction XLOOKUP dans Google Sheets. Je suppose que Google inclura bientôt cette fonction (je peux me tromper) !
L’image ci-dessous explique pourquoi je le pense.
Dans Google Sheets, pour la recherche inversée, il n’est pas nécessaire d’utiliser XLOOKUP puisque Vlookup lui-même est capable de le faire.
Vous aimerez peut-être : Exemples de recherche inversée dans Google Sheets [Options de formule].
Même s’il n’est pas nécessaire d’utiliser Index-Match, de nombreux utilisateurs de Google Sheets, qui sont familiers avec Excel, préfèrent utiliser Index-Match.
Donc dans cet article, apprenons comment créer un lien hypertexte vers le résultat de l’Index-Match dans Google Sheets. Cela a du sens, n’est-ce pas ?
J’utilise un tableau très simple pour que vous puissiez facilement le créer pour tester ma formule. Il contient les noms de quelques pays et leurs capitales nationales.
Créez le tableau ci-dessus dans la plage A1:B5 et renommez la feuille en ‘data’. Ensuite, suivez les étapes ci-dessous pour créer un lien hypertexte vers la cellule de sortie Index-Match dans Google Sheets.
Formule Index-Match (pour créer un lien hypertexte)
Je sais que vous utilisez déjà Index-Match. Cependant, j’explique toujours son utilisation pour rendre ce tutoriel accessible aux débutants.
Exemple :
Je souhaite trouver « Allemagne » dans le tableau ci-dessus dans la colonne B, puis décaler le même nombre de lignes dans la colonne A et obtenir la valeur « Berlin ».
Pour trouver le pays (chaîne de caractères) « Allemagne » dans la colonne B, nous pouvons utiliser la fonction MATCH en suivant cette syntaxe – MATCH(clé_recherche, plage, type_recherche).
Saisissez la clé de recherche « Allemagne » dans la cellule D1 et utilisez cette formule Match dans la cellule E1. La formule renverra 3.
=match(D1,B1:B,0)
Cela signifie que nous voulons décaler 3 lignes dans une autre colonne, ici la colonne A, et obtenir la valeur « Berlin ». Comment ?
Nous pouvons utiliser la fonction INDEX selon la syntaxe – INDEX(référence, ligne, colonne).
=INDEX(A1:A, 3, 1)
Remplacez simplement le # 3 par la formule Match elle-même. Voici la formule Index-Match pour obtenir la « Capitale » du « Pays » Allemagne.
=index(Data!A1:A,match(Data!D1,Data!B1:B,0),1)
Note :
Le # 1 dans la formule représente le numéro de colonne à décaler. La référence Index est A1:A, ce qui signifie la colonne 1.
Si la référence est A1:E et que vous mettez 5 comme numéro de colonne, alors la formule décalera 3 lignes dans la colonne E.
Voyons maintenant comment créer un lien hypertexte vers ce résultat de l’Index-Match dans Google Sheets, ou plutôt comment rendre le label « Berlin » dans la cellule E1 cliquable.
Techniques de création de liens hypertexte
Il y a en réalité 3 étapes pour créer un lien hypertexte vers un résultat d’Index-Match dans Google Sheets. Nous avons déjà terminé l’étape 1 ! Voici ces 3 étapes.
- Formule Index-Match (nous lierons la sortie de cette formule) : ✓
- URL de la cellule A1 de la feuille ‘data’. Mon tableau d’exemple se trouve dans la feuille nommée ‘data’ : ✘
- ID de cellule de la sortie de la formule Index-Match : ✘
Seules les étapes 2 et 3 sont en attente.
Étape 1
=index(Data!A1:A,match(Data!D1,Data!B1:B,0),1)
Étape 2
Pour obtenir l’URL, faites un clic droit sur la cellule A1 de la feuille ‘data’ et cliquez sur « Obtenir le lien vers cette cellule ».
Faites un clic droit et collez le lien copié dans la cellule E2. Après avoir collé, modifiez le lien pour supprimer la référence de cellule A1 à la fin du lien.
https://docs.google.com/spreadsheets/d/****#gid=92***9&range=
L’URL ci-dessus est un exemple. Vous devez utiliser le lien copié depuis votre feuille comme indiqué ci-dessus. Le signe = doit être le dernier caractère après avoir modifié le lien comme indiqué ci-dessus.
Étape 3
Dans la cellule E3, utilisez la formule d’Index-Match de l’étape 1 dans la fonction CELL pour obtenir l’ID de cellule de la sortie de l’Index-Match.
Veuillez suivre la formule générique ci-dessous pour obtenir l’ID de cellule de la valeur résultante de l’Index-Match.
=cell(« address »,index-match_formula)
Formule :
=cell(« address »,index(Data!A1:A,match(Data!D1,Data!B1:B,0),1))
La formule ci-dessus renverra l’adresse de cellule $A$3. Nous devons la nettoyer en supprimant le signe $ de la formule.
De plus, si votre tableau est dans la feuille ‘data’ et que la formule est dans une autre feuille de ce fichier, alors le résultat serait data!$A$3.
Donc, que le nom de la feuille soit présent ou non avec l’adresse de cellule, prévoyons de supprimer la chaîne de caractères data! du résultat.
Modifiez simplement la formule dans la cellule E3 pour inclure plusieurs fonctions SUBSTITUTE comme ci-dessous.
=substitute( substitute( cell(« address »,index(A1:A,match(D1,B1:B,0),1)), »data! », » »), « $ », » »)
Le premier Substitute supprime le signe $ tandis que le deuxième supprime la sous-chaîne data! si elle est présente. Le résultat de la formule ci-dessus sera l’ID de cellule ‘A3’.
Utilisation des formules des étapes 1, 2 et 3 dans Hyperlink pour créer un lien hypertexte vers le résultat de l’Index-Match
La fonction Hyperlink prend deux arguments. Ce sont ‘URL’ et ‘link_label’.
Syntaxe : HYPERLINK(URL, [label_lien])
Nous pouvons utiliser la formule Index-Match (étape 1) comme ‘link_label’ dans Hyperlink. Et ‘URL’ ?
L’URL doit être la sortie combinée des formules des étapes 2 et 3. La formule générique suivante vous aidera à comprendre cela.
=hyperlink( « step_2″& step_3, step_1 )
Formule qui crée un lien hypertexte vers le résultat de l’Index-Match (basée sur la formule générique ci-dessus) :
=hyperlink( « https://docs.google.com/spreadsheets/d/****#gid=92***9&range= »& substitute( substitute( cell(« address »,index(A1:A,match(D1,B1:B,0),1)), »Data! », » »), « $ », » »), index(data!A1:A,match(data!D1,data!B1:B,0),1) )
Supprimez maintenant les formules dans les cellules E1, E2 et E3. Insérez notre formule finale ci-dessus dans la cellule E1 et voilà !
Exemple de lien hypertexte 17520
Ressources :
- Comment nommer une URL dans Google Sheets en utilisant la fonction HYPERLINK
- Recherche de valeur et lien hypertexte vers la cellule trouvée dans Google Sheets
- Lien hypertexte UNIQUE en double dans Google Sheets – Mêmes libellés, URLs différentes
- Valeurs maximales et minimales de liens hypertexte dans une colonne ou une ligne dans Google Sheets
- Recherche V inversée sensible à la casse en utilisant Index-Match dans Google Sheets
- Conditions multiples dans Index-Match dans Google Sheets
