How to Sort an Excel Table by Points and Break Ties Using SORTBY
Question details
Sort a two-column Excel table by points in descending order and use a separate ranking table to break ties between equal scores.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing a leaderboard or competition scoring data where multiple entries share the same score, requiring a secondary criteria (like a separate ranking table) to accurately determine final placements without using VBA macros.
- Observed behavior
- The user requires an automated array formula to accurately arrange records, sorting primary points descending, while referencing a secondary table to resolve ranking ties.
Ensure you are using a recent version of your spreadsheet software that supports dynamic array functions, specifically LET, SORTBY, XLOOKUP, HSTACK, and DROP.
Use Dynamic Array Functions (LET, SORTBY, XLOOKUP)
Combine multiple dynamic array functions inside a LET formula to retrieve the tie-breaking ranks, sort the combined data, and present a clean output.
By utilizing the LET function, you can declare local variables to make complex calculations readable and faster. In this solution, we use XLOOKUP to find the tie-breaking rank, HSTACK to temporarily join it to the points table, SORTBY to sort based on both criteria, and DROP to remove the helper rank column from the final output.
Format your primary data as a table named 'Points' with columns [Name] and [Points]. Format your secondary tie-breaker data as a table named 'Ranks' with columns [Name] and [Rank].
Click on the empty cell where you want the top-left corner of your sorted data to spill into. Ensure there is enough empty space below and to the right so the array formula does not encounter a #SPILL! error.
Type the following formula: =LET(ranks, XLOOKUP(Points[Name], Ranks[Name], Ranks[Rank]), stacked, HSTACK(Points, ranks), sorted, SORTBY(stacked, Points[Points], -1, ranks, -1), DROP(sorted,, -1))
Press Enter. The formula will automatically look up the secondary rank, stack the arrays, sort by points (descending) then by rank (descending), and output only the sorted Name and Points columns.
Sort Data Like a Pro with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for modern dynamic array functions. You can seamlessly use SORTBY, XLOOKUP, HSTACK, and LET to analyze and sort your scoring data without needing complex VBA scripts.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your Points and Ranks data.
- 2. Select an output area: Click on the cell where you want the dynamically sorted table to appear.
- 3. Input the dynamic formula: Paste the combined =LET(...) formula to look up ranks, stack, and sort your data.
- 4. View instant results: Press Enter to instantly calculate and display the sorted leaderboard with all ties successfully resolved.
Frequently Asked Questions
Why do I get a #NAME? error when using SORTBY or LET?
The #NAME? error typically occurs if your spreadsheet software version is older and does not support modern dynamic array functions. Upgrading to the latest version of WPS Office or Microsoft 365 will resolve this.
How do I change the sorting order to ascending?
In the SORTBY function parameters, change the sort order argument from -1 (descending) to 1 (ascending). For example: SORTBY(stacked, Points[Points], 1, ranks, 1).
Can I add a third tie-breaking criteria?
Yes. SORTBY allows for multiple sort criteria. You can expand the formula by appending an additional sort array and its corresponding sort order at the end of the SORTBY arguments.
What happens if there is a tie in both points and the secondary rank?
If both the primary and secondary criteria are identical, SORTBY will leave those specific tied records in the original order they appeared in the source data.
