Exceljet

Quick, clean, and to the point

Excel SORTBY Function

Excel SORTBY function
Summary 
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.
Purpose 
Sorts range or array by column
Return value 
Sorted array
Syntax 
=SORTBY (array, by_array, [sort_order], [array/order], ...)
Arguments 
  • array - Range or array to sort.
  • by_array - Range or array to sort by.
  • sort_order - [optional] Sort order. 1 = ascending (default), -1 = descending.
  • array/order - [optional] Additional array and sort order pairs.
Version 
Usage notes 

The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array with a formula. The result from SORTBY is a sorted copy of the data which will "spill" onto the worksheet into a range. If values in the source data change, the output from SORTBY will update automatically. 

The SORTBY function takes three primary arguments: arrayby_array, and sort_order. The first argument, array, is the range or array to be sorted. The second argument, by_array, contains the values to be used for sorting. These values can come from an existing range, or from an array created by a formula. Note that by_array values do not need to be part of the source data, and do not need to appear in the output. However, by_array must have dimensions compatible with array. For example, if array contains ten rows, by_array should also contain ten rows. Finally, the optional sort_order argument determines sort direction. Use 1 for ascending order and -1 for descending order. By default, SORTBY will sort in ascending order. To sort by more than one level, provide additional sort_by and sort_order arguments in pairs.

Unlike the SORT function, the SORTBY function does not have an argument that explicitly controls sorting by rows versus sorting by columns. Instead, the range or array provided for the by_array argument will determine the sort orientation automatically. When by_array is a vertical range, SORTBY sorts vertically by rows. When by_array is a horizontal range, SORTBY sorts horizontally by columns.

Basic example

To sort range1 in ascending order using values in range2:

=SORTBY(range1,range2// sort range1 by range2, ascending
=SORTBY(range1,range2,1)// sort range1 by range2, ascending

To switch the sort order to descending, use:

=SORTBY(range1,range2,-1)// sort range1 by range2, descending

Sort with arrays

The SORTBY function can be used with arrays and array constants as well. In the example below, we sort the first four letters in the alphabet in a custom order using the numeric values in the second array. By default, sort order is ascending:

=SORTBY({"a","b","c","d"},{3,4,2,1}) // returns {"d","c","a","b"}

Using negative one (-1) for sort order will sort the letters in descending order based on the numeric values in the second array:

=SORTBY({"a","b","c","d"},{3,4,2,1},-1) // {"b","a","c","d"}

Ranges and arrays can be mixed. The formula below will sort the values in A1:A5 using the array constant provided:

=SORTBY(A1:A5,{3;5;2;1;4})

Note the dimensions or the range argument must be compatible with those of the by_array argument. In this case, both contain five rows.

Example - sort by score

SORTBY function example - sort by score

In the example shown above, the formula in E5 is:

=SORTBY(B5:B14,C5:C14,-1) // sort by grades descending

The output in E5:E14 is the list of names sorted by scores in descending order. To sort by grades in ascending order use:

=SORTBY(B5:B14,C5:C14,1) // sort by grades ascending

Sort by two levels

To sort a set of data by two levels, the generic formula is:

=SORTBY(array,by_array1,sort_order1,by_array2,sort_order2)

where array is a range that contains all data to be sorted, and by_array1 and by_array2 are compatible ranges or arrays, and sort_order1 and sort_order2 are provided as 1 (ascending) or -1 (descending). Example: sort by two columns.

Notes

  • All array arguments must have compatible dimensions or SORTBY will return #VALUE!
  • 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 for sort_order, SORTBY will sort in ascending order.
SORTBY is a new function available in Excel 365 only.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.