SORTN(range, n, display_ties_mode, sort_column, is_ascending) sorts a range and keeps the first n rows.
The third argument decides what happens to ties at the cut:
- 0: exactly n rows, even if the next row has the same score.
- 1: n rows plus every row that ties with the last one. In a leaderboard where three players share third place, all three appear.
- 2: n rows with no duplicates in the sort column: one row per distinct value.
- 3: the first n distinct values, with all their duplicates.
Mode 2 has a handy use: sort the data the way you want first, then SORTN by a group column with a large n, and you keep the first row of each group, for example each team's best player: =SORTN(SORT(A2:C11, 2, FALSE), 99, 2, 3, TRUE).
Use a cell for n, SORTN(A2:B11, E1, 0, 2, FALSE), and the list grows or shrinks when someone changes it.
=SORTN(A2:B11, 3, 1, 2, FALSE)
=SORTN(SORT(A2:C11, 2, FALSE), 99, 2, 3, TRUE)
The top three scores with ties; the best player in each team.