Fix Excel Table References Ignoring Newly Added Rows in WPS Office
Question details
The user needs to fix an Excel structured table reference formula that does not find records added to the table. The likely cause is XMATCH using approximate or binary search on an unsorted lookup column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table with a structured-reference XMATCH formula and adds new rows, but the formula still evaluates the old range or returns an incorrect match.
- Observed behavior
- The formula returns incorrect results or appears to ignore newly added rows when the XMATCH lookup column is unsorted and the formula uses approximate or binary search.
Open the workbook in WPS Office Spreadsheets, click the Formulas tab, and identify the XMATCH formula that returns the wrong result. Check whether the lookup column is sorted if the formula uses approximate or binary match modes.
Switch XMATCH to Exact Match and Verify the Table Range
Use this when a structured-reference XMATCH formula returns incorrect results after rows are added, because the lookup column is unsorted and the formula uses approximate or binary search.
XMATCH accepts a fourth argument that controls match mode: 0 is exact match, 1 is exact or next smaller, -1 is exact or next larger, 2 is binary search ascending, and -2 is binary search descending. Binary and approximate modes assume the lookup column is sorted; if it is not, XMATCH can skip newly added rows or return the wrong position.
Open the workbook in WPS Office Spreadsheets and click the cell that contains the XMATCH formula.
In the formula bar, change or add the fourth argument to 0, for example =XMATCH(LookupValue,TableName[LookupColumn],0).
Press Enter, then go to Formulas > Calculate Now to refresh dependent formulas.
Click inside the table, select Table Tools > Resize Table, and confirm the range includes the newly added rows.
Sort the Lookup Column If Binary Search Mode Is Required
Use this only when you intentionally keep XMATCH in binary search mode for performance, and the lookup column must be sorted to match the chosen mode.
Fix Excel Table Reference Lookups in WPS Office Spreadsheets
Open your Excel workbook in WPS Office Spreadsheets, switch the XMATCH formula to exact-match mode, and confirm that new rows are included in the table range to restore accurate lookups.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the Excel file that contains the table and XMATCH formula.
- 2. Edit the XMATCH formula: Select the formula cell, click the formula bar, and set the fourth argument to 0: =XMATCH(LookupValue,TableName[LookupColumn],0).
- 3. Recalculate formulas: Press Enter, then click Formulas > Calculate Now to update formula results.
- 4. Verify the table range: Click inside the table, open Table Tools > Resize Table, and confirm the newly added rows are within the table.
- 5. Save with Excel compatibility: Save the workbook in .xlsx format to keep structured references and XMATCH working with Microsoft Excel.
Frequently Asked Questions
Why does my XMATCH formula ignore rows I just added to a table?
Usually the formula is using approximate or binary search mode on an unsorted lookup column. Change the fourth argument of XMATCH to 0 for exact match, for example =XMATCH(LookupValue,TableName[LookupColumn],0).
How can I check whether new rows are included in an Excel table in WPS Office?
Click inside the table, go to Table Tools > Resize Table, and verify or adjust the table range so it covers the new rows.
Does exact-match XMATCH require sorting?
No. Exact-match mode 0 does not require sorting. Binary search modes 2 and -2 do require the lookup column to be sorted ascending or descending.
How do I refresh formula results after changing XMATCH?
After editing the formula, press Enter and then click Formulas > Calculate Now to recalculate the worksheet.
Can WPS Office open and save Excel files with structured references and XMATCH?
Yes. WPS Office Spreadsheets is compatible with .xlsx files and supports structured table references and XMATCH, so you can edit and save without losing formulas.
