How to sort by a custom order with a formula in Excel
Excel formula
=SORTBY(A2:C10,XMATCH(B2:B10,F2:F4))Sort by each value's position in your own list: =SORTBY(A2:C10,XMATCH(B2:B10,F2:F4)) puts High first, then Medium, then Low.
- Result on the example
- a 9-row list that spills from H2 in
H2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in H2. Change any input and the result updates, or edit the formula to experiment.
How it works
XMATCH returns where each ticket's priority sits in the list F2:F4: 1 for High, 2 for Medium, 3 for Low. SORTBY sorts the table by those numbers, smallest first. Tickets with the same priority keep their original order, which here is oldest first.
Reorder the list in F2:F4 and the table follows. Google Sheets' SORT takes the MATCH positions as its sort column.
=SORTBY(A2:C10,XMATCH(B2:B10,F2:F4))Result{9 rows × 3 cols} "T-302", "High", 46145, "T-305", …
A2:C109 rows × 3 cols: "T-301", "Low", 5/2/2026, "T-302", …A range: 9 rows × 3 columns.
B2:B109 rows × 1 col: "Low", "High", "Medium", "Low", …A range: 9 cells in one column.
F2:F43 rows × 1 col: "High", "Medium", "Low"A range: 3 cells in one column.
XMATCH(B2:B10,F2:F4){9 rows × 1 col} 3, 1, 2, 3, …XMATCH: Modern MATCH: exact by default.- lookup_value
B2:B109 rows × 1 col: "Low", "High", "Medium", "Low", … - lookup_array
F2:F43 rows × 1 col: "High", "Medium", "Low"
- lookup_value
SORTBY(A2:C10,XMATCH(B2:B10,F2:F4)){9 rows × 3 cols} "T-302", "High", 46145, "T-305", …SORTBY: Sorts a range by other ranges. Spills.- array
A2:C109 rows × 3 cols: "T-301", "Low", 5/2/2026, "T-302", … - by_array1
XMATCH(B2:B10,F2:F4){9 rows × 1 col} 3, 1, 2, 3, …
- array
In Google Sheets
Google Sheets needs a different formula: =SORT(A2:C10,MATCH(B2:B10,F2:F4,0),TRUE). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORTBY(A2:C10,XMATCH(B2:B10,F2:F4),1,C2:C10,-1)Excel only | spills 9 × 3Different result | Add a second key to sort within each priority, here newest first. Newest ticket first inside each priority. |
| =SORTBY(A2:C10,XMATCH(B2:B10,{"High","Medium","Low"}))Excel only | spills 9 × 3 | Type the order into the formula when there's nowhere to keep a list. |
Common mistakes
- A value missing from the order list makes XMATCH return #N/A, and SORTBY fails. Add every value to the list, or wrap XMATCH in IFNA with a large number to send unknowns to the bottom.
- Sorting the priority column alphabetically gives High, Low, Medium, which is why a plain SORT doesn't work here.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sort by a custom order with a formula
H2, sort the tickets in A2:C10 by priority in the order listed in F2:F4: High, then Medium, then Low.Questions
How do I sort weekdays or month names in calendar order?
Use the same pattern with the names in order: =SORTBY(A2:C10,XMATCH(B2:B10,{"Mon","Tue","Wed","Thu","Fri"})).
Can I do this without a formula?
Yes: Data > Sort > Order > Custom List. The formula version updates on its own and leaves the source untouched.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.