Get column letter from value in cell

[Phil Payne]

This works great for characters A-Z but not for columns after Z. E.g., 'AB' I tried the LEFT ADDRESS MATCH formula which works for Columns >Z but not for those <AA.

Any ideas?

The above refers to solution by VOG "=CHAR(66+MATCH(A1,C1:K1,0))" in 2010.​

I found the above "Return column letter from match or lookup" discussion written a long time ago.

I am struggling to make this work because my range goes beyond column Z to column BW!

Does anyone have a solution please?

 

[Phuoc]

Like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
2	28
3
4	AB
5
Sheet1

Cell Formulas
Range	Formula
A4	A4	=SUBSTITUTE(ADDRESS(1,MATCH(A2,A1:AB1,0),4),1,"")

 

[Phil Payne]

Thankyou Phuoc,

I see what you have done and it works.

However my problem is that I am looking for a specific date in a row of dates and that when found returns column letter.

I hope you can see the attached image and have a solution for me.

Thanks for taking the time to help me.

 

[offthelip]

try this ( untested)

=IF(MATCH(A1,C1:K1,0)>26,CHAR(66+MATCH(A1,C1:K1,0)-26) &CHAR(66+MATCH(A1,C1:K1,0)),MATCH(A1,C1:K1,0))

 

[RoryA]

when found returns column letter

Just out of curiosity, why?

 

[Phil Payne]

I want this to be dynamic/manage itself.

Using the date presented by Today() I need to provide a status from the data in the relevant column that holds that months data.

Today is 18th August so the month I need to look at is August 2023.

This will change as soon as we get to 01 September and remain until 1st October etc..

Can you see a way for me to do this?

 

[RoryA]

My question is really why do you need the column letter? How are you using it?

 

[Phil Payne]

As part of a formula that looks down the column.

 

[RoryA]

So I guess you're using INDIRECT? You'd probably be better off using something like INDEX (or V/XLOOKUP) and MATCH.

 

[Phil Payne]

Do you have an alternative method?