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. The range or array used to sort does not need to appear in the source data. In the example shown, 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
Notes
- 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.
SORTBY is a new function available in Excel 365 only.
SORTBY formula examples
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is...
In this example, the goal is to sort a list of names and birthdays by month and year. The complication is that the birthdays also include a birth year, so if we try to sort the raw data by birthdays, we'll end up with a...
The goal of this formula is to return the next n birthdays in a set of 25 birthdays as shown, based on the current date. The complication is that the birthdays contain the year of birth, so we can't filter and sort the...
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called "names" which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this...
The SORTBY function allows sorting based on one or more "sort by" arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the...
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by...
In the example shown, we want to sort data in B5:D14 first by group in descending order. Here is the configuration needed:
array = B5:D14
by_array1 = D5:D14
sort_order1 = 1
The formula below will sort data by...
The SORT function sorts a range using a given index, called sort_index. Normally, this index represents a column in the source data.
However, the SORT function has an optional argument called "by_col" which allows...
The SORTBY function can sort values in a range with an array that doesn't exist on the worksheet.
In this example, we want to sort the values in B5:B15 by the number of characters each string contains. Working from...
The goal in this example is to use a formula to report which things exist a cell. The list of things to check for is in the named range things (E5:E9). The result is returned as a comma separated text string.
The first...
In this example, we are sorting a table with 10 rows and 3 columns. In the range J5:J7 (the named range "custom"), the colors "red", "blue", and "green" are listed in the desired sort order. The goal is to sort the...
Related videos
In this video, we’ll look at a basic example of sorting with the SORTBY function. With the SORTBY function, you supply sort order in pairs of inputs. Each pair has an argument for range, and an argument for sort order.
With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior. In this video, we quickly demo all 6.
In this video, we’ll look at how to sort by multiple columns with the SORT and SORTBY functions.
In this video, we’ll look at how to perform a random sort with the SORTBY function, with help from the RANDARRAY function.
In this video, we'll look at how to sort with SORTBY function using a custom list. We use the custom list to specify a custom order.
Related functions
See also
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
