To sort one Excel list in the order specified by another, use SORTBY with XMATCH in Microsoft 365, or add a helper column that assigns each item its position in the priority list and sort by that column. For a short, fixed sequence, Excel’s Custom List sort can also apply a manually defined order.
What it means to sort by another list
A normal A-to-Z sort arranges values according to their text or number order. Sorting by another list instead uses that second list as a sequence of priorities. For example, a department list might need to appear in the order Support, Sales, Finance, Operations, even though that is neither alphabetical nor the order in the source data.
The list that contains the values to rearrange is the source list. The other list is the order, or priority, list. Excel needs a way to match each source value to its position in the priority list. It can then place values with smaller positions before values with larger positions.
This is useful for custom business sequences, project stages, product rankings, survey response categories, and names arranged to match a separate roster. It is not the same as sorting by a second column in the same table: the order list can be located elsewhere in the workbook and can be maintained separately.
Use SORTBY and XMATCH in Microsoft 365
For an automatically updating result, place the sorted output in an empty cell outside the source and order ranges. With source values in A2:A7 and the desired order in D2:D6, enter this formula in F2:
=SORTBY(A2:A7,IFNA(XMATCH(A2:A7,D2:D6,0),ROWS(D2:D6)+1),1)
In this example, A2:A7 contains Mira, Omar, Priya, Luis, Hana, Zoe, while D2:D6 contains Priya, Mira, Zoe, Omar, Hana. The formula returns Priya, Mira, Zoe, Omar, Hana, Luis. Luis is not in the order list, so IFNA gives it a rank after the five listed names. The formula sorts the source values; it does not change their original cells.
XMATCH finds each source value’s position in the order list. The zero requests an exact match. SORTBY then orders the source values by those positions, ascending. IFNA supplies a fallback rank for values with no match, and ROWS counts the entries in the priority range so that the fallback comes after the listed ranks.
These functions are available in Microsoft 365 and Excel 2021 or later. The result is a dynamic array: it spills into the cells below F2 and recalculates when its inputs change. Keep the spill area empty or Excel will show a #SPILL! error. If the source or priority list grows, edit the formula ranges or convert the data into Excel tables and use structured references.
For an entire table, sort the table rows by the key column rather than sorting one column independently. If the key is in A2:A7 and related details are in B2:C7, a corresponding formula placed in an empty area is:
=SORTBY(A2:C7,IFNA(XMATCH(A2:A7,D2:D6,0),ROWS(D2:D6)+1),1)
This returns the rows from columns A through C in the requested order. The key range used by XMATCH must have the same number of rows as the table being sorted. Sorting only names while leaving their associated dates, amounts, or other fields behind would detach the records.
Use a helper column when you want to sort in place
A helper column works in more Excel versions and is often the clearest choice when the existing table itself must be reordered. With source values in A2:A7 and the priority list in D2:D6, enter this formula in B2 and fill it down through B7:
=IFERROR(MATCH(A2,$D$2:$D$6,0),ROWS($D$2:$D$6)+1)
The matching source values receive ranks from 1 to 5. Luis receives rank 6 because it has no exact match. Select the entire table, including the helper column, and use Data > Sort. Set Sort by to the helper column, Sort On to Cell Values, and Order to Smallest to Largest. If Excel asks, choose to expand the selection so each row stays together. You can remove the helper column after sorting if you no longer need it.
MATCH with a final argument of zero looks for an exact match. The dollar signs keep the priority range fixed as the formula is filled down, while A2 changes to A3, A4, and so on. If source values are missing from the order list, the fallback rank places them after the listed items. If several source rows have the same value, they receive the same rank; Excel may leave their relative order as it was, but do not rely on a particular tie order if it matters. Add a second sort level, such as date or original row number, to define ties.
For a table with multiple columns, calculate the helper rank from its key column, but sort the whole table. Do not select just the key and rank columns if other fields belong to the same records. Sorting the complete rows keeps each value attached to its related data.
Sort with a custom list from the ribbon
For a small sequence that rarely changes, you can define a Custom List and use Excel’s built-in sort. On Windows, open File > Options > Advanced, scroll to the General section, and choose Edit Custom Lists. Enter the desired order, with each item on a separate line, and add the list. Then select the data and choose Data > Sort. In the Order menu for the relevant column, choose Custom List and select the sequence.
This method is convenient for familiar fixed orders such as weekdays or a small set of workflow stages. It avoids adding a formula column, but the custom sequence is a setting in Excel rather than a visible ranking beside the data. A formula-based or helper-column approach is easier to inspect when the priority order is maintained in worksheet cells, changes often, or needs to be shared as part of the workbook.
Menu labels and the location for managing custom lists can vary by Excel platform. Excel for the web has fewer desktop options, so if Custom List is unavailable in your version, use a formula or helper column. In Excel for Mac, use Excel > Settings or Preferences to locate the custom-list settings; exact labels can vary by release. Regardless of platform, check the sorted result for values not included in the custom sequence rather than assuming their placement.
Choose the method that fits your workflow
- Choose SORTBY and XMATCH when you want a separate, formula-driven result that updates as the source or priority list changes. This is usually the most convenient option in Microsoft 365 or Excel 2021 and later.
- Choose a helper column when you need to reorder the original table, want to see each item’s rank, or use a version without the newer dynamic-array functions.
- Choose a Custom List for a short, stable sequence that you want to apply through the Sort dialog without storing ranks in worksheet cells.
Consider who will use the workbook. A formula can be transparent because the priority list and matching logic are visible on the sheet, but a dynamic-array formula may not work in older Excel versions. A helper column is broadly understandable and makes unmatched entries easy to identify, although it takes an extra sort step. A custom list is quick for repeated manual sorts on one computer, but it is less self-documenting if another person needs to inspect or reproduce the order.
Common matching problems and how to fix them
Items unexpectedly appear at the end. Check that every source value appears in the priority list. A trailing space, different punctuation, a spelling variation, or a different data type can prevent a match. For example, a number stored as text may not behave like the same number stored numerically. Compare the cells and standardize the data before changing the formula.
Repeated values do not get separate priority positions. A lookup finds the first matching occurrence. If the priority list contains the same label more than once, duplicate labels do not express distinct positions in a useful way: the same source value matches the first occurrence. Make the priority labels unique if each entry needs an independent rank, or add another key such as an ID.
Blanks or extra rows affect the result. Blank cells in a priority range can match blank source cells, and oversized ranges can include unintended values. Use ranges that cover the actual lists, and decide explicitly whether blank source rows should be excluded. For a clean result, remove empty rows or handle them with a separate filtering step.
The formula returns an error or will not spill. Confirm that the function names are supported by your Excel version, that the ranges are valid, and that the output cells are clear. A blocked dynamic-array output produces #SPILL!. If you see #N/A, check for unmatched values; the example formula uses IFNA to give those values a final rank instead.
Only one column moved. This is a data-integrity problem, not a sorting preference. Always sort the full range or table so related information remains on the same row. If Excel prompts you to expand the selection, choose that option when the adjacent columns belong to the records.
Keep the order list maintainable
Put the priority list in one clearly labeled place, separate from the output, and use one spelling for each category. If you update the order frequently, keeping it in worksheet cells makes the ranking easy to review and change. You can also use a table for the list so adding entries is easier to manage, although the formula’s reference must include the new entries or use a suitable structured reference.
For a process that has categories not yet added to the priority list, decide whether those entries should appear at the end, be excluded, or trigger a visible warning. The example formula places unmatched items last, which is practical for many lists, but it can conceal a missing priority assignment. A helper column makes those fallback ranks visible; you can also inspect the unmatched values before sharing the sorted output.
Keep an untouched copy of the original order when the sequence is important for audit or review. A formula result leaves the source in place, while an in-place sort changes the row order. That distinction matters when the original order represents timing, an import sequence, or another meaningful arrangement.
Frequently Asked Questions
Can Excel sort a list to match another column?
Yes. Use SORTBY with XMATCH for a formula result, or use a helper column with MATCH and sort the complete table by the resulting ranks.
How do I put items missing from the order list last?
Wrap the match lookup in IFNA and assign unmatched values a rank larger than the ranks in the priority list. The formula above uses the priority-list row count plus one for that fallback.
Can I sort an entire table by a separate list?
Yes. Use the key column to calculate each row’s priority, then sort the full table by that rank. With SORTBY, supply the whole table as the first argument and a matching-height key range for the lookup.
Will this work in older Excel versions?
The dynamic-array example needs SORTBY and XMATCH, available in Microsoft 365 and Excel 2021 or later. The helper-column method uses MATCH, IFERROR, and ROWS, which are available in older versions too.
Can Excel sort by a custom order without a formula?
Yes. Define a Custom List in Excel’s settings, then choose it as the sort order in the Data > Sort dialog. This is best for a fixed sequence; use a worksheet-based priority list when the order changes or needs to travel clearly with the workbook.
Related
- Excel Shortcut to Select Cells to the End of a Column
- Excel Shortcut to Select the Active Cell’s Entire Column
- Excel Shortcut to Select to the Bottom of a Column
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Round Up to the Nearest Thousand in ExcelTo round a number in cell A2 up to the next thousand in Excel, use =ROUNDUP(A2,-3). The negative…
- Excel Filter All Rows Containing Text in a ColumnTo filter Excel rows that contain a word or phrase, open the filter arrow in the relevant column…
- Excel Tables: Do New Rows Keep Formulas?Yes. In Excel for Microsoft 365 on Windows, a table normally carries a calculated-column formula into new rows,…
- Excel MATCH Function: Find a Value’s PositionExcel’s MATCH function finds a value in a range and returns its relative position, not the value itself.…
- Volume of a Right Cylinder: Formula, Steps and ExamplesThe volume of a right cylinder is V = πr²h: multiply pi by the radius squared, then by…
- Excel COUNTIF for Dates Between a RangeTo count dates between two boundaries in Excel, use COUNTIFS, which applies both a start-date and an end-date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.