The SORTBY function
Sorts a range by the values in another range, which need not be part of the result. Add more by-range and order pairs to break ties.
Syntax
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)- array
- The range to sort and return.
- by_array1
- The range to sort by, one value per row of array.
- sort_order1 (optional)
- 1 or left out for smallest to largest, -1 for largest to smallest.
- by_array2, sort_order2 (optional)
- More ranges and orders, used when the earlier ones tie.
Examples
E1
=SORTBY(1A2:A5,2C2:C5,3-1)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Team | Sales | Ben | |
| 2 | Maya | East | 320 | Maya | |
| 3 | Ben | West | 450 | Ava | |
| 4 | Zoe | East | 180 | Zoe | |
| 5 | Ava | West | 275 |
In plain words: Sort 1the names in A2:A5 by 2the sales in C2:C5, 3largest first.
E1=SORTBY(A2:C5,B2:B5,1,C2:C5,-1)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Sales | Maya | East | 320 | |
| 2 | Maya | East | 320 | Zoe | East | 180 | |
| 3 | Ben | West | 450 | Ben | West | 450 | |
| 4 | Zoe | East | 180 | Ava | West | 275 | |
| 5 | Ava | West | 275 |
Questions
What is the difference between SORT and SORTBY?
SORT sorts by a column number inside the range. SORTBY sorts by a separate range, so the column you sort by does not have to be in the result, and several sort levels are easy to add.
How do I sort by two columns?
Give SORTBY two pairs, as in =SORTBY(A2:C5,B2:B5,1,C2:C5,-1). It sorts by team first and, inside each team, by sales from highest to lowest.