Rank and return column header

J

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?
 
K

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
 
B

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
 
J

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?
 
J

Jshendel

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

Jshendel said:
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?
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top