Excel Functions

Excel SORTBY Function

Pinterest LinkedIn Tumblr

The Excel SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.

Excel-SORTBY-Function

Purpose of Excel SORTBY Function

SORTBY makes use of the changes made to the calculation engine, enabling Excel to return (or “Spill” to use the correct terminology) the results of a single formula into multiple cells.

Return value

SORTBY returns a sorted table based on the column(s) specified.

Syntax

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

Arguments

ArgumentsDescriptionRemarks
arrayThe range, or array to sort.Mandatory
by_array1The first array or range to sort by.Mandatory
[sort_order1]A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order. Optional
[ by_array2] The nth array or range to sort by. Optional
[sort_order2]The order to use for sorting. 1 for ascending, -1 for descending. Default is ascending.Optional

Usage notes

  • 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.
  • Error conditions
  • The by_array arguments must either be one row high, or one column wide.
  • All of the arguments must be the same size.
  • If the sort order argument is not -1, or 1, the formula will result in a #VALUE! error. If you leave out the sort order argument, Excel will default to ascending order.

Availability

Since 2019 for Windows.
Available for Microsoft 365.

Related Posts

Write A Comment