Quick answer
The SORTBY formula sorts one range based on the values in another range.
=SORTBY(A2:D100,D2:D100,-1)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=SORTBY(array, by_array1, [sort_order1], ...)When to use it
Lookup formulas connect tables together. Dynamic array formulas go further by returning lists, tables, and spill ranges that update automatically when the source data changes.
- Return a price from a product table
- Find an employee name from an ID
- Filter open orders into a report
- Create sorted or unique lists
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Lookup value such as SKU, ID, or name |
| F:G | Reference table with the match column and return column |
| B | Returned result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Sort table by a score column
=SORTBY(A2:D100,D2:D100,-1)Sort the full table by a separate score or date column.
Sort names by last name
=SORTBY(A2:B100,B2:B100,1)Keep both columns together while sorting by last name.
Sort by two columns
=SORTBY(A2:D100,B2:B100,1,D2:D100,-1)Sort by region, then by amount descending.
Sort filtered rows
=SORTBY(FILTER(A2:D100,D2:D100="Open"),FILTER(B2:B100,D2:D100="Open"),1)Sort only the rows that match your filter.
Sort using custom helper values
=SORTBY(A2:D100,IF(C2:C100="High",1,2),1)Use helper logic when normal alphabetical order is not enough.
Top records by sorted output
=TAKE(SORTBY(A2:D100,D2:D100,-1),10)Return the first 10 rows after sorting.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- The spill area is blocked by existing values below or to the right of the formula cell.
- The source ranges do not have compatible row or column sizes.
- The formula returns an empty result because no rows match the condition.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the SORTBY formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.