Why XMATCH Returns Unexpected Results for Entire Row References in WPS Spreadsheets
Question details
The user needs to understand why XMATCH with entire-row references such as =XMATCH(1:1,1:1,,-1) returns unexpected values including 16384, #N/A, and 1 in column Q, and how to get reliable results.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Using XMATCH to match values across an entire worksheet row, such as =XMATCH(1:1,1:1,,-1), instead of limiting the lookup to the actual data range.
- Observed behavior
- When the lookup vector becomes large, XMATCH returns unexpected results such as 16384, #N/A, or a result of 1 in column Q instead of the expected match position.
Open the workbook and note the exact XMATCH formula and the cells that contain data, so you can replace entire-row references with a bounded range or apply an explicit conversion.
Limit XMATCH Arrays to the Actual Data Range
Use this method when you know the range that contains the lookup and return values; it avoids the large-vector behavior that produces 16384, #N/A, or 1 in column Q.
The unexpected results appear when XMATCH handles very large vectors such as an entire row. Limiting the lookup_array and return_array to the used range keeps the vector small enough for reliable matching.
Click the cell that contains the XMATCH formula, such as =XMATCH(1:1,1:1,,-1).
Click the formula bar or press F2, then replace each entire-row reference 1:1 with the actual data range, for example A1:SS1.
Press Enter to apply the change, then check that the return value now matches the expected position.
Use an Explicit Conversion Around Entire-Row References
Use this method if you need to keep whole-row references; wrapping both arguments in a conversion function such as INT can make the lookup return consistent results.
Fix XMATCH Entire Row Lookup Errors in WPS Spreadsheets
Open the workbook in WPS Spreadsheets and edit the XMATCH formula to use a bounded range or an explicit conversion. This avoids the vector-length issue and returns the correct match position while keeping your Excel files fully compatible.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the workbook that contains the XMATCH formula.
- 2. Select the formula cell: Click the cell with the unexpected XMATCH result.
- 3. Replace entire-row ranges: In the formula bar, replace 1:1 with your actual data range, for example A1:SS1, or wrap both arguments in INT if you must keep the full row range.
- 4. Apply and verify: Press Enter and confirm that XMATCH now returns the correct match position.
Frequently Asked Questions
Why does =XMATCH(1:1,1:1,,-1) return 16384 or #N/A?
When the lookup and search arrays are entire rows, the vector can exceed 512 elements and trigger an XMATCH matching defect. Reducing the range to the actual data normally resolves the result.
What is the safest way to avoid unexpected XMATCH results?
Replace entire-row references such as 1:1 with the actual data range, for example A1:SS1, in both the lookup_array and return_array arguments.
Can I keep whole-row references and still get correct results?
In some cases you can wrap the references in a conversion function, such as =XMATCH(INT(1:1),INT(1:1),,-1), but limiting the range is the more reliable fix.
Does this XMATCH issue affect all match modes?
The reported defect appears with exact match and a search mode of -1 when the vector is large. Other match modes may not show the same behavior, but using a bounded range is recommended.
Which WPS product should I use to fix this formula?
Open the workbook in WPS Spreadsheets, select the formula cell, edit the range in the formula bar, and press Enter to recalculate.
