How to Do Multi-Criteria Ranking in Excel using SORTBY and XMATCH
Question details
The user needs to rank a list of records based on multiple specific criteria (e.g., Score, Back 6, Back 3, and Back 1) to accurately resolve tied scores.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating advanced dynamic rankings and breaking ties without manually rearranging the dataset.
- Observed behavior
- The goal is to automatically calculate ranks or sort data into a new table by applying multiple prioritized tie-breaking conditions.
Ensure you are using a version of Excel or WPS Office that supports modern dynamic array functions, as SORTBY, XMATCH, and LET are required for these solutions.
Calculate Ranks While Preserving Original Order
Use this method if you want to display the calculated rank number in a column next to your original data without changing the row order.
This approach uses the LET function to first generate a sorted list of names in the background using your multiple criteria. Then, it uses XMATCH to find the position (rank) of each original name in that sorted list.
Click on the top empty cell in your 'Rank' column next to your first record.
Enter the following formula, adjusting the named ranges (name, Score, back_6, back_3, back_1) to match your data: =LET(rank,SORTBY(name,Score,-1,back_6,-1,back_3,-1,back_1,-1),XMATCH(name,rank))
Press Enter. The dynamic array formula will automatically spill down the column, assigning the correct multi-criteria rank to each row.

Sort the Entire Data Table Dynamically
Use this method when you want to output a completely new, rearranged table based on your tie-breaking criteria.
Perform Advanced Multi-Criteria Ranking in WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like SORTBY, XMATCH, and LET, making complex data analysis and multi-criteria tie-breaking incredibly simple.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the records you need to rank.
- 2. Enter the ranking formula: Click the cell where you want the rank to appear and type: =LET(rank,SORTBY(name,Score,-1,back_6,-1,back_3,-1,back_1,-1),XMATCH(name,rank))
- 3. View your dynamic results: Press Enter to see the ranks instantly calculated and spilled down the column, perfectly resolving any tied scores.

Frequently Asked Questions
What does the -1 signify in the SORTBY formula?
The -1 argument tells the SORTBY function to sort the preceding array in descending order. This means higher numbers (like top scores) are ranked first. Changing it to 1 would sort the data in ascending order.
Why am I getting a #SPILL! error when using these formulas?
A #SPILL! error happens when the dynamic array function needs to output results into multiple cells, but one or more of those target cells already contain data. To fix this, simply clear the data in the cells blocking the output path.
Can I use SORTBY, LET, and XMATCH in older versions of Excel?
No, these are modern dynamic array functions introduced in Microsoft 365 and Excel 2021. If you are using an older version (like Excel 2016 or 2019), these functions will not work. However, modern alternatives like WPS Office Spreadsheet fully support them.




