SORTBY

What it does

SORTBY sorts a range by a different range of the same height, which doesn't have to be part of the result. You can add more pairs to break ties.

Syntax

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

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;

Arguments

array

The range to return, sorted.

by_array

The values to sort by, one per row of the array.

sort_order

1 or left out for smallest first; -1 for largest first.

Example

List the runners from fastest to slowest.

The formula in D2:

=SORTBY(A2:A4;B2:B4)

The result spills into the cells below: Ben; Kai; Ana

The names are sorted by their times, smallest first: Ben 59.8, Kai 61.1, Ana 62.4. Only the names are returned.

Common mistakes

• Giving a sort range of a different height, which gives #VALUE!.
• Using -1 for times. The fastest time is the smallest, so leave the order as 1.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• SORT and SORTBYModule 11 · Dynamic arrays

More from this module

• UNIQUE
• FILTRE
• TRIER
• SEQUENCE

All functions