Function Problems

How to Match Competitors Correctly After Sorting Excel Sheets

Phi Hung VoPhi Hung Vo Nov 11, 2026 868 views

Question details

The user needs to maintain accurate associations between competitor names and their scores across multiple worksheets after sorting changes their row positions.

How to Match Competitors Correctly After Sorting Excel Sheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell in your master summary sheet where you want to retrieve the competitor's score for a specific class.

2
Enter the XLOOKUP formula

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.

3
Apply to all competitors

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 XLOOKUP for Dynamic Name-Based Lookups
Tip: XLOOKUP automatically handles exact matches by default, making it much less error-prone than older lookup functions.
Advanced Formulas Made Easy

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your competitor and class sheets.
  2. 2. Insert the XLOOKUP function: Navigate to your master sheet, select the score column, and type `=XLOOKUP(`.
  3. 3. Follow the function tooltip: Follow the intuitive on-screen tooltip to select your lookup value, lookup array (names), and return array (scores).
  4. 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.
Fully supports advanced lookup functions including XLOOKUP and INDEX/MATCHSeamless compatibility with Microsoft Excel (.xlsx) filesBuilt-in error checking and function tooltips for complex formulasLightweight application with fast data processing for large worksheets
microsoft office alternative - wps office

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.