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.
SORTBY( array, by_array1, [sort_order1], [by_array2, sort_order2], … )
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
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:
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 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:
Each by_array argument can only be one row or column.
You should always make sure that the array and all by_array references have compatible dimensions. If not, SORTBY will throw a #VALUE! error.
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.
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.