Formula Errors

How to Fix #N/A Error in Nested XLOOKUP Formulas in Excel

Maira Mehtab Sep 27, 2026 871 views

Question details

The user needs to fix an #N/A error in a nested XLOOKUP grid lookup formula that occurs because pasted lookup values contain hidden leading or trailing spaces.

Product: Spreadsheets
Device & OS: not provided
Scenario: Using a nested XLOOKUP formula to retrieve data from a two-dimensional grid, where lookup values pasted from another source fail to match the source headers.
Observed behavior: The formula returns an #N/A error because the pasted lookup values have invisible leading or trailing spaces, preventing an exact match with the lookup array data.

Before you start

Verify that your lookup values and source data headers do not contain unintended leading or trailing spaces, as XLOOKUP requires an exact match by default.

Solution 1Recommended

Wrap the Lookup Array with the TRIM Function

Dynamically remove leading and trailing spaces from your lookup array within the formula itself to ensure an exact match without altering source data.

When dealing with data pasted from external sources, invisible spaces often cause lookup formulas to fail. By nesting the TRIM function inside your XLOOKUP formula, you can clean the lookup array on the fly.

Select the target cell

Click on the cell containing the nested XLOOKUP formula that is currently returning the #N/A error.

Modify the inner XLOOKUP array

Click into the formula bar and locate the lookup array reference for your inner XLOOKUP (e.g., Sheet2!$B$1:$E$1). Wrap this reference with the TRIM function.

Apply the updated formula

Update the formula to match this structure: =XLOOKUP(A2,Sheet2!$A$2:$A$5,XLOOKUP(B2,TRIM(Sheet2!$B$1:$E$1),Sheet2!$B$2:$E$5)) and press Enter.

Formula Update Complete: The TRIM function will now ignore any leading or trailing spaces in the header row, allowing the XLOOKUP to return the correct value.

Solution 2

Clean the Source Data Headers Manually

Permanently fix the issue by removing the extra spaces directly from your source data headers or pasted values.

Seamless Spreadsheet Formulas

Easily Resolve Formula Errors with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP and TRIM. You can easily troubleshoot and build complex nested formulas with its intuitive interface and high compatibility.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the nested XLOOKUP formula.
2. Edit the formula: Select the cell with the #N/A error and click into the Formula Bar at the top.
3. Add the TRIM function: Insert the TRIM function inside your nested XLOOKUP formula to clean the array spaces, and press Enter to instantly fix the error.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Native support for modern functions including XLOOKUP and dynamic arrays.Built-in error checking and formula auditing tools.Free and lightweight office suite for daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP return #N/A even when I can see the value in the grid?

The #N/A error means XLOOKUP cannot find an exact match. This frequently occurs when either the lookup value or the source array contains hidden characters like leading or trailing spaces, making seemingly identical text different to the formula.

Does XLOOKUP require exact matches by default?

Yes, unlike older functions like VLOOKUP which default to an approximate match if not specified, XLOOKUP defaults to an exact match. You must ensure the data strings match perfectly.

How do I handle #N/A errors gracefully in XLOOKUP without breaking the sheet?

XLOOKUP has a built-in argument for handling errors. You can use the fourth argument '[if_not_found]' to return a custom message or value (like a blank cell or 'Not Found') instead of the #N/A error. For example: =XLOOKUP(A1, B:B, C:C, "Not Found").

WPS Office