Vous pouvez coder l’une des meilleures formules pour rechercher une valeur de cellule et renvoyer son en-tête en utilisant Hlookup. Utilisez la formule Hlookup pour rechercher dans l’ensemble du tableau et trouver l’en-tête, c’est-à-dire récupérer le nom de la colonne ou l’étiquette de champ, dans Google Sheets.
Une des meilleures formules ?
Oui ! Parce que j’ai un tutoriel similaire sur cette page. Je vais sûrement mettre un lien vers ces pages plus tard dans cet article.
Je sais que Hlookup est utilisé pour rechercher dans la première ligne. Mais vous pouvez utiliser Hlookup pour rechercher dans n’importe quelle ligne avec une solution de contournement. Je vais partager cette astuce géniale ici dans cet article.
Cette GIF vous donne une idée assez claire de la recherche dans un tableau entier et de la recherche de l’en-tête dans Google Sheets, n’est-ce pas ?
Si vous êtes un lecteur régulier ou un visiteur régulier, vous avez peut-être déjà vu des tutoriels similaires sur cette page à des fins similaires ou différentes.
Avant de continuer, laissez-moi vous présenter les tutoriels similaires précédents.
-
Recherche à travers les colonnes et renvoie l’en-tête dans Google Sheets – Dans ce tutoriel précédent, j’ai détaillé la solution ci-dessus mais en utilisant une approche très différente. J’ai utilisé la fonction Query comme formule clé. Pas si difficile à coder, mais bien sûr, un peu complexe.
-
Recherche et récupération de l’en-tête de colonne dans Google Sheets – C’est une solution légèrement différente. Vous pouvez l’utiliser pour rechercher dans la première colonne, puis renvoyer l’en-tête des colonnes requises.
Cette fois, j’utilise Hlookup, qui est plus élégant et très simple à coder.
Formule pour rechercher dans l’ensemble du tableau et trouver l’en-tête dans Sheets
Comme vous le savez peut-être, Hlookup ne peut rechercher que dans la première ligne d’un tableau (range/array) et renvoyer la valeur de la colonne trouvée. Mais l’inverse est également possible ! C’est-à-dire que vous pouvez rechercher dans un tableau entier (array/range) et renvoyer l’en-tête.
Codons la formule pour les mêmes données d’exemple ci-dessus. Le tableau se trouve dans la plage B2:E6. Mais nous pouvons utiliser une plage de tableau infinie dans la formule pour inclure les lignes futures. Je vais donc utiliser la plage B2:E dans la formule.
Hlookup première ligne vs Hlookup n’importe quelle ligne
Le comportement par défaut d’Hlookup est de rechercher dans la première ligne comme indiqué ci-dessous.
Vous pouvez rechercher les mots-clés « North », « South », « East » ou « West » dans Hlookup car ils se trouvent dans la première ligne du tableau ou de la plage.
Supposons que vous utilisez « South » comme mot-clé. Vous pouvez alors renvoyer n’importe quelle valeur de la plage C3:C.
Exemple :
=hlookup("South",B2:E,2,0)
Cela renverrait la valeur « Adam » car j’ai spécifié 2 comme indice de ligne (deuxième ligne dans la plage).
Je veux faire l’inverse de cela. C’est-à-dire rechercher le mot-clé « Adam » et renvoyer l’en-tête « South ».
Ci-dessous, vous pouvez trouver la formule Hlookup pour rechercher un tableau entier et trouver l’en-tête dans Google Sheets.
Formule Hlookup pour rechercher un tableau entier et trouver la valeur de l’en-tête de la ligne
Formule :
=hlookup(1,{SORTN(search(F3,B3:E));B2:E2},2,0)
Vous pouvez utiliser cette même formule Hlookup pour trouver la valeur de l’en-tête de n’importe quelle cellule dans Google Sheets.
Pour cela, c’est-à-dire pour vos données source, vous devez simplement modifier la référence du tableau dans la formule qui se trouve entre les accolades.
Vous devez apprendre à coder cette formule, puis seulement vous pourrez l’utiliser dans vos données source. Je vais expliquer cela plus en détail dans la partie explicative de la formule ci-dessous.
Explication de la formule – Comment Hlookup recherche un tableau entier et trouve l’en-tête
Dans la formule Hlookup ci-dessus, vous devez simplement changer la ‘plage’. Il est indispensable de comprendre les arguments d’Hlookup pour comprendre ma formule. Permettez-moi donc de commencer par la syntaxe.
Syntaxe :
HLOOKUP(search_key, range, index, [is_sorted])
Selon ma formule, la clé de recherche est 1, la plage est {SORTN(search(F3,B3:E));B2:E2}, l’index est 2 et is_sorted est 0.
Dans cela, vous devez simplement comprendre la plage car c’est la seule partie de la formule que vous devez changer pour votre plage. Les autres valeurs de la formule sont constantes.
Plage virtuelle d’Hlookup en utilisant la fonction Recherche pour rechercher sur n’importe quelle ligne
Voyez la valeur dans la cellule F3. Cette cellule contient la clé de recherche qui est « Adam ».
Dans la plage Hlookup ci-dessus, vous pouvez voir une formule basée sur la fonction Recherche. J’ai utilisé la cellule F3 (c’est-à-dire la clé de recherche « Adam ») dans cette formule.
=recherche(F3,B3:E)
Si vous entrez cette formule dans votre feuille, cela ne fonctionnera pas. Parce qu’il a besoin du support de tableau. Vous devez utiliser les fonctions Filtre, Trier, SortN, Index ou ArrayFormula avec celle-ci.
Nouveau dans ces fonctions de tableau ? Plus tard, vous pouvez consulter mon guide des fonctions.
Il vous suffit de saisir la formule ci-dessus de la manière suivante dans votre feuille et de voir le résultat.
=ArrayFormula(search(F3,B3:E6))
Dans la formule principale, j’ai cette formule dans SORTN. Je n’ai donc pas utilisé ArrayFormula. Plus tard, je vous expliquerai pourquoi j’ai choisi SORTN plutôt que ArrayFormula.
Le nombre 1 dans la formule indique que la première cellule de la deuxième colonne contient la clé de recherche « Adam ».
Changez la clé de recherche « Adam » en « Roger » dans la cellule F3 et voyez ce qui se passe.
Cela signifie que la clé de recherche « Roger » se trouve dans la troisième ligne de la quatrième colonne.
Vous devez comprendre qu’Hlookup ne peut rechercher que dans la première ligne. Il ne peut jamais rechercher dans la deuxième, troisième ou toute autre ligne.
Donc, la plage virtuelle ci-dessus n’a aucun sens, n’est-ce pas ? Mais cela a du sens lorsque vous l’utilisez dans SORTN ! Veuillez lire la suite pour en savoir plus sur cette utilisation.
Affiner la plage virtuelle de Hlookup avec SORTN
Remplacez simplement ArrayFormula dans la formule Recherche par SORTN. Cela ne renverrait alors qu’une seule ligne contenant le nombre 1.
En fait, SORTN trie le tableau virtuel (la sortie de Recherche) et limite la sortie à une seule ligne.
En raison du tri effectué, la ligne contenant le nombre 1 sera toujours en haut.
=SORTN(recherche(F3,B3:E6))
J’ai joint la ligne d’en-tête B2:E2 à celle-ci en tant que deuxième ligne, c’est-à-dire la plage Hlookup (virtuelle).
Rappelez-vous que la clé de recherche est 1 dans Hlookup. Il recherche dans la première ligne de cette plage virtuelle et renvoie la ligne d’en-tête (maintenant deuxième colonne).
Suivez cette méthode pour coder une formule Hlookup qui recherche un tableau entier et trouve l’en-tête dans Google Sheets.
Merci de votre attention, profitez-en !
