SORTBY Function

by | Feb 19, 2019 | Excel Formulas

sortby function

Excel SORTBY function is a lookup and reference formula that sorts the values in a range or array based on the values in a corresponding range or array. In this guide, we’re going to show you how to use the Excel SORTBY function and also go over some tips and error handling methods.

The Dynamic Array functions can populate an array of values in a range of cells based on a formula. This behavior is called spilling and can help overcome the limitations of array formulas.

Supported versions

  • At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it's ready, the feature is planned for release for Office 365 users.

Syntax

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2],…)

Arguments

array

The range or array to be sorted.

by_array1

The first array or range to sort by.

[sort_order1]

Optional. A numeric value indicating the order of sorting for by_array1.

  • Ascending: 1 (default)
  • Descending: -1

by_arrayN

The nth array or range to sort by.

[sort_orderN]

Optional. A numeric value indicating the order of sorting for by_arrayN.

  • Ascending: 1 (default)
  • Descending: -1


Examples

Note that we're using named ranges in our sample formulas to make them easier to read. This is not required.

Basic usage

=SORTBY(NameAndType,HP,-1)
is a simple use case with the Excel SORTBY function. The formula returns values from the named range NameAndType sorts the results based on the named range HP. The sorting is done in descending order.

Sort by multiple columns

=SORTBY(Pokemon,Type,-1,Name,1)
formula demonstrates sorting multiple columns (Pokemon) based on 2 columns (Type and Name) in different orders (Type in descending, Name in ascending).

Shuffle values

=SORTBY(Name,RANDARRAY(COUNTA(Name)))
shows how to shuffle values using the SORTBY function. Although, the SORTBY function is designed to be used for sorting, it can also shuffle a list of values when combined with the RANDARRAY function. The RANDARRAY function returns an array of randomly generated numbers. Using these random generated numbers for the by_array argument, the array gets sorted randomly. Please be aware that the order will change with every re-calculation.

The COUNTA function in the formula retrieves the size of the array dynamically.

Download Workbook

Tips

  • If you need to sort a range or array based on a different range or array, you should use SORTBY. The SORTBY function can handle column changes because it references a range rather than an index number like the SORT function does.
  • If the list to be sorted is based on its own values, the SORT function is more suitable.

Issues

#SPILL!

If there isn't enough space for adding the results below the formula, you will see a #SPILL! error. Excel marks the target range with dashed lines. Clear the contents of the cells in this range, and Excel will automatically update the results.

#VALUE!

  • If array and by_array arguments have different sizes, the function returns a #VALUE! error.
  • If [sort_order] argument is neither 1 nor -1, the function returns a #VALUE! error.