How to Match Competitors Correctly After Sorting Excel Sheets
Question details
The user needs to maintain accurate associations between competitor names and their scores across multiple worksheets after sorting changes their row positions.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing and retrieving competitor scores across multiple class sheets where sorting frequently changes row orders.
- Observed behavior
- Fixed row references break when sheets are sorted, leading to mismatched competitor names and pulled scores.
Ensure that competitor names are spelled exactly the same across all your class worksheets, as name-based lookup functions require exact text matches to retrieve the correct scores.
Use XLOOKUP for Dynamic Name-Based Lookups
XLOOKUP is a modern, highly flexible function that finds names across sheets regardless of their current sort order.
When dealing with data that gets sorted frequently, direct cell references (like =Class1!B2) will quickly point to the wrong competitor. Using XLOOKUP allows the spreadsheet to search for the competitor's exact name and return the corresponding score, no matter which row it ends up in after a sort.
Click on the cell in your master summary sheet where you want to retrieve the competitor's score for a specific class.
Type `=XLOOKUP(A2, 'Class1'!A:A, 'Class1'!B:B)` where A2 is the cell containing the competitor's name in your master sheet, 'Class1'!A:A is the column containing names in the source sheet, and 'Class1'!B:B is the column containing their scores.
Press Enter to calculate the first score. Then, click and drag the fill handle at the bottom right of the cell to copy the formula down for all other competitors.

Use INDEX and MATCH for Broad Compatibility
If you are using an older version of Excel that doesn't support XLOOKUP, combining the INDEX and MATCH functions provides a robust alternative.
Master Data Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced formulas like XLOOKUP, INDEX, and MATCH, allowing you to accurately track and manage competitor scores across sorted sheets. It offers a seamless experience for organizing large datasets without fear of broken references.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your competitor and class sheets.
- 2. Insert the XLOOKUP function: Navigate to your master sheet, select the score column, and type `=XLOOKUP(`.
- 3. Follow the function tooltip: Follow the intuitive on-screen tooltip to select your lookup value, lookup array (names), and return array (scores).
- 4. Calculate and secure data: Press Enter. You can now freely sort the source sheets, and WPS Spreadsheet will dynamically keep the matched data accurate.

Frequently Asked Questions
Why do direct cell references break when I sort my Excel sheet?
Direct references (like =Class1!B2) point to a specific cell position, not the data itself. When you sort the sheet, the data moves to a new row, but the formula continues looking at the original cell coordinate, pulling incorrect information for that competitor.
Can I use VLOOKUP instead of XLOOKUP or INDEX MATCH?
Yes, you can use VLOOKUP as long as the competitor's name is located in the very first column of your lookup range. However, XLOOKUP and INDEX MATCH are more flexible because they can look up data regardless of column order.
How do I calculate the total and find the overall winner?
Use your lookup formulas to pull scores from each class sheet into separate columns on a master sheet. Then, use the SUM function (e.g., =SUM(B2:D2)) to add each competitor's scores across the columns, and apply the MAX function to the total column to find the highest overall score.




