How do I return a column name for highest value of a row

[colinheslop1984]

I need a formula to return the name of the column which contains the highest number within a chosen row. Below is the table layout I am using. Assume the column names are also the cell references I am using, i.e. B1 would be 200. I need the formula to tell me "H" is the highest and return the name of the column for H. The cell I want to show this data is R2.

[TABLE="width: 960"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] 200[/TD]
[TD] 12[/TD]
[TD] 57[/TD]
[TD] 43[/TD]
[TD] 500[/TD]
[TD] 76[/TD]
[TD] 890[/TD]
[TD] 355[/TD]
[TD] 650[/TD]
[TD] 138[/TD]
[TD] 99[/TD]
[TD] 70[/TD]
[TD] 8[/TD]
[TD="align: right"]0.0%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.0%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.0%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What do those 0.0% mean?

 

[Scott T]

If there is a tie for highest value this will return the first header that has the max value.

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	header B	header C	header D	header E	header F	header G	header H	header I	header J	header K	header L	header M	header N
2	200	12	57	43	500	76	890	355	650	138	99	70	8
3
4	header H
Sheet1

Cell Formulas
Range	Formula
B4	=INDEX(B1:N1,MATCH(MAX(B2:N2),B2:N2,0))

 

[colinheslop1984]

Nothing it can be ignored

 

[colinheslop1984]

great, that worked perfectly. Now what if I wanted to know the top 5? in ranking order. SO R1 would show top, R2 second, R3 third, etc?

 

[Joyner]

You can change the MAX to: LARGE(B2:N2,1), LARGE(B2:N2,2)....LARGE(B2:N2,5)

 

[Aladin Akyurek]

great, that worked perfectly. Now what if I wanted to know the top 5? in ranking order. SO R1 would show top, R2 second, R3 third, etc?

Do you really want to return column letters or the headers from row 1 assuming that you have headers in row 1?

 

[offthelip]

if you want the column letter this will work up to column Z
=CHAR(MATCH(MAX(B2:N2),B2:N2,0)+65)

 

[Joyner]

if you want the column letter this will work up to column Z
=CHAR(MATCH(MAX(B2:N2),B2:N2,0)+65)

Or for all column Letters using a formula I got from Aladin:

=SUBSTITUTE(ADDRESS(1,MATCH(MAX(B2:N2),B2:N2,0)+1,4),1,"")