How to Use the SORTN Function in Google Sheets
=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...).
What SORTN does
A leaderboard needs the top three, not the whole list. SORT returns everything in order, and you would then have to cut it down. SORTN does both in one step: it sorts and returns only the first n rows.
SORTN syntax
=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)
range: the data to sort in order to find the first n items.n: optional. The number of items to return, with a default of 1. It must be greater than 0.display_ties_mode: optional, default 0. A value of 0 returns at most the first n rows, 1 adds any extra rows identical to the nth row, 2 returns the first n rows after removing duplicate rows, and 3 returns the first n unique rows with every duplicate of those rows included.sort_column1: optional. A column index within the range, used for sorting.is_ascending1: optional. TRUE sorts ascending, FALSE descending.
Per the help page, only the specified sort columns determine order. If you give no sort column, the sort uses the lowest-index column, and later columns break ties.
A step-by-step example
Here are five scores in cells A1:B6.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ann | 82 |
| 3 | Ben | 54 |
| 4 | Cara | 67 |
| 5 | Dev | 91 |
| 6 | Eli | 73 |
=SORTN(A2:B6, 2, 0, 2, FALSE)
This returns two rows: Dev with 91 and Ann with 82. Here is why: the data is sorted by column 2, the score, in descending order, and only the first 2 rows are kept. Display mode 0 means no extra tied rows are added. To get the lowest score instead, sort ascending and keep one row:
=SORTN(A2:B6, 1, 0, 2, TRUE)
This returns Ben with 54.
Common mistakes
- Forgetting to name a sort column. The sort then uses the first column.
- Skipping display_ties_mode when you also give sort columns. Pass 0 as a placeholder.
- Mixing up ascending and descending. TRUE is ascending.
Try it yourself
Use the table above (A1:B6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Top two scores
Write a formula that returns the top 2 rows of A2:B6 by score (column 2), highest first, with no extra tied rows.
Exercise 2: Lowest score
Write a formula that returns the single row with the lowest score in A2:B6.
What to learn next
- SORT: sort a range with a formula.
- RANK: rank a value within a list.
- PERCENTILE: the value below which a share of the data falls.
Syntax and rules in this guide were checked against Google's SORTN help page. See all guides.