Rank and return column header
 

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?

Rank and return column header
 

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

Jshendel wrote:
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?

Rank and return column header
 

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

"Ken Puls" wrote:

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

Jshendel wrote:
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?

Rank and return column header
 

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?

"Bob Umlas, Excel MVP" wrote:

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

"Ken Puls" wrote:

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

Jshendel wrote:
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?

Rank and return column header
 

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

"Jshendel" wrote:

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?

"Bob Umlas, Excel MVP" wrote:

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

"Ken Puls" wrote:

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

Jshendel wrote:
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?

Rank and return column header
 

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

"Jshendel" wrote in message
...
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?