Search
Clear search
Close search
Google apps
Main menu
true

SORTN

Returns the first n items in a data set after performing a sort.

Sample Usage

SORTN(A1:A10, 2)

SORTN(A2:C20, 2, 2, B2:B20, TRUE)

SORTN(A2:C20, 2, 3, B2:B20, TRUE, 3, FALSE)

Syntax

SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)

  • range - The data to be sorted to find the first n items.
  • n - [OPTIONAL - 1 by default] The number of items to return. Must be greater than 0.
  • display_ties_mode - [OPTIONAL - 0 by default] A number representing the way to display ties.
    • 0: Show at most the first n rows in the sorted range.
    • 1: Show at most the first n rows, plus any additional rows that are identical to the nth row.
    • 2: Show at most the first n rows after removing duplicate rows.
    • 3: Show at most the first n unique rows, but show every duplicate of these rows.
  • sort_column1 - [OPTIONAL] - The index of the column in range or a range outside of range containing the values to sort by. A range specified as a sort_column1 must be a single column with the same number of rows as range.

  • is_ascending1 - [OPTIONAL] - TRUE or FALSE indicates how to sort sort_column1. TRUE sorts in ascending order. FALSE sorts in descending order.
  • sort_column2, is_ascending2, ... - [OPTIONAL] - Additional columns and sort order flags used if a tie happens, in order of precedence.

Notes

  • range is sorted only by the specified columns. Other columns are returned in the order they originally appear.
  • If sort_column1 and is_ascending1 aren't included, the sort is performed on the lowest-index column in range, with subsequent columns used to sort if there are ties.

See Also

  • SORT: Sorts the rows of a given array or range by the values in one or more columns.
  • FILTER: Returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
  • MAX: Returns the maximum value in a numeric dataset.
  • INDEX: Returns the content of a cell, specified by row and column offset.
  • LARGE: Returns the nth largest element from a data set, where n is user-defined.

Examples

The following table is used for the examples below.

  A B C
1 Student Test 1 score Test 2 score
2 Alice 100 90
3 Bob 75 85
4 Carol 80 85
5 Devon 100 95
6 Eloise 80 90

 

Formula Result
=SORTN(A2:C6)

Alice 100 90

=SORTN(A2:C6, 2)

Alice 100 90
Bob 75 85

=SORTN(A2:C6, 3, 0, B2:B6, FALSE)

Alice 100 90
Devon 100 95
Carol 80 85

=SORTN(A2:C6, 3, 1, B2:B6, FALSE)

Alice 100 90
Devon 100 95
Carol 80 85
Eloise 80 90

=SORTN(A2:C6, 3, 2, B2:B6, FALSE)

Alice 100 90
Carol 80 85
Bob 75 85

=SORTN(A2:C6, 3, 3, B2:B6, FALSE)

Alice 100 90
Devon 100 95
Carol 80 85
Eloise 80 90
Bob 75 85

=SORTN(A2:C6, 3, 3, 2, FALSE, 3, FALSE)

Devon 100 95
Alice 100 90
Eloise 80 90

Mary is a Docs & Drive expert and author of this help page. Leave her feedback below about the page.

Was this article helpful?
How can we improve it?