(Excel) How to return column header of the nth highest values with formula

Question

Given a table like this one below:

	David	Mike	Lisa
David	50	10	40
Mike	0	50	50
Lisa	10	40	50

I'm trying to come up with a column of formulas and information that will automatically rank and label. See below for a correct output example.

Chosen Row ->	Mike (this cell is defined by the user)
Rank	Number
Mike	50
Lisa	50
David	0

Basically the user should be able to change the top right cell and have the rest of the table automatically adjust based on that input.

Here, the user input "Mike" into the cell, so the formulas should look into the row labeled "Mike" (with the values 0, 50, and 50 in that order) and tell me a list of the highest scoring column names, along with their scores.

The only thing that is tripping me up is integrating something into the formula that makes it skip names it has already put into the rank list. As you can see, there are 2 scores of 50 there.

There is a page for this on Microsofts website on dealing with ties, but I don't fully grasp their example where they use Countif, so I have not been able to translate it over for my purposes. They also use rank numbers to the left of the names in their example, but I'd prefer that not be necessary if possible. I don't mind writing the cell formulas by hand if auto-fill doesn't help me with certain aspects.

I know there is a way to accomplish this. I've played with the formulas and did a lot of research. I've just about resorted to coding it all in VBA, but I feel like that would be far more work. At this point I'm so close, I feel like I'm just missing one critical element.

Answer 1

I made in excel 2016, but I think it works in all versions. First I created a Rank, then I used the Index function. Download the file here:

https://1drv.ms/x/s!Apn_2zN0NfqFhWT-OdNazVTlxI2n?e=WsZLuH

Code for Rank using array formula:

=MATCH(LARGE(INDIRECT("B"&$K$1&":D"&$K$1)+COLUMN(INDIRECT("B"&$K$1&":D"&$K$1))/100000,ROW()-2),
INDIRECT("B"&$K$1&":D"&$K$1)+COLUMN(INDIRECT("B"&$K$1&":D"&$K$1))/100000,0)

Answer 2

If one has the dynamic formula in Office 365 then we can use SORT:

=SORT(CHOOSE({1,2},TRANSPOSE(B1:D1),TRANSPOSE(INDEX(B2:D4,MATCH(H1,A2:A4,0),0))),2,-1)

And Excel will spill the results correctly.

---

For older we need two formula:

First get the values in order:

=LARGE(INDEX($B$2:$D$4,MATCH($H$1,$A$2:$A$4,0),0),ROW(ZZ1))

Put that in H2 and copy down.

Then we need to reference those value to get the correct name:

=INDEX($B$1:$D$1,AGGREGATE(15,7,(COLUMN(INDEX($B$2:$D$4,MATCH($H$1,$A$2:$A$4,0),0))-MIN(COLUMN(INDEX($B$2:$D$4,MATCH($H$1,$A$2:$A$4,0),0)))+1)/(INDEX($B$2:$D$4,MATCH($H$1,$A$2:$A$4,0),0)=H2),COUNTIF($H$2:H2,H2)))

Put that in G2 and copy down.