# [excel] Extracting the top 5 maximum values in excel

I have an excel file with one column corresponding to the player's name and the other column corresponding to the baseball statistic OPS.

`````` OPS        Player
1.000   player 1
5.000   player 2
3.000   player 3
1.000   player 4
---     player 5
4.000   player 6
1.000   player 7
---     player 8
1.000   player 9
---      player 10
1.333   player 11
1.000   player 12
2.000   player 13
---     player 14
---     player 15
---     player 16
1.500   player 17
3.500   player 18
1.500   player 19
---     player 20
1.000   player 21
1.000   player 22
0.000   player 23
0.000   player 24
0.500   player 25
0.000   player 26
0.667   player 27
``````

Now, in excel, I need to figure out how to create a formula that returns a column of the names of the players with the top 5 OPS value. Thus, I would like for the query to return a 5 x 1 column vector in excel. What cell formula could I use to achieve this?

Also, given that their will be repeating values of OPS, I need the expression to be robust against ties.

This question is related to `excel`

Given a data setup like this: The formula in cell D2 and copied down is:

``````=INDEX(\$B\$2:\$B\$28,MATCH(1,INDEX((\$A\$2:\$A\$28=LARGE(\$A\$2:\$A\$28,ROWS(D\$1:D1)))*(COUNTIF(D\$1:D1,\$B\$2:\$B\$28)=0),),0))
``````

This formula will work even if there are tied OPS scores among players.

There 3 functions you want to look at here:

I ran a sample in Excel with your OPS values in Column B and Players in Column C, see below: • In Cells A13 to A17, the values 1 to 5 were inserted to specify the nth highest value.
• In Cell B13, the following formula was added: `=LARGE(\$B\$2:\$B\$11, A13)`
• In Cell C13, the following formula was added: `=INDEX(\$C\$2:\$C\$11,MATCH(B13,\$B\$2:\$B\$11,0))`
• These formulae get the highest ranking OPS and Player based on the value in A13.
• Simply select and drag to copy these formulae down to the next 4 cells which will reference the corresponding ranking in Column A.

Put the data into a Pivot Table and do a top n filter on it ``````=VLOOKUP(LARGE(A1:A10,ROW()),A1:B10,2,0)
``````

Type this formula in first row of your sheet then drag down till fifth row...

its a simple `vlookup`, which finds the `large` value in array `(A1:A10)`, the `ROW()` function gives the row number (first row = 1, second row =2 and so on) and further is the lookup criteria.

Note: You can replace the `ROW()` to 1,2,3,4,5 as requried...if you have this formula in other than the 1st row, then make sure you subtract some numbers from the `row()` to get accurate results.

EDIT: TO check tie results

This is possible, you need to add a helper column to the sheet, here is the link. Do let me know in case things seems to be messy....

To my mind the case for a PT (as @Nathan Fisher) is a 'no brainer', but I would add a column to facilitate ordering by rank (up or down): `OPS` is entered as VALUES (Sum of) twice so I have renamed the column labels to make clearer which is which. The PT is in a different sheet from the data but could be in the same sheet.

Rank is set with a right click on a data point selected in that column and `Show Values As...` and `Rank Largest to Smallest` (there are other options) with the Base field as `Player` and the filter is a `Value Filters`, `Top 10...` one: Once in a PT the power of that feature can very easily be applied to view the data in many other ways, with no change of formula (there isn't one!).

In the case of a tie for the last position included in the filter both results are included (Top 5 would show six or more results). A tie for top rank between just two players would show as `1 1 3 4 5` for Top 5.