Formula Errors

Fix XLOOKUP Returning #N/A With a Formula Lookup Value

Camila Milosovich Oct 7, 2026 869 views

Question details

The user needs to resolve an #N/A error that occurs when a lookup function uses a dynamically extracted value from another formula, even though manually typing the value works.

Fix XLOOKUP Returning #N/A With a Formula Lookup Value

Product: Spreadsheets
Device & OS: not provided
Scenario: Attempting to retrieve data using XLOOKUP, VLOOKUP, or INDEX/MATCH where the lookup value is generated by text manipulation functions like RIGHT.
Observed behavior: The lookup function returns an #N/A error because the extracted lookup value is stored as text, while the lookup array contains numbers.

Before you start

Verify that your lookup array does not contain trailing spaces and confirm whether the target data is formatted as numbers or text.

Solution 1Recommended

Convert the Extracted Text to a Number Using the Double Unary Operator

Use the double minus sign (--) to force text-based formula results into numeric values, resolving the data type mismatch without altering source data.

Text functions like RIGHT, LEFT, or MID always return a text string, even if the result looks exactly like a number. If your lookup column contains actual numbers, XLOOKUP will read the text and number as entirely different values, resulting in an #N/A error.

Locate the error cell

Open your spreadsheet and click on the cell containing the #N/A error to enter the formula editing mode.

Insert the double unary operator

Edit the formula and place two minus signs (--) immediately before the text extraction function. For example, change RIGHT(F11,6) to --RIGHT(F11,6).

Update the lookup formula

Ensure your complete XLOOKUP formula incorporates this change, such as: =XLOOKUP(--RIGHT(F11,6),Sheet1!F:F,Sheet1!G:G).

Apply and calculate

Press Enter to apply the formula. The data type is now converted to a number on the fly, and the #N/A error should disappear.

Convert the Extracted Text to a Number Using the Double Unary Operator

Quick Fix: The double unary operator (--) is the most efficient way to coerce text into a number inside a formula without needing helper columns.

Solution 2

Standardize the Data Type of Your Lookup Column

Change the formatting of your source lookup column to text so it matches the text string generated by your lookup formula.

Advanced Spreadsheet Solution

Solve Formula Errors Easily with WPS Spreadsheet

WPS Spreadsheet provides a robust and user-friendly environment for managing complex formulas like XLOOKUP, VLOOKUP, and text manipulation. With clear data formatting tools, you can easily avoid or identify data type mismatches.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your lookup formulas.
2. Edit the formula: Double-click the cell showing the #N/A error to bring up the formula bar.
3. Apply type coercion: Add the double minus (--) before your RIGHT or LEFT function to convert the text to a number.
4. Confirm the result: Press Enter to instantly calculate the correct lookup result with zero compatibility issues.

100% compatible with Microsoft Excel formulas, functions, and formatsBuilt-in error checking to help you quickly identify #N/A issuesAdvanced 'Text to Columns' tools to prevent data type mismatchesLightweight and free to use for daily data processing and analysis

Frequently Asked Questions

Why does XLOOKUP return #N/A when the values look exactly the same?

XLOOKUP requires a strict match in both value and data type. If one value is stored as text (e.g., extracted by the RIGHT function) and the other is stored as a number, they will not match, triggering an #N/A error even if they appear visually identical.

Can I convert numbers to text within the XLOOKUP formula instead of text to numbers?

Yes, you can append an empty text string to a numeric lookup value by adding &"" to the end of the cell reference (e.g., A2&""). This forces the number to be evaluated as text to match a text-formatted lookup column.

Does this data type mismatch issue apply to VLOOKUP and INDEX/MATCH?

Yes, VLOOKUP, HLOOKUP, and the INDEX/MATCH combination all require exact data type matches to function correctly. The double unary (--) method or standardizing column formats will fix these functions as well.

WPS Office