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.

My guess is that your question was down voted because the formula is not in english. — marikamitsos, Nov 07 '20 at 06:30
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, Nov 07 '20 at 06:38

player0 · Answer 1 · 2020-11-07T09:34:13.847

4

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

edited Nov 07 '20 at 09:34

answered Nov 07 '20 at 09:11

player0

124,011
12
67
124

1

Thank you very much for your help ! I haven't choosen yet what solution I will take, because I'm doing all with dynamic sheets and range references. Regarding your hyperlink tip, the little strange thing is that at each click on the hyperlink, it acts as if a different url was run, and then the history browser gets inflated. I will look if there's a way not to have this effet.. Anyway thank you ! – Nad Nov 07 '20 at 18:47

marikamitsos · Accepted Answer · 2020-11-07T08:18:51.063

3

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<>'' "))

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.

Functions used:

edited Nov 07 '20 at 08:18

answered Nov 07 '20 at 06:23

marikamitsos

10,264
20
26

2

Thank you so much Marikamitsos, you have exactly found what I needed, sorry for not being so clear. I would get crazy without your help ! And moreover you have translated everything in French syntax, you are incredible. God bless you – Nad Nov 07 '20 at 06:35
1

Glad I could help. You can find the reference for `=char(64+column())` [in this answer](https://stackoverflow.com/a/39644793/1527780) – marikamitsos Nov 07 '20 at 06:39
1

Thank you, It is important for me to understand that new step :-) – Nad Nov 07 '20 at 06:41
1

Please see the updated answer. It may interest you :) – marikamitsos Nov 07 '20 at 08:19
It is usefull, thank you again, I'll let you know within 2 days what final option I will adapt to my situation. Kind regards – Nad Nov 07 '20 at 18:46

score 1 · Answer 3 · answered Nov 10 '20 at 12:22

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?

score 1 · Answer 4 · answered Jan 30 '22 at 00:02

1

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

answered Jan 30 '22 at 00:02

Mads Tuxen

46
9

1

Comment to message below:If you paste the above code in the first or what ever cell and then drag-copy the code, it works for every cell. Of cause if you insert a new column you will have to copy paste the code in that cell some how. Cheers Mads – Mads Tuxen Feb 01 '22 at 16:22
Thank you very much – Nad Feb 04 '22 at 21:22

score 0 · Answer 5 · answered Nov 10 '20 at 12:50

0

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

answered Nov 10 '20 at 12:50

Nad

179
12

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

5 Answers5

spreadsheet demo