Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank a column but not include some cells | Excel Discussion (Misc queries) | |||
Filtering Data with Rank Function | Excel Worksheet Functions | |||
Look up one value and return multiple corresponding values in exce | Excel Discussion (Misc queries) | |||
Display the max, then the next down, then the next down, etc. | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |