SORTBY function
SORTBY sorts one range by the values in another. Use it to list names by a score you don't want to show, or to sort by several columns at once, such as team first and then score. The sort keys don't have to be part of what you return, only the same height.
- Available in
- Excel 2021, 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- A sorted copy of array. It spills.
Syntax
=SORTBY(array, by_array1, [sort_order1], ...)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range to sort and return. |
by_array1 | Required | The range to sort by. It must have the same number of rows as array and doesn't have to be part of it. |
sort_order1 | Optional | 1 sorts ascending (the default), -1 descending. Add more by_array and sort_order pairs to break ties. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =SORTBY(A2:A6,D2:D6,-1) | Chloespills 5 × 1 | Names by score, highest first, without showing the scores |
| H2 | =SORTBY(A2:B6,B2:B6,1,D2:D6,-1) | Chloespills 5 × 2 | Team A to Z, then score highest first within each team |
| J2 | =SORTBY(A2:A6,C2:C6) | Elispills 5 × 1 | Longest-serving first: sorted by hire date |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Name | Team | Hired | Score | By score | By team, then score | By hire date | |||
| Ava | Sales | 3/15/2021 | 88 | Chloe | Chloe | Sales | Eli | ||
| Ben | Support | 7/1/2019 | 92 | Ben | Ava | Sales | Ben | ||
| Chloe | Sales | 1/9/2023 | 95 | Ava | Eli | Sales | Dev | ||
| Dev | Support | 11/23/2020 | 79 | Eli | Ben | Support | Ava | ||
| Eli | Sales | 5/14/2018 | 84 | Dev | Dev | Support | Chloe |
Practice SORTBY
Graded instantly, then re-checked on changed data.
Leaderboard names
F2, list the reps in A2:A8 sorted by revenue in C2:C8, highest first. Show only the names.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | by_array is a different height from array, often because one is a whole column like D:D. Make both ranges cover the same rows. |
| Ties come out in the wrong order | Rows with equal values keep their original order. Add a second by_array and sort_order pair, such as hire date, to decide ties. |
| #SPILL! | Something is in the cells the result needs. Clear everything below and to the right of the formula, or move it to an empty area. |
Tips
- Sort by a calculation without a helper column: =SORTBY(A2:A6,C2:C6*D2:D6,-1) sorts by price times quantity.
- by_array can be a different column from the one you show, so private data like salaries can drive the order without appearing.
Questions
What is the difference between SORT and SORTBY?
SORT sorts by a column inside the range it returns. SORTBY sorts by any range of the same height, so you can sort names by a score you don't display, and it takes several sort keys.
How do I sort by multiple columns with a formula?
Add pairs of by_array and sort_order: =SORTBY(A2:B6,B2:B6,1,D2:D6,-1) sorts by team A to Z, then by score from highest to lowest.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.