Description
The Excel SORTBY function sorts the contents of a given range or array based on the values of another range or array. It returns a dynamic array result. The range or array that is used to sort does not need to appear in the result.
Syntax
SORTBY( array, by_array1, [sort_order1], [by_array2, sort_order2], … )
Arguments
array – The range or array to sort
by_array1– The array or range to sort by
sort_order1 – [Optional] A number indicating the sort order (1 = ascending, -1 = descending)
by_array2 – [Optional] A second array or range to sort by
sort_order2 – [Optional] A number indicating the sort order for by_array2
Return value
A sorted range or array
How to use the SORTBY function in Excel
Use the Excel SORTBY function to sort a given range or array based on the values in another range or array. Unlike for the more compact SORT function, the array or range to sort by does not have to be included in the result.
The result is a dynamic array of values that will be spilled into the surrounding cells if directly entered into a cell (not wrapped by another function). Every time a value changes in the source range, the SORT function automatically updates and re-sorts the range.
In the above example, we sort the range B6:B13 by a second array C6:C13 in a descending order (-1) using the following formula:
= SORTBY(B6:B13, C6:C13, -1) // return regions by revenue (desc)
The resulting dynamic array does not include the revenue array C6:C13, but only the sorted countries. If you need to include the revenue values, you can easily do this by adjusting the array argument like this:
= SORTBY(B6:C13, C6:C13, -1) // return array by revenue (desc)
SORTBY multiple columns
The SORTBY function also allows you to sort by multiple columns at once. To to that, you have to provide additional by_array/ sort_order pairs like this:
= SORTBY(B6:B13, C6:C13, -1, D6:D13, 1)
Each by_array argument can only be one row or column.
Additional Note
You should always make sure that the array and all by_array references have compatible dimensions. If not, SORTBY will throw a #VALUE! error.
Formula examples
Related Tutorials
Related Functions
Excel SORT function
The Excel SORT function sorts the contents of a given range or array and returns a dynamic array result. Values can be sorted by one more multiple columns.
Excel FILTER function
The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.
Excel UNIQUE function
The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array.
