Return column containing number 1

Topic

New Reply

I have an excel file with 5 columns A1:E1 and 100 rows. Each row has numbers 1 to 5.
I need a formula in F1 that will return the column that has the number 1 in it. So in the example below, I need it to return A for row 1, E for row 2 and D for row 3.

A B C D E
1 5 4 3 2
5 4 3 2 1
2 5 4 1 3

Thanks for the help.

Reply | Quote

Replies

Dear jha900

If you can insert a row dropping your data to A2 and put the numbers 1 to 5 in Col A to E then the following formula should work.

=HLOOKUP(MATCH(1,A3:E3,0),$A$1:$E$2,2)
copy down to all rows to be evaluated.

Regards,

TD

Reply | Quote

This will work without the intermediate row:
=SUBSTITUTE(ADDRESS(1,MATCH(1,A1:E1,0),4),1,””)

Steve
PS: The above is generic, but if you will be less than 26 columns and you will start with A, you could use
=CHAR(64+MATCH(1,A1:E1,0))

Reply | Quote