Extracting top 5 maximum values (column wise, not row wise)

Question

I'm looking to perform this operation for my data when it's organised in rows rather than in columns. How would I adapt the formula to do this?

Sample data:

Score   9   2   3   5   9   6   9   9   9
Player  P1  P2  P3  P4  P5  P6  P7  P8  P9

Do you want the top 5 _including_ duplicates or excluding them? — Tim Biegeleisen, Nov 16 '15 at 01:16
@TimBiegeleisen Including them please! It needs to account for ties, much like the formula in the link I posted. However, we can assume there won't be more than 5 top values in my actual data set. — Thev, Nov 16 '15 at 01:20

score 1 · Accepted Answer · answered Nov 16 '15 at 01:50

1

Using the formula from your link in B4 and auto-fill to the right:

=INDEX($B$2:$J$2,MATCH(1,INDEX(($B$1:$J$1=LARGE($B$1:$J$1,COLUMNS($A4:A4)))*(COUNTIF($A4:A4,$B$2:$J$2)=0),),0))

answered Nov 16 '15 at 01:50

Dirk Reichel

1 Answers1