SORTN sorts a range and returns a limited result without rearranging the source. Its tie mode decides whether the limit counts rows or distinct sort-key groups, and whether tied rows remain visible.
SORTN function syntax
=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)- range: the source rows, excluding their header.
- n: a positive output limit; the default is 1.
- display_ties_mode: 0 limits rows, 1 includes cutoff ties, 2 selects representatives of distinct keys, and 3 includes all rows in selected distinct-key groups.
- sort_column1: a 1-based column within the range, or an aligned external one-column range.
- is_ascending1: TRUE for smallest first, FALSE for largest first. Additional sort pairs refine the ordering and ties.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Name | Score |
|---|---|
| Maya | 100 |
| Leo | 90 |
| Ana | 90 |
| Omar | 80 |
| Ivy | 70 |
Return the top three rows
Enter this formula in A9. Column 2 is Score, and FALSE sorts largest first. Mode 0 returns at most three rows: Maya, Leo and Ana.
=SORTN(A2:B6,3,0,2,FALSE)Result: Maya, 100; Leo, 90; Ana, 90.
Return the bottom three rows
Ascending order returns Ivy, Omar and Leo. Leo and Ana are tied at 90; mode 0 keeps only enough rows to meet the limit.
=SORTN(A2:B6,3,0,2,TRUE)Result: Ivy, 70; Omar, 80; Leo, 90.
Include every row tied at the cutoff
With n=2 and mode 1, Maya is first and the cutoff score is 90. Both Leo and Ana are included, so three rows are returned.
=SORTN(A2:B6,2,1,2,FALSE)Result: Maya, 100; Leo, 90; Ana, 90.
Return one representative per distinct score
Mode 2 with n=3 returns one row for each of the top three score values: 100, 90 and 80. Leo represents the 90 group because he appears first among those tied source rows.
=SORTN(A2:B6,3,2,2,FALSE)Result: Maya, 100; Leo, 90; Omar, 80.
Return everyone in the top three score groups
Mode 3 selects the top three distinct score groups and keeps every row in them. Maya, Leo, Ana and Omar appear. This differs from mode 1’s cutoff at the nth row.
=SORTN(A2:B6,3,3,2,FALSE)Result: Maya, 100; Leo, 90; Ana, 90; Omar, 80.
Return a single best row
Set n to 1 and mode 0 to return Maya and 100. For products and sales, the same pattern returns the highest-sales product row, rather than only the amount.
=SORTN(A2:B6,1,0,2,FALSE)Result: Maya, 100.
Add an explicit secondary sort
After descending Score, ascending Name puts Ana before Leo within the score of 90. The result is Maya, Ana and Leo. Additional sort keys also affect what counts as a tie.
=SORTN(A2:B6,3,0,2,FALSE,1,TRUE)Result: Maya, 100; Ana, 90; Leo, 90.
Rank only eligible rows
FILTER first removes scores below 80. SORTN then returns the first two rows by descending score. Change the filter condition to match your actual eligibility rule.
=SORTN(FILTER(A2:B6,B2:B6>=80),2,0,2,FALSE)Result: Maya, 100; Leo, 90.
Other Google Sheets articles you may also like