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

1
votes

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.

Lookup formula

5
google-sheetsreferencematchgoogle-sheets-formulalookup
My guess is that your question was down voted because the formula is not in english. – marikamitsos
ok, sorry, I already have diffuculties with french syntax and am not used to english one, I have always proceeded like that but I'll try to do convert next time if so. Thank you very much – Nad

5 Answers

4
votes

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

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

enter image description here

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; ))

enter image description here

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; ))

enter image description here

3
votes

If I understand your question correctly, you want to

convert the column index into the corresponding column letter for FRANCE.

Please use the following formula in cell L2

=CHAR(64+QUERY(ArrayFormula(TRANSPOSE({IFERROR(REGEXEXTRACT(B2:F2;K10));
                                       COLUMN(B2:F2)})); 
                  "select Col2 where Col1<>'' "))

enter image description here

UPDATE

How to simplify your formula and free it from being tied to the specific columns (as it now is).

Taking a closer look at what you are trying to do, I believe you would greatly benefit if you could make 2 simple modifications.

  • Add an extra row above your countries having the zones' letters
  • Replace your formula to the following one

(Notice the changed columns.)

=QUERY(ArrayFormula(TRANSPOSE({IFERROR(REGEXEXTRACT(E3:I3;N11));
                (E2:I2)})); 
                  "select Col2 where Col1<>'' ")

You can now freely move and place your data wherever you want.

enter image description here

Functions used:

1
votes

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?

enter image description here

0
votes

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

enter image description here

0
votes

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