To sort Excel rows into the order of a second list, use SORTBY with XMATCH, or add a helper column that assigns each row a position in the order list and sort by that column. For a one-time sort of familiar labels, Excel’s Custom List option can also set the order.
What it means to sort by another list
A normal alphabetical sort arranges text from A to Z, and a numeric sort arranges values from smallest to largest. Sorting by another list means using a separate sequence as the ranking. For example, you might have sales records for six fruit types but want them displayed in the order Mango, Apple, Banana, Pear, Orange, Grape—not alphabetically.
The sequence list is sometimes called a lookup list, priority list, or sort order. Excel needs to match each value in the data to its position in that sequence. It can then use those positions to reorder the entire row, keeping each label attached to its related figures, dates, or other fields.
This is different from sorting one column by its own values. The important rule is to sort complete records, not just the label column; otherwise, the figures and other details can become associated with the wrong item.
Use SORTBY and XMATCH for a live sorted result
In Microsoft 365 for Windows, enter a dynamic-array formula in an empty area when you want a sorted copy that updates as the source data changes. These functions are also available in Excel 2021. Suppose the fruit names are in A2:A7, sales figures are in B2:B7, and the requested order is in D2:D7. With those ranges, enter this formula in an empty cell:
=SORTBY(A2:B7,XMATCH(A2:A7,D2:D7),1)
For the example order Mango, Apple, Banana, Pear, Orange, Grape, the result is six rows in that order, with the matching sales figures carried along. If Pear has sales of 12, for instance, Pear remains beside 12 in the output. XMATCH returns each fruit’s position in D2:D7, and SORTBY arranges the two-column data by those positions in ascending order.
The formula returns results into adjacent cells, so leave enough empty space for the output. It does not rearrange the original data. If a source value is not present in the order list, XMATCH returns #N/A, which prevents this basic formula from producing a complete result. Fix the missing match or use a method that handles unmatched values before relying on the output.
When two source rows have the same label, they receive the same position. If their order relative to each other matters, add a second sort key, such as a date or original row number, to SORTBY. The formula shown uses exact matching by default; XMATCH does not treat a partly matching label as the same item.
Sort the original data with a helper column
A helper column is often the clearest choice when you need to rearrange the source table itself, or when you want a process that works in older Excel versions. It makes the ranking visible and gives you a chance to inspect the matches before sorting.
- Put the items in the desired order in a separate range, such as D2:D7.
- Add a heading such as Sort position to an unused column beside the data.
- With data labels in A2:A7 and the order list in D2:D7, enter this in C2:
=IFERROR(MATCH(A2,$D$2:$D$7,0),ROWS($D$2:$D$7)+ROW(A2))
- Fill the formula down through C7. MATCH with a final argument of 0 looks for an exact match and returns its position in the order list.
- Select the entire data range, including the helper column. Choose Data > Sort, sort by Sort position, and choose Smallest to Largest.
- After checking the result, you can remove the helper column if it is no longer needed.
For the six-item example, known fruits receive ranks 1 through 6. The IFERROR part gives an unmatched item a larger rank than the listed items, so it sorts after them. Its value is based on its worksheet row, which also gives different unmatched rows distinct positions. If you would rather have Excel stop and make you resolve missing items, use =MATCH(A2,$D$2:$D$7,0) instead; an unmatched value will then show #N/A.
On Windows, use Data > Sort rather than sorting only the helper column. If Excel asks whether to expand the selection, choose Expand the selection so the whole records move together. On Mac, the same basic Data tab sort workflow is available, though some dialog details can differ.
Use a Custom List for a reusable fixed order
Excel’s Custom List feature is useful when the same short sequence is used repeatedly—for example, business stages such as New, In progress, Waiting, Complete, or the days of the week. It lets the Sort dialog order matching cell values according to a defined sequence rather than alphabetically.
On Excel for Microsoft 365 for Windows, select a cell in the data and choose Data > Sort. Select the column to sort by, open the Order menu, and choose Custom List. Choose an existing list or create or import the desired sequence in the Custom Lists dialog, then confirm the sort. When importing, select the cells that contain the sequence and use the dialog’s import option.
A custom list is not the same as pointing the sort directly at any arbitrary range each time. It is a saved sequence that can be reused. That makes it convenient for stable categories but less convenient when the order changes often or is different for each workbook. Custom List availability and dialog layout can vary by platform; if the option is not convenient in your Excel edition, use a helper column instead.
Use Power Query for repeatable imports
Power Query can help when the data is refreshed regularly from a file, database, or other source and needs the same custom ordering each time. The general approach is to load the records and the order list as queries, add an index to the order list, merge that index into the records by the matching label, then sort by the index.
In Excel for Windows, select a source range and choose Data > From Table/Range to open it in Power Query. Load the order list as a separate query as well. In the order-list query, use Add Column > Index Column to create positions. Then select the records query and use Home > Merge Queries, matching the label column to the corresponding order-list column. Expand the merged result to include the index, sort the records by that index, and choose Home > Close & Load when the query is ready.
Check the merge results before loading. A blank index indicates that a record did not match the order list; decide whether to add the missing label, exclude the record, or assign it a deliberate fallback position. Power Query is more setup than a formula for a small one-off task, but it can save repeated manual sorting when the same transformation is part of a refresh workflow.
Choose the method that fits the job
- Use SORTBY and XMATCH for a separate, automatically updating sorted view in Excel 2021 or Microsoft 365.
- Use a helper column when you want to inspect ranks, sort the original records, or support older Excel versions.
- Use a Custom List for a familiar sequence that you reuse in manual sorts.
- Use Power Query when the sorting is one step in a repeatable data-import or refresh process.
For a simple task in a workbook shared with people using different Excel versions, the helper-column approach is usually easiest to explain. Dynamic-array formulas can be elegant, but an older version may not recognize newer functions. Custom Lists are convenient but depend on the saved list being available in the relevant Excel environment.
Common problems and checks
- A label is missing from the result: Check for unmatched values in the order list. Spelling differences, extra spaces, and inconsistent naming can prevent exact matches. A value such as North may not match North Region.
- Rows have become mixed up: The sort may have been applied to only one column. Undo the sort if possible, then select the full table and sort again. Sorting a table by a column normally keeps its rows together; for a plain range, pay attention to Excel’s Expand the selection prompt.
- The output shows #N/A: With XMATCH or MATCH, this normally means a source label was not found in the order range. Check that the intended lookup range includes every order item and that the text is consistent.
- Some items appear in an unexpected order: Confirm that the order list itself is in the intended sequence. Also check for duplicate entries in that list: a lookup returns the first matching position, so duplicate labels do not define two different ranks.
- The formula does not spill: A dynamic-array formula needs an unobstructed output area. Clear cells in the way of the expected results, and make sure the formula is not being entered inside an Excel Table column that cannot accept the full spill range.
Excel text matching in these examples is not case-sensitive, so Apple and APPLE are treated as the same label. If capitalization is being used to distinguish items, these methods need a different matching design. For most lists, it is better to make the labels genuinely distinct rather than rely on capitalization alone.
Frequently Asked Questions
Can I sort one Excel list to match the exact order of another?
Yes. Use a helper rank based on the second list, then sort the full data range by that rank. In Excel 2021 or Microsoft 365, SORTBY with XMATCH can instead return a sorted copy.
How do I sort by a custom order instead of A to Z?
Use Data > Sort and choose a Custom List for a sequence you reuse, or create a helper column that assigns each item its position in the desired order. A helper column is more flexible when the order changes.
Can Excel sort rows using a list on another sheet?
Yes. The order range can be on another worksheet. In a formula or helper-column formula, reference that sheet in the lookup range, such as Sheet2!$D$2:$D$7. Keep the references pointed at the cells that contain the full sequence.
What happens if an item is not in the other list?
A basic XMATCH or MATCH lookup returns #N/A. You can add the missing item to the order list, or use an error-handling formula to place unmatched rows after the listed items. Check those unmatched items rather than assuming their position is correct.
Does sorting the formula result change the original list?
No. SORTBY creates a separate spilled result and leaves the source range in place. A helper-column sort or a Custom List sort rearranges the selected source records, so keep a copy if you need to preserve their original order.
Related
- Excel Paste Keyboard Shortcuts: Copy, Paste Special & More
- Excel Packing List Template: What to Include and How to Use
- Excel Ordinal Number Format: Show 1st, 2nd, and 3rd
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Outlook Folder Rules: Create Rules to Sort EmailCreate an Outlook rule by choosing a condition, such as a sender or subject phrase, and an action…
- Excel Formula to Highlight Every Other Row AutomaticallyTo highlight every other row in Excel, select the range, open Home > Conditional Formatting > New Rule,…
- Calculating Hours Worked in Excel: Formulas & TipsTo calculate hours worked in Excel, put a shift’s start time in A2 and end time in B2,…
- Excel Rank Formula Without Duplicate Ranks: ExamplesTo give tied values different, consecutive ranks in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 beside the first value and fill down.…
- Excel Sum Between Dates: SUMIFS Formulas and ExamplesIf dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in E2…
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.