Excel SORTBY function

How to use the Excel SORTBY function

Description

The Excel SORTBY function sorts the contents of a given range or array based on the values of another range or array. It returns a dynamic array result. The range or array that is used to sort does not need to appear in the result.

Syntax

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

Arguments

array – The range or array to sort

by_array1– The array or range to sort by

sort_order1 – [Optional] A number indicating the sort order (1 = ascending, -1 = descending)

by_array2 – [Optional] A second array or range to sort by

sort_order2 – [Optional] A number indicating the sort order for by_array2

Return value

A sorted range or array

How to use the SORTBY function in Excel

Use the Excel SORTBY function to sort a given range or array based on the values in another range or array. Unlike for the more compact SORT function, the array or range to sort by does not have to be included in the result.

The result is a dynamic array of values that will be spilled into the surrounding cells if directly entered into a cell (not wrapped by another function). Every time a value changes in the source range, the SORT function automatically updates and re-sorts the range.

In the above example, we sort the range B6:B13 by a second array C6:C13 in a descending order (-1) using the following formula:

= SORTBY(B6:B13, C6:C13, -1)  // return regions by revenue (desc)

The resulting dynamic array does not include the revenue array C6:C13, but only the sorted countries. If you need to include the revenue values, you can easily do this by adjusting the array argument like this:

= SORTBY(B6:C13, C6:C13, -1)  // return array by revenue (desc)

SORTBY multiple columns

The SORTBY function also allows you to sort by multiple columns at once. To to that, you have to provide additional by_array/ sort_order pairs like this:

= SORTBY(B6:B13, C6:C13, -1, D6:D13, 1)

Each by_array argument can only be one row or column.

Additional Note

You should always make sure that the array and all by_array references have compatible dimensions. If not, SORTBY will throw a #VALUE! error.

Formula examples

Related Tutorials

Related Functions

How to use the Excel FILTER function

Excel FILTER function

The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.

How to use the Excel SORT function

Excel SORT function

The Excel SORT function sorts the contents of a given range or array and returns a dynamic array result. Values can be sorted by one more multiple columns.

How to use the Excel UNIQUE function

Excel UNIQUE function

The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array. 

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email