Google sheet - Dynamic lookup function for string in an horizontal range, in order to get the matched column index letter reference

Question

Could you please help retrieving the column index letter in which is the result of a dynamic lookup formula ? Here I fill a country in K10 and look in which cell it is in Data range B2:F2, and then I need the correspondant column letter. In my example, it should be column B instead of E (in cell L2 or L10). I can't see what's wrong with the formula I picked up somewhere in this forum :

=MAJUSCULE(CAR(COLONNE(INDEX(B2:F2;EQUIV(K10;B2:F2)))+96))

Here is the sheet https://docs.google.com/spreadsheets/d/1B5t4QrSX1_cI1J66nSaHghhHBkz7CBHQKyTm-_mPhp8/edit?usp=sharing

Thank you very much.

Answer 1

proper way (works with column range A-ZZZ) would be:

=SUBSTITUTE(ADDRESS(2; MATCH("*"&K10&"*"; A2:F2; 0); 4); 2; )

---

you can even create jumping link:

=HYPERLINK("#gid=569443896&range="&
            ADDRESS(2; MATCH("*"&K10&"*"; A2:2; 0); 4); 
 SUBSTITUTE(ADDRESS(2; MATCH("*"&K10&"*"; A2:2; 0); 4); 2; ))

spreadsheet demo

---

or jump straight to B4:

=HYPERLINK("#gid=569443896&range="&
            ADDRESS(1+MATCH(M10; A:A; 1); MATCH("*"&K10&"*"; A2:2; 0); 4); 
 SUBSTITUTE(ADDRESS(2;                    MATCH("*"&K10&"*"; A2:2; 0); 4); 2; ))

Answer 2

Thanks to all of you, I finally adapted the proposed formulas in order to have dynamic sheet-cell reference wrapped in a dynamic hyperlink with pre-named ranges :

=LIEN_HYPERTEXTE("#gid="&RECHERCHEV(S3;Sheets_GID;2)&"range="&ADRESSE(1+EQUIV(Q3; INDIRECT(S3&"_Poids"); 1); EQUIV("*"&M3&"*"; INDIRECT(S3&"_Zones"); 0); 4);SUBSTITUE(ADRESSE(2; EQUIV("*"&M3&"*"; INDIRECT(S3&"_Zones"); 0); 4); 2; ))

I get the sheets GID thanks to Player0's script found here : Is there a custom function or script that returns gid of a specific sheet in Google Sheets?

Answer 3

Here is some code that will give you the row column letter when you give it the cell id (works no matter where in the sheet you place it.) you can put the cell id inside the column bracket. eg column(B87) or just copy paste it as is anywhere.

=IF(TRUNC((column()-1)/26)<1;CHAR((MOD((column()-1);26))+65);CHAR(TRUNC((column()-1)/26)+65)&CHAR((MOD((column()-1);26))+65))

hope this is usefull. Cheers Mads

Answer 4

But it doesn't work anymore when I insert some columns to the table reference, I have to correct some details..