The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array. The range or array used to sort does not need to appear in results.
Syntax:= SORTBY (array, by_array, [sort_order], [array/order], …)
The SORTBY function syntax has the following arguments:
- array: The range of cells, or array of values to be returned by the function
- By_array1: The range of cells or array of values to sort by.
- [sort_order1]: 1 = sort By_array1 in ascending order, -1 = sort By_array1 in descending order (if excluded it will default to 1).
- [By_array2…]: The range of cells or array of values to apply the second sort by. This argument is entirely optional; you can exclude this if you only need one sort column.
- [sort_order2]: the sort order to apply to the By_array2 1= ascending, -1 = descending.
Example: Let’s look at some Excel SORTBY function examples and explore how to use the SORTBY function as a worksheet function in Microsoft Excel:
Let’s say we want to sort the table in cells A2:E17 by the employee’s Date Joined and then by Salary in ascending order. We can do this easily using the SORTBY Function as shown below:
Syntax: =SORTBY(A2:E17;C2:C17;1;E2:E17;1)
Result:
Sort Randomly
We can use the RANDARRAY function, which is another of the new dynamic array functions, to randomly sort a list. This could be used as an alternative to randomly drawing names out of a hat, as shown in the example below:
Syntax: =SORTBY(A2:A17;RANDARRAY(16))
Note: The 16 in the RANDARRAY tells it to return 16 random numbers between 0 and 1. The bonus with this solution is there are no repeats.
Tip: The RANDARRAY function is volatile, so once you have your results be sure to copy and paste them as values, so they don’t keep changing every time you edit a cell etc.
Note:
- All arguments must have compatible dimensions, i.e. array and by_array1 must contain the same number of rows.
- The by_array arguments can only be one row or one column.
- The sort_order argument can only be -1 (descending) or 1 (ascending). If no value is provided, SORTBY will sort in ascending order.