Lookup & Dynamic Arrays
Sort a Table by Two Columns (Region Ascending, Revenue Descending) with SORTBY
Order records simultaneously by a primary text category ascending (+1) and a secondary numeric KPI descending (-1) using multi-key SORTBY.
Combine alphabetical grouping with largest-first KPI ranking in one formula
Sales leaderboards typically group reps alphabetically by Region (EMEA first, then NA) while ranking reps within each region from highest ARR to lowest ARR. Single-key SORT cannot apply +1 to one column and -1 to another unless nested or supplied array constants.
SORTBY(array, by_array1, sort_order1, by_array2, sort_order2) accepts unlimited (vector, direction) pairs directly: passing B2:B6, 1, C2:C6, -1 groups regions A-to-Z and breaks every regional tie by highest ARR.
| Rank Order | Account Rep | Region (+1 Asc) | Booked ARR (-1 Desc) |
|---|---|---|---|
| 1 | Zoe | EMEA | $160,000 |
| 2 | Mia | EMEA | $120,000 |
| 3 | Liam | EMEA | $95,000 |
| 4 | Noah | NA | $185,000 |
| 5 | Ava | NA | $140,000 |
Ordered Rep Leaderboard Sequence (Region Asc, ARR Desc)
Sorts A2:A6 by Region B2:B6 ascending (1) and ARR C2:C6 descending (-1).
=TEXTJOIN(", ", TRUE, SORTBY(A2:A6, B2:B6, 1, C2:C6, -1))Top-Ranked Rep Inside First Alphabetical Region
Extracts row 1 of the two-key sorted array.
=INDEX(SORTBY(A2:A6, B2:B6, 1, C2:C6, -1), 1)Try the example
Use the original values or change them to test your understanding.