How to Match Monthly Excel Data When Sort Order Changes
Question details
The user needs to retrieve specific monthly data for a person or item from a previous worksheet where the sort order of rows is different from the current sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing or aggregating monthly sales data across different worksheets where the list of salespeople is sorted differently each month.
- Observed behavior
- Direct cell references (e.g., =July!B5) return data for the wrong person because the row positions change from month to month.
Ensure that the unique identifiers (like the salespeople's names) are spelled identically on both worksheets without extra trailing spaces to avoid mismatch errors.
Use the XLOOKUP Function
Use the modern XLOOKUP function to dynamically search for a name and return its corresponding value, regardless of the row's position on the previous month's sheet.
A direct reference simply looks at a specific cell position. If the list is sorted differently, that position holds someone else's data. XLOOKUP searches the entire column for the specific name, ensuring accurate data retrieval.
Click on the cell in your current month's worksheet where you want the previous month's total to appear.
Type the formula: =XLOOKUP(A5, July!A:A, July!BP:BP, ""). In this example, A5 is the salesperson's name on the current sheet, July!A:A is the column containing names on the previous sheet, and July!BP:BP is the column containing the totals you want to pull.
Press Enter to see the result. Click the fill handle in the bottom-right corner of the cell and drag it down to apply the formula to the rest of the salespeople.
Use the VLOOKUP Function
If you are using an older version of Excel or spreadsheets that does not support XLOOKUP, use the traditional VLOOKUP function to achieve the same result.
Match Data Across Worksheets Seamlessly in WPS Office
WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP, VLOOKUP, and INDEX/MATCH, allowing you to cross-reference and aggregate monthly data effortlessly regardless of sorting variations.
- 1. Open your monthly workbooks: Launch WPS Spreadsheet and open the file containing your differently sorted monthly data.
- 2. Apply the lookup formula: Select the target cell and type =XLOOKUP(...) to match your data using identical syntax to Microsoft Excel.
- 3. Calculate and evaluate: Press Enter to execute the formula and drag to fill the rest of the column flawlessly.
Frequently Asked Questions
Why does my XLOOKUP formula return an #N/A error?
The #N/A error occurs when the formula cannot find an exact match. This is usually caused by spelling differences, accidental trailing spaces in the names, or the person simply not existing on the previous month's sheet.
How do I handle names that have extra spaces?
You can wrap the lookup value in the TRIM function to remove excess spaces. For example, use =XLOOKUP(TRIM(A5), July!A:A, July!BP:BP, "") to ensure a cleaner match.
Can I use INDEX and MATCH to solve this sort order problem?
Yes, INDEX and MATCH is highly reliable and compatible with all spreadsheet versions. You can use the formula =INDEX(July!BP:BP, MATCH(A5, July!A:A, 0)) to retrieve the exact same result.
What does the empty string ("") do at the end of the XLOOKUP formula?
The empty string ("") is the optional 'if_not_found' argument in XLOOKUP. It tells the formula to return a blank cell instead of an #N/A error if the salesperson is not found in the previous month's data.
