Formula Errors

How to Fix Excel VLOOKUP Returning #N/A for Identical Values

John Wilson Sep 28, 2026 869 views

Question details

The user's VLOOKUP formula is returning an #N/A error despite the lookup values appearing identical to the source data.

How to Fix VLOOKUP Returning #N/A for Apparently Identical Values

Product: Excel
Device & OS: not provided
Scenario: Using the VLOOKUP function to match imported data that might contain hidden formatting differences or non-printing characters.
Observed behavior: The VLOOKUP formula fails to find a match and returns an #N/A error because invisible characters or trailing spaces cause a mismatch.

Before you start

Verify that both your lookup values and your source data column share the exact same data type, ensuring numbers aren't accidentally stored as text.

Solution 1Recommended

Use TRIM and CLEAN Functions to Remove Hidden Characters

Remove invisible non-printing characters and extra spaces from imported data that cause VLOOKUP to fail.

When importing data from external databases or websites, hidden characters or trailing spaces are often included. Excel treats 'Apple ' and 'Apple' as completely different values, resulting in an #N/A error. The CLEAN function removes non-printing characters, while TRIM removes extra spaces.

Insert a helper column

Right-click the column letter next to your lookup values or source data and select 'Insert' to create a new blank column.

Apply TRIM and CLEAN formulas

In the first cell of the new column, enter the formula =TRIM(CLEAN(A2)) (assuming A2 contains your original uncleaned data) and press Enter.

Copy the formula down

Click the cell with the formula, then double-click the small square fill handle in the bottom-right corner to apply it to the entire dataset.

Replace original data with values

Select the newly cleaned column, press Ctrl+C to copy it, right-click the original data column, select 'Paste Special', and choose 'Values' to overwrite the uncleaned data.

Use TRIM and CLEAN Functions to Remove Hidden Characters

Update your VLOOKUP: Once the data is cleaned, your original VLOOKUP formula should automatically update and display the correct result instead of #N/A.

Solution 2

Convert Text to Numbers Using Text to Columns

Fix mismatches caused by numbers being stored as text in either the lookup value or the source array.

Efficient Spreadsheet Data Processing

Clean Data and Run VLOOKUP Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers powerful data handling capabilities that are highly compatible with Microsoft Excel. You can easily use TRIM, CLEAN, and VLOOKUP functions to process your imported datasets without errors.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the VLOOKUP #N/A error.
2. Clean the data: Use the =TRIM(CLEAN()) formula in an adjacent helper column to instantly strip away any hidden formatting or spaces.
3. Apply VLOOKUP: Ensure your VLOOKUP formula points to the newly cleaned data range to accurately retrieve your matched values.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Built-in powerful functions like TRIM, CLEAN, and XLOOKUP for data processingLightweight, fast, and free to use for daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

What does the #N/A error mean in a VLOOKUP formula?

The #N/A (Not Available) error indicates that the spreadsheet application cannot find an exact match for your lookup value within the first column of the specified source array.

How do I find invisible characters in my Excel cells?

You can use the LEN function (e.g., =LEN(A2)) to count the total number of characters in a cell. If the count is higher than the visible characters, it confirms there are hidden spaces or non-printing characters present.

Is there an alternative way to make VLOOKUP ignore hidden spaces without cleaning the data?

While data cleaning is recommended, you can sometimes bypass minor trailing or leading spaces by using wildcards in your lookup formula. For example, changing your lookup value reference to "*"&A2&"*" will search for the value anywhere within the cell, ignoring surrounding spaces.

Why does VLOOKUP fail even after using TRIM and CLEAN?

TRIM and CLEAN do not remove all non-breaking spaces (specifically ASCII character 160, often found in web data). To remove these, you need to use the SUBSTITUTE function: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).

Excel & Data Problems