SORTBY function
SORTBY sorts one range by the values in a separate key range, even if the key column is not in the output, and supports multiple keys. The key range must be the same height as the array.
Example
Read it as a sentence: sort the Item names in B2:B7 by their matching Qty in D2:D7, from largest to smallest, and show the first one, which gives Candle.
Syntax
SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
Arguments in [square brackets] are optional.
-
array(required) - Range or array to return sorted.
-
by_array1(required) - The range or array to sort by.
-
sort_order1(optional) - 1 ascending (default), -1 descending.
-
by_array2(optional) - A second sort key.
-
sort_order2(optional) - Order for the second key.
Returns: The array sorted by a separate key array.
One SORTBY formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SORTBY({"a","b","c"},{3,1,2}) | b | Horizontal |
=SORTBY(Items!B2:B7,Items!D2:D7,-1) | Candle | By units descending |
=SORTBY(Items!B2:B7,Items!C2:C7,1,Items!D2:D7,-1) | Candle | Two keys |
=SORTBY(Items!B2:B7,Items!D2:D7) | Cigar | Default ascending |
=SORTBY(Items!B2:B7,Items!D2:D4) | #VALUE! | by_array wrong size |
=SORTBY(Items!B2:B7,Items!D2:D7,2) | #VALUE! | Bad order |
The Items sheet these formulas read
Watch out for
- by_array must be the same height as array.
- Keys can be calculations, such as sorting by LEN.
Where it works
- Excel
- SORTBY arrived in Excel 2021. Older versions do not have it.
- Google Sheets
- Done with a different function: `SORT`. In Sheets, SORT(range, sort_column, is_ascending, ...) covers this, with key columns given as ranges.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .