Retrieving column and row header for n maximum values in array with repeated values

Hi all!

I've got this spreadsheet with points obtained by different players (columns) using different items (rows).

I'd like to get an ordered list of the n maximum values within this spreadsheet, with the name of the item used and the name of the player it corresponds to (i.e., getting the column and row header for each of the n maximum values). The issue is that some of the values are repeated, and excel doesn't seem to know which item/player it should reference in this case.

My data is located in array B2:H60; the column headers are in B1:H1 and the row headers are in A2:A60, and I want to have 3 columns: one with the top n values, another with the row header corresponding to the nth largest number and another with the column header corresponding to the nth largest value

Here's an image of my spreadsheet for reference.

Screen Shot 2023-02-17 at 13.38.30.png

Thanks a lot!

Hi,

here's the excel file. As you can see, column L has the list of the largest n values (that was easy), while column M has the list of the associated players (that was a bit trickier for a noob like me), but when there are repeated values on column L, it doesn't seem to know how to differentiate them. Column N should have the item name associated to the largest n value, but I don't know how to do it.

Thanks again!

Try this,

M2
=INDEX($A$1:$J$1,AGGREGATE(15,6,COLUMN($B$1:$J$1)/($B$2:$J$60=L2),COUNTIF($L$2:L2,L2)))

copied down

I'm afraid this doesn't work...

Works like a charm!

Thanks a lot windknife, I couldn't have done it myself