Fix XLOOKUP #N/A Errors in WPS Spreadsheet for Numbers Stored as Text
Question details
The user needs to fix an XLOOKUP formula that returns #N/A when imported numeric values are stored as text or when the match mode is entered in the wrong argument position.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is looking up a number in an imported data range and needs an exact or approximate XLOOKUP match to return the corresponding value.
- Observed behavior
- XLOOKUP returns #N/A because the lookup values may be text rather than numbers, or because the match mode is not supplied as the fifth argument.
Select a few cells in the lookup column and check whether the imported values are stored as text rather than numeric values. Also review the XLOOKUP argument order before changing the match mode, because the fourth argument controls the not-found result.
Convert imported text numbers before using XLOOKUP
Use this solution when the lookup column contains numbers that were imported or pasted as text.
Number formatting only changes how a value appears; it does not convert text that looks like a number into a numeric value. Convert the lookup column first, then reference the converted values in XLOOKUP.
Select the imported lookup column, open the Data tab, choose Text to Columns, keep the default delimiter settings, and click Finish to convert numeric text into numbers.
In a helper column, enter =VALUE(J10) or =VALUE(TRIM(J10)) if the imported values contain extra spaces, then fill the formula down the data set.
Select the helper results, use Home > Copy, then choose Home > Paste Special > Values to paste numeric results over the original lookup column or keep the helper column for the lookup.
Use XLOOKUP against the converted numeric range, such as =XLOOKUP(30,J10:J310,A10:A310), and confirm that the lookup value and lookup range use the same data type.
Correct the XLOOKUP match mode argument
Use this solution when the lookup values are numeric but the formula still returns #N/A or uses an approximate match incorrectly.
Use WPS Spreadsheet to Clean Imported Numbers and Repair XLOOKUP
WPS Spreadsheet lets you convert imported text numbers, correct XLOOKUP arguments, and verify exact or approximate matches in a familiar spreadsheet workspace. It supports Excel workbook formats so you can update the file without changing your existing workflow.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the XLOOKUP formula.
- 2. Convert the lookup values: Select the lookup column and go to Data > Text to Columns > Finish, or enter =VALUE(J10) in a helper column and fill it down.
- 3. Correct the formula: Select the result cell and enter =XLOOKUP(30,J10:J310,A10:A310,,-1) when you need an exact match or the next smaller value.
- 4. Sort approximate-match data: Select the lookup range, open Data > Sort, and sort the values in ascending order before testing the formula.
Frequently Asked Questions
Why does XLOOKUP return #N/A when both values appear to be numbers?
The lookup value or lookup column may contain numbers stored as text. Select the lookup column and use Data > Text to Columns > Finish, or convert the values with =VALUE(J10), then try XLOOKUP again.
Does number formatting convert text into numbers in WPS Spreadsheet?
No. Number formatting changes the display only. Use Data > Text to Columns, a VALUE formula, or another numeric conversion method to change text values into real numbers.
Where should the XLOOKUP match mode be placed?
Match mode is the fifth XLOOKUP argument. For exact match or next smaller value, use =XLOOKUP(30,J10:J310,A10:A310,,-1). The fourth argument is reserved for the not-found result.
Do I need to sort the lookup column for XLOOKUP?
Exact matches do not require sorting. For approximate matching, sort the lookup range in ascending order through Data > Sort so the result is reliable.
