Syntax
=SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], ...)Arguments
| Argument | Required | Description |
|---|---|---|
range | Required | The data to sort |
n | Optional | How many rows to return (default 1) |
display_ties_mode | Optional | 0 first n rows (default), 1 also rows tied with the last one, 2 drop duplicate rows, 3 first n unique rows with all their duplicates |
sort_column | Optional | The column (from 1) to sort by |
is_ascending | Optional | TRUE (default) ascending, FALSE descending |
How to use SORTN
When SORTN returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
SORTN takes 1 required argument and 4 optional arguments, and accepts more values in the same pattern. Type =SORTN( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=SORTN({3;1;2}, 2)Result Spilled result
| A | |
|---|---|
| 1 | 1 |
| 2 | 2 |
=SORTN({"a",3;"b",1;"c",2}, 1, 0, 2, FALSE)Result Spilled result
| A | B | |
|---|---|---|
| 1 | a | 3 |
Related array functions
- SCANApplies a LAMBDA to each value with a running accumulator and returns every intermediate result.
- SEQUENCEReturns a list of sequential numbers in an array of the given size.
- SORTSorts the rows or columns of a range or array.
- SORTBYSorts a range or array by the values of other ranges or arrays.
- TAKEReturns a number of rows or columns from the start (positive) or end (negative) of an array.
- TOCOLReturns the values of an array as a single column.
- TOROWReturns the values of an array as a single row.
- TRIMRANGERemoves empty rows and columns from the edges of a range.
- UNIQUEReturns the unique rows (or columns) of a range or array.