Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sort by a custom list order in Excel and Google Sheets
Excel & Google Sheets
=SORT(A2:B4, MATCH(A2:A4, $E$2:$E$4, 0))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Create a column with your custom list order in a separate range.
- 2Use the MATCH function to assign a rank to each item in your data based on the custom list.
- 3Apply the SORT function using the ranks from the MATCH function as the key.
Tips
- Ensure your custom list contains all possible values found in your data to avoid errors.
- Adjust cell references in formulas to match the actual location of your data and custom list.
- In both Excel and Google Sheets, sorting is case-sensitive; consider standardizing text case if necessary.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if my data has missing values?
Missing values will be sorted at the end of the list when using the SORT function.
Can I sort by multiple columns using this method?
Yes, you can extend the SORT function to include multiple keys by adding additional arguments.
How do I handle duplicates in my custom list?
Duplicates in the custom list can lead to unpredictable sorting; ensure each entry is unique.