Formula Errors

How to Fix XLOOKUP Returning #N/A Error Due to Extra Spaces

Maira Mehtab Sep 28, 2026 870 views

Question details

The user needs to resolve an #N/A error generated by the XLOOKUP function when the lookup or source values contain hidden spaces.

How to Fix XLOOKUP Returning #N/A Error Due to Extra Spaces

Product: Spreadsheet
Device & OS: not provided
Scenario: Attempting to match lookup values with source data using the XLOOKUP function to retrieve corresponding information.
Observed behavior: The formula returns an #N/A error indicating a failed match, even when the lookup value and source value visually appear identical, due to hidden leading, trailing, or duplicate spaces in the cells.

Before you start

Verify that your lookup array and return array ranges are correctly aligned, and visually inspect a sample of the error cells by double-clicking them to see if the text cursor reveals obvious trailing spaces.

Solution 1Recommended

Use the TRIM Function to Remove Extra Spaces

Wrap your lookup value or lookup array in the TRIM function to automatically clean unwanted spaces during the search process.

The most common cause of visually identical cells failing to match is hidden spaces. The TRIM function is designed to remove all spaces from a text string except for single spaces between words.

You can dynamically clean the data inside the XLOOKUP formula without permanently altering your original dataset.

Select the error cell

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

Clean the lookup value

Modify your formula to wrap the lookup_value argument in the TRIM function. For example: =XLOOKUP(TRIM(A2), B:B, C:C).

Clean the lookup array (Alternative)

If the extra spaces are located in your source data rather than the lookup value, wrap the lookup_array argument instead: =XLOOKUP(A2, TRIM(B:B), C:C).

Apply and fill

Press Enter to apply the updated formula, then drag the fill handle down to apply the corrected formula to the rest of your column.

Use the TRIM Function to Remove Extra Spaces

Using CLEAN for non-printable characters: If TRIM does not resolve the issue, the data might contain non-printable characters (often seen in data imported from web systems). In this case, use =XLOOKUP(CLEAN(TRIM(A2)), B:B, C:C).

Solution 2

Evaluate the Formula Using Calculation Steps

Use the built-in error checking tools to evaluate the formula step-by-step and pinpoint exactly where the text mismatch occurs.

Advanced Data Cleaning

Fix Complex Formula Errors Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides a powerful, intuitive environment for handling complex data lookups. With native support for XLOOKUP, TRIM, and built-in error-evaluation tools, cleaning up messy data and achieving accurate results has never been easier.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx or .csv file containing the problematic lookup values.
2. Enter the nested formula: Click the target cell and type your XLOOKUP formula, nesting the TRIM function to clean data on the fly: =XLOOKUP(TRIM(A2), B2:B100, C2:C100).
3. Utilize Evaluate Formula: Navigate to the Formulas tab and click 'Evaluate Formula' to inspect any remaining #N/A errors and instantly detect hidden spaces in source arrays.
4. Save your work seamlessly: Save your document in standard formats without worrying about compatibility issues when sharing with Excel users.

Fully compatible with Microsoft Excel formulas, formatting, and file types (.xlsx)Native support for advanced functions like XLOOKUP, TRIM, and CLEANBuilt-in Error Checking and Evaluate Formula tools for step-by-step troubleshootingLightweight, fast, and completely free for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

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

This happens because one of the values may be stored as text with hidden spaces, or formatted differently. XLOOKUP requires an exact match in both the character value and the underlying data type. Even a single invisible trailing space will cause the match to fail.

What is the difference between the TRIM and CLEAN functions?

The TRIM function is used specifically to remove leading, trailing, and duplicate spaces from text. The CLEAN function, on the other hand, removes non-printable characters (such as line breaks or system codes) that frequently appear in data imported from other databases or websites.

Can I use wildcards with XLOOKUP to ignore extra spaces?

Yes. You can set the match_mode argument in XLOOKUP to 2 (wildcard character match) and use asterisks around your lookup value (e.g., =XLOOKUP("*"&A2&"*", B:B, C:C, "Not found", 2)). However, this may return incorrect results if part of a word matches another completely different word. Using TRIM is much safer for exact matching.

How do I permanently remove spaces from my source data without changing the formula?

You can use the Find and Replace tool. Press Ctrl+H, enter a single space in the 'Find what' field, leave 'Replace with' completely blank, and click 'Replace All'. Alternatively, you can use the 'Text to Columns' feature to re-parse the data, which often naturally strips trailing spaces.

Excel & Data Problems