How to Move Matching Comments Between Excel Workbooks with INDEX MATCH
Question details
The user needs to pull comments from a previous monthly workbook into the current workbook by matching unique customer codes, even though the current file contains only customers who did not book services.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains monthly workbooks and wants to carry forward comments from the previous month without manually copying each row.
- Observed behavior
- The current workbook does not contain the previous comments, so a formula-based lookup is needed to match and return them by customer code.
Open both the previous month and current workbooks in WPS Spreadsheets, and verify that the customer code columns in both files use the same data type and formatting before creating the formula.
Transfer Comments with INDEX MATCH Across Workbooks
Use this when you need a widely compatible lookup formula that references a separate workbook and returns comments by exact customer code.
INDEX MATCH is useful when the return column is not the leftmost column or when you need compatibility with older workbook formats. Keeping both workbooks open makes the formula easier to create and update.
In WPS Spreadsheets, click File > Open and open the previous month workbook and the current workbook.
In the current workbook, click the cell where the first comment should appear, such as B2 next to the first customer code in A2.
Type =IFERROR(INDEX('[Previous Month.xlsx]Sheet1'!$B:$B,MATCH(A2,'[Previous Month.xlsx]Sheet1'!$A:$A,0)),"") into the formula bar and press Enter.
Drag the fill handle from the formula cell down through all rows that contain customer codes to populate the matching comments.
Select the formula results, press Ctrl+C, right-click the selection, choose Paste Special > Values, and press Enter if the comments should be static.
Use XLOOKUP as a Simpler Modern Alternative
Use XLOOKUP if your WPS Spreadsheets version supports it and you want a shorter formula for exact lookups across workbooks.
Move Matching Comments Between Workbooks with WPS Spreadsheets
Use INDEX MATCH or XLOOKUP in WPS Spreadsheets to match unique customer codes across separate Excel workbook files and automatically bring the previous month's comments into your current workbook without manual copying.
- 1. Open both workbooks: In WPS Spreadsheets, click File > Open and open the previous month and current workbook files.
- 2. Create the lookup formula: Click the first comment cell in the current workbook, then enter the INDEX MATCH or XLOOKUP formula that references the previous workbook by customer code.
- 3. Fill the results down: Drag the fill handle down the customer code rows to populate comments, then use Copy > Paste Special > Values if you need static text.
Frequently Asked Questions
Can INDEX MATCH reference a closed workbook in WPS Spreadsheets?
Yes. You can use the full file path in the reference, but keeping the source workbook open is easier and avoids update prompts. Reopen the source workbook later to refresh the values.
Why does my INDEX MATCH formula return a blank cell?
The IFERROR wrapper returns an empty text value when no exact customer code match is found. Check for leading or trailing spaces and confirm both code columns use the same data type.
Can I use XLOOKUP instead of INDEX MATCH across separate workbooks?
Yes. Use =XLOOKUP(A2,'[Previous Month.xlsx]Sheet1'!$A:$A,'[Previous Month.xlsx]Sheet1'!$B:$B,"") and fill it down. XLOOKUP is available in newer WPS Spreadsheets and Microsoft 365 versions.
How do I paste the returned comments as static values?
Select the formula cells, press Ctrl+C, right-click the selection, choose Paste Special > Values, and press Enter. This replaces formulas with the text results.
What if the workbook name contains spaces or special characters?
Use the exact file name inside single quotes in the reference, such as '[Previous Month.xlsx]Sheet1'!$A:$A, including spaces and special characters exactly as saved.
