1. Overview
The Sort() function sorts a given array or cell range according to one or more (column, sort order) pairs and returns a vector of row indices describing the sorted order. This index vector can then be used with the INDEX() function to obtain the final sorted array or range.
2. Function syntax
Sort(array, column1, order1 [, column2, order2, column3, order3])
2.1 Parameters
- array — the source array or cell range to be sorted.
- column1, column2, column3 — 1‑based column indices specifying the relative column positions within the source range used for sorting.
- order1, order2, order3 — sort order for the corresponding column:
- 0 — descending order
- 1 — ascending order
2.2 Result
The function returns a column vector of row indices. Each element of this vector is the 1‑based index of a row in the original array, in the order determined by the specified sort criteria.
To obtain the sorted data itself, use the returned index vector with the INDEX() function.
3. Examples
3.1 Sorting a single‑column numeric array
Ascending order:
=sort({3; 1; 5; 2; 4}, 1, 1)
returns {2; 4; 1; 5; 3}, which means that the sorted order of rows is: row 2, row 4, row 1, row 5, row 3.
Descending order:
=sort({3; 1; 5; 2; 4}, 1, 0)
returns {3; 5; 1; 4; 2}, corresponding to the order: row 3, row 5, row 1, row 4, row 2.
3.2 Sorting a two‑column mixed array
Consider the array:
{2, "b"; 2, "a"; 3, "d"; 3, "c"; 1, "e"}
Sort by first column ascending, then second column ascending:
=sort({2, "b"; 2, "a"; 3, "d"; 3, "c"; 1, "e"}, 1, 1, 2, 1)
returns {5; 2; 1; 4; 3}. This means the sorted row order is: row 5, row 2, row 1, row 4, row 3.
Using INDEX() to obtain the sorted array:
=index({2, "b"; 2, "a"; 3, "d"; 3, "c"; 1, "e"}, {5; 2; 1; 4; 3}, 1)
returns {1; 2; 2; 3; 3}, where {5; 2; 1; 4; 3} is the index vector produced by the Sort() function above. In practice, you would typically use INDEX() with both row and column indices to reconstruct the fully sorted table.
4. Inserting Sort() via the menu
You can insert the Sort() function with just two clicks using the Insert > SORT() command. The “Insert SORT()” dialog box determines the optimum parameters, including the option to copy styles from the selected source data range, and automatically creates the formula.
The source range 16‑element list is a global list created and stored in the settings file. Subsequent source ranges are added in a circular manner by clicking one of the following:
The “Copy as Location” menu command
The “Enter” toolbar button
