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

true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.

Search
Clear search
Close search
Google apps
Main menu
12393301765799421573
true
Search Help Center
true
true
true