Function Problems

How to Do Multi-Criteria Ranking in Excel using SORTBY and XMATCH

Phi Hung VoPhi Hung Vo Nov 10, 2026 872 views

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.

How to Do Multi-Criteria Ranking in Excel using SORTBY and XMATCH
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the top empty cell in your 'Rank' column next to your first record.

2
Input the LET and SORTBY formula

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))

3
Apply the ranking

Press Enter. The dynamic array formula will automatically spill down the column, assigning the correct multi-criteria rank to each row.

Calculate Ranks While Preserving Original Order
Tip: The '-1' in the SORTBY parameters dictates a descending sort order, ensuring the highest scores receive the top ranks.
Powerful Spreadsheet Alternative

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. 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the records you need to rank.
  2. 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. 3. View your dynamic results: Press Enter to see the ranks instantly calculated and spilled down the column, perfectly resolving any tied scores.
Fully compatible with Microsoft Excel formulas and dynamic arraysInstantly calculates multi-level sorts without complex VBALightweight, fast, and completely free to useFeatures a familiar, easy-to-navigate user interface
microsoft office alternative - wps office

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.