Rank and return column header

[Jshendel]

I have a list like this:
A B C D E F etc.
1 ab cd ef gh ij kl
2 8 1 4 2 5 3
3 1 3 9 5 8 2
etc.

For row 2, I want to find the max number and return the column header (ab).
Then, another equation to find the 2nd highest value and return the column
header (ij), etc.

How do I do that?

 

[Ken Puls]

Using your table below, the following will return the column header for
the max value in row 1:

=INDEX($A$1$:$F$1,1,MATCH(MAX(A2:F2),A2:F2,FALSE))

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Bob Umlas, Excel MVP]

In addition to Ken's answer, you can get the 2nd largest via:
=INDEX($A$1$:$F$1,1,MATCH(LARGE(A2:F2,2),A2:F2,0))
Bob Umlas

 

[Jshendel]

Works great!
One more question,
If I have 2 cells with the same value, is there a way to return one as rank
3 and then the other as rank 4?

 

[Jshendel]

To reiterate: what do I do in case of a tie?

Works great!
One more question,
If I have 2 cells with the same value, is there a way to return one as rank
3 and then the other as rank 4?

 

[Biff]

To handle ties it's more complicated. Basically, you have to coerce all the
numbers to be unique.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A$1:F$1,MATCH(LARGE(A$2:F$2-COLUMN(A2:F2)/10^10,ROWS($1:1)),A$2:F$2-COLUMN(A$2:F$2)/10^10,0))

Copied down

Note: when there are ties the leftmost tie will be returned first.

Biff