SORTBY - Rank values using aligned external keys
Rank a name vector by ticket counts that are not in the returned vector, then add an explicit Excel tie-breaker when needed.
By the end, you can
- Separate returned data from its sort key.
- Keep key arrays aligned with records.
- Use multiple Excel keys to make ties explicit.
Before you start: Understand SORT and aligned source records.
In this lesson
The sort key need not appear in the result
SORTBY orders an array using separately supplied key arrays. A support report can rank employee names by closed-ticket counts without returning the count column in that list. It can also order whole records or apply a sequence of tie-breakers. A ticket-count rank is one measure, not a complete employee-performance assessment.
Syntax and key/order pairs
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)Each key must align to the returned records. The normal omitted order is ascending.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | Values or records to order. | A2:A5 |
by_array1Required | Aligned key vector that determines the primary order. | B2:B5 |
sort_order1Optional | 1 ascending or -1 descending; default 1. | -1 |
additional key/order pairsOptional | Secondary keys resolving ties in earlier keys. | A2:A5, 1 |
A support team's name ranking
Meera| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Closed tickets | Quality score | Ranked employee | |
| 2 | Anika | 32 | 94 | Meera | |
| 3 | Dev | 18 | 97 | Anika | |
| 4 | Meera | 40 | 91 | Rahul | |
| 5 | Rahul | 26 | 95 | Dev |
Change the key or the returned columns
=SORTBY(A2:C5,C2:C5,-1)This answers a different question and carries employee, ticket count, and quality together.
=SORTBY(A2:A5,B2:B5)The default order is ascending, beginning with the smallest count.
A secondary key defines a tie policy
Anika| A | B | |
|---|---|---|
| 1 | Employee | Closed tickets |
| 2 | Anika | 32 |
| 3 | Dev | 26 |
| 4 | Meera | 40 |
| 5 | Rahul | 32 |
=SORTBY(A2:B5,B2:B5,-1,A2:A5,1)First use descending tickets, then ascending names for equal counts. The tie order is Anika before Rahul.
Limits, mistakes, and Practice scope
- Key vectors must have compatible dimensions and record alignment; a shifted key can rank a name by another person's count.
- A blocked destination gives #SPILL!. Keep the anchor outside an Excel Table and allow enough cells for all returned records.
- The key can be outside the returned columns, but it must describe those same records. Sort orders should be 1 or -1.
- SORTBY is available in Microsoft 365 and Excel 2021/2024. A key sequence describes tie rules explicitly rather than relying on incidental source order.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.