Table of Contents
The Excel SORTBY function is a DYNAMIC ARRAY FUNCTION that can sort the contents of an array or range based on the values from another range or array. The array or range used to sort need not appear in the result.
Lookup and reference
Sort an array or range
=SORTBY (array, by_array, [sort_order], [array/order], ...)
array – Array or range to sort
by_array – Array or range to sort by
sort_order – [optional] Sort order, 1 for Ascending order (default) and -1 for Descending order
array/order – [optional] Additional array and sort order pairs
The main difference between SORT and SORTBY functions is that the latter supports multiple levels of sorting.
In this example, SORTBY function is used to sort a list containing the names of some students and their marks. Names are in column B (B3:B12) and corresponding marks are in Column C (C3:C12). The following formula will sort the list of students in the Ascending Order of marks.
Here the SORTBY function has sorted and returned the entire array as I have used the entire array (B3:C12) as the first argument. If you want the SORTBY function to return only the names, use the address of that particular array (B3:B12) in the place of the first argument.
To sort the list in the Descending Order of marks scored, use -1 in the place of the third argument, sort_order
Let’s see an example of performing multiple levels of sorting using the SORTBY function.
At first, we do a single level of sorting. To sort the list of employees (B3:D14) according to their salary, use the following formula.
Now, 2 levels of sorting using the SORTBY function.
To sort the list based on the departments and then salary use the following formula.
Employee list is sorted according to their departments called Accounts, Admin and H.R. In each department employee list sorted on Ascending of salary.
The SORTBY function returns an array of values and these values ‘spill’ into the spill range on the worksheet. If this spill range contains any data, the function will return a #SPILL! error.