Sort by a Custom Order With a Formula

To sort by a custom list with a formula, give each row its position in your list with MATCH, then sort by those positions with SORTBY. This is how you put priorities in the order High, Medium, Low, or departments in the order your manager wants, instead of A to Z. The result is a live, sorted copy of your data that updates when the data changes.

Why A to Z isn't enough#

The SORT function orders text alphabetically, and alphabetical order is often wrong for business data. Sorting priorities A to Z gives High, Low, Medium. Sorting month names gives Apr, Aug, Dec. What you want is your own order, called a custom order.

The trick is to turn each value into a number that matches the order you want, then sort by the numbers. All the examples use this task list, with the custom order typed in E2:E4:

The sort by custom list formula#

Use SORTBY with MATCH. Type this in an empty cell with room below and to the right, such as G2:

=SORTBY(A2:C7,MATCH(B2:B7,E2:E4,0))

The formula spills: it fills G2:I7 with the sorted table. A spill is a result that fills several cells from one formula. The High tasks come first (Fix login bug, Call supplier), then the Medium ones, then the Low ones.

SORTBY and MATCH-based sorting need Excel 2021 or Microsoft 365. The guide to SORT and SORTBY covers both functions in general.

How the formula works#

MATCH does the real work. With 0 as its last argument, it finds an exact match and returns its position in the list:

Because B2:B7 is a range, MATCH returns all six positions at once: {3;1;2;1;3;2}. SORTBY then sorts the rows of A2:C7 by those numbers, smallest first. Its syntax is:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

The by_array must have the same number of rows as the data. Here both have six. In Microsoft 365 you can also use XMATCH(B2:B7,E2:E4), which defaults to an exact match.

Sort rows with the same priority#

Add a second pair to SORTBY to decide the order inside each group. This sorts by priority, then by task name A to Z:

=SORTBY(A2:C7,MATCH(B2:B7,E2:E4,0),1,A2:A7,1)

The result:

The 1 after each by_array means ascending. Use -1 to reverse a level: MATCH(B2:B7,E2:E4,0),-1 puts Low first. For more on sorting by several levels, see how to sort by multiple columns.

Keep the list in cells or inside the formula#

You can type the custom list straight into the formula as an array constant, a list in curly brackets:

=SORTBY(A2:C7,MATCH(B2:B7,{"High","Medium","Low"},0))

It returns the same result. A list in cells is usually better, because anyone can change the order without editing the formula. It also works for long lists, such as departments or month names (Jan to Dec).

What if a value isn't in the list?#

If a row holds a value that isn't in your list, such as "Urgent" or a typo like "Hihg", MATCH returns #N/A for it, and that error can break the whole sort. Wrap MATCH in IFERROR to give unknown values a large number, so they go to the bottom:

=SORTBY(A2:C7,IFERROR(MATCH(B2:B7,E2:E4,0),99))

Trailing spaces cause the same problem, because "High " doesn't match "High". Clean the column with TRIM if rows land at the bottom unexpectedly.

Sort a filtered list in a custom order#

To sort only some rows, FILTER them first, and filter the sort key the same way so both have the same number of rows. Ana's tasks in priority order:

=SORTBY(FILTER(A2:C7,C2:C7="Ana"),MATCH(FILTER(B2:B7,C2:C7="Ana"),E2:E4,0))

The result is Call supplier (High), then Update budget (Low). FILTER also needs Excel 2021 or Microsoft 365. The article on combining SORT and FILTER shows more patterns, and a unique sorted list shows how to add UNIQUE.

Custom order in Excel 2019 and older#

Without SORTBY, use a helper column, an extra column that holds a value only for sorting:

1. In D2, type =MATCH(B2,$E$2:$E$4,0) and fill down (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep the list fixed.
2. Select the table and go to Data > Sort.
3. Sort by the helper column, smallest to largest, and click OK.

In Microsoft 365 you can also keep the helper column and use SORT: =SORT(A2:D7,4) sorts by the 4th column. The SORT function guide explains its arguments. If you'd rather not use formulas at all, a custom list in the Sort dialog does the same job; the guide to custom sort in Excel shows how.

Practise it#

Sorting formulas click once you've watched one spill. Practise the SORT function in a live spreadsheet to sort by one and two columns, then try the modern Excel functions lesson for XMATCH and SORTBY. Each answer is checked instantly. Start with the High, Medium, Low exercise on this page.