Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .