SORTBY function
Values
The values to sort.
SortValues
The first sort array, whose values determine the order of the values of the first array.
SortOrder
The sort order of the first sort array. Use SortOrder.AscendingSortOrder,Ascending or 1 for ascending order (greater values appear last) and SortOrder.DescendingSortOrder,Descending or -1 for descending order (greater values appear first). If omitted, ascending order is used.
OtherSortValues
An additional sort array, which is used to break ties if the values of the preceding sort arrays do not produce a conclusive sort order.
OtherSortOrder
The sort order of the sort array given as the prior parameter. Use SortOrder.AscendingSortOrder,Ascending or 1 for ascending order (greater values appear last) and SortOrder.DescendingSortOrder,Descending or -1 for descending order (greater values appear first). If omitted, ascending order is used.
Returns
A sorted version of the array given as the first parameter.
Sorts and returns a given array, where the sort order is based on sort arrays given to this function. Values are compared as though the SORT function was used.
Consider this formula:
The formula above returns { "first", "second", "third" }{ "first"; "second"; "third" }. "third" in the first array corresponds to 3 in the sort array, "second" corresponds to 2 and "first" corresponds to 1. When the sort array is sorted, { 1, 2, 3 }{ 1; 2; 3 } is the result, meaning that the first array is sorted in that order as well: { "first", "second", "third" }{ "first"; "second"; "third" }. In other words, the sort order of the first array is based on the sort array.
Break ties with multiple sort arrays
Any number of sort arrays may be given, where later sort arrays are only consulted to break ties. Consider this formula:
The formula above can legally return both { "b", "c", "a" }{ "b"; "c"; "a" } and { "c", "b", "a" }{ "c"; "b"; "a" }, because both "b" and "c" are associated with 2. To break the tie, another sort array may be given:
The formula above always returns { "c", "b", "a" }{ "c"; "b"; "a" } because "b" is associated with TRUE while "c" is associated with FALSE, and TRUE is considered greater than FALSE in terms of sort order.
Sort order
To specify the sort order of the given sort arrays, pass an extra parameter just after the sort array. (The first sort order is optional and is set to SortOrder.AscendingSortOrder,Ascending if omitted, while the others are required.)
Specify either SortOrder.AscendingSortOrder,Ascending or 1 for ascending order (greater values appear last) and -1 or SortOrder.DescendingSortOrder,Descending for descending order (greater values appear first). This function accepts numbers in addition to descriptive names for the sort order to ensure compatibility with spreadsheets.
Examples
Returns { 2, 1 }{ 2; 1 }, because the sort order of the second array is used to sort the first array. 1 corresponds to "z" and 2 corresponds to "a". The sort order has been omitted, and is assumed to be ascending (greater values appear last).
Returns { 2, 1 }{ 2; 1 }, because the sort order of the second array is used to sort the first array. 1 corresponds to "z" and 2 corresponds to "a". The sort order has been given explicitly.
Returns { 2, 1 }{ 2; 1 }, because the sort order of the second array is used to sort the first array. 1 corresponds to "z" and 2 corresponds to "a". The sort order has been given explicitly as the number 1, instead of SortOrder.AscendingSortOrder,Ascending.
Returns { 1, 2 }{ 1; 2 }, because the sort order of the second array is used to sort the first array when sorted in descending order (greater values appear first). 1 corresponds to "z" and 2 corresponds to "a".
Returns { 1, 2 }{ 1; 2 }, because the sort order of the second array is used to sort the first array when sorted in descending order (greater values appear first). 1 corresponds to "z" and 2 corresponds to "a". The sort order has been given explicitly as the number -1, instead of SortOrder.DescendingSortOrder,Descending.
Returns { 2, 1, 3 }{ 2; 1; 3 }, as -2 — which is associated with 3 — is considered the greatest array element of the first sort array, as it is sorted in descending order, thereby placing 3 as the last element. The first sort array has no sort order preference regarding the other elements of the first array — 1 and 2. As such, the second sort array, which is sorted in ascending order, is used to break the tie. 30 is associated with 1, which is greater than 20, which is associated with 2, meaning that 1 is placed to the right of 2.