SORTBY

Excel Functions

Sorts range or array by column

Syntax

EXCEL

=SORTBY(array, by_array, [sort_order], [array/order], ...)

Arguments

Parameter	Description
`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.

Return Value

Sorted array

Details

The SORTBY function sorts the contents of a range or array based on the values from another range or array. The result is a dynamic array that will "spill" onto the worksheet. If values in the source data change, the output from SORTBY updates automatically. Note that the SORTBY function cannot sort data in place like Excel's Sort command on the Ribbon. SORTBY always outputs results to a new location. The first argument, array, is the data to sort. The second argument, by_array1, contains the values used for sorting. These values can come from an existing range or from an array created by a formula — see Sort by custom list below for an example. The key feature of SORTBY is that by_array1 values do not need to be part of the source data and do not need to appear in the output. However, by_

Examples

Basic usage

To sort by values in another range:

EXCEL

=SORTBY(B5:B16,C5:C16) // sort B by C values, ascending
=SORTBY(B5:B16,C5:C16,-1) // sort B by C values, descending

Basic usage

To sort by a calculated value:

EXCEL

=SORTBY(B5:B16,LEN(B5:B16)) // sort by text length
=SORTBY(B5:B16,RANDARRAY(12)) // random sort

Basic usage

To sort by multiple levels:

EXCEL

=SORTBY(B5:D14,D5:D14,1,C5:C14,-1) // by D ascending, then C descending

Sort by score

One of the key advantages of SORTBY is the ability to sort data using values that don't appear in the output. In the worksheet below, the goal is to s

EXCEL

=SORTBY(B5:B16,C5:C16,-1)

Sort by score

The array argument is B5:B16 (names only), while by_array1 is C5:C16 (scores). Because only the names are provided for array, the scores are used for

EXCEL

=SORTBY(B5:C16,C5:C16,-1) // returns both names and scores

Sort horizontally

Unlike the SORT function, SORTBY does not have an argument that controls sorting by rows versus columns. Instead, SORTBY auto-detects the sort orienta

EXCEL

=SORTBY(B4:K5,B5:K5,-1)

SORTBY

Syntax

Arguments

Return Value

Details

Examples

See Also