Getting Sheet Column Header Name from MATCH

[davidlim]

hi i'm a newbie here and been wanted to ask some questions to excel xperts here


question: How do i get the sheet column header name (A, B, C, ...)?


scenarios:


i have a simple table with header (Product A, Product B, and Product C).


this table starts at cell C3, D3, and D3 (for headers above).


if i do a MATCH for "Product A", it gives me "1" as the result (which is correct as it is the 1st column from the range C3:D3).


However, i wanted to get the actual sheet column name for Product A (intended result is "C").


any ideas for a simple formula (no VBA or macro)


thanks!

 

[Hui]

David


Welcome to Chandoo.org


I am assuming you are looking up the value "Product A" in the Range C3:Z3

B1 is any cell in the Column before your search range


=SUBSTITUTE(ADDRESS(1,COLUMN(B1)+MATCH("Product A",C3:Z3),4),"1","")

 

[Luke M]

The key is doing a MATCH on the entire row. If simply knowing column number was enough, you can do:

=MATCH("Product A",3:3,0)


If you really want the column letter:

=SUBSTITUTE(ADDRESS(1,MATCH("Product A",3:3,0),4,1),"1","")

 

[davidlim]

thanks Hui and Luke!