Fix Excel VLOOKUP #N/A for Macro-Pasted Values
Question details
Find out why VLOOKUP returns #N/A for values inserted by a macro when manually typed values work.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A macro inserts lookup values into an Excel worksheet, and VLOOKUP searches for them in a table.
- Observed behavior
- VLOOKUP returns #N/A for macro-pasted values, but matching values entered manually work.
Identify a macro-pasted value, the matching cell in the lookup table, and the VLOOKUP formula that refers to them. Check that the lookup range includes the matching row and that the lookup column is the first column in that range.
Normalize lookup values and use an exact VLOOKUP match
Use helper columns to remove common hidden spaces and compare lookup values as text.
The following example uses A2:A100 for lookup keys, B2:B100 for return values, and E2 for the macro-pasted lookup value. Choose empty columns for the helpers and adjust the references to fit your sheet.
In C2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))&"" and fill the formula down through C100. This removes common nonprinting characters and converts nonbreaking spaces to regular spaces.
In D2, enter =B2 and fill the formula down through D100. VLOOKUP can now search the cleaned keys in column C and return the corresponding values from column D.
In F2, enter =TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160)," ")))&"" to normalize the macro-pasted value in the same way as the table keys.
Enter =VLOOKUP(F2,$C$2:$D$100,2,FALSE) in an empty cell. FALSE requires an exact match instead of an approximate match.

Check macro-pasted data types and paste values without formatting
Use this approach when the macro may be inserting numbers as text or carrying unwanted source formatting.
Clean lookup values and run VLOOKUP in WPS Spreadsheets
Open the workbook in WPS Spreadsheets, normalize the lookup keys with helper formulas, and use an exact-match VLOOKUP to test the result.
- 1. Open the workbook: In WPS Spreadsheets, choose Open and select the Excel workbook containing the lookup table.
- 2. Create cleaned table keys: In an empty helper column, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))&"" and fill it down beside the table. Adjust A2 to the first lookup key.
- 3. Add the return values: In the column immediately to the right of the cleaned keys, reference the corresponding return-value cell, such as =B2, and fill the formula down.
- 4. Clean and look up the pasted value: In an empty cell, apply the same cleaning formula to the pasted value. Then enter an exact-match formula such as =VLOOKUP(F2,$C$2:$D$100,2,FALSE), adjusting the references to your sheet.

Frequently Asked Questions
Why does a manually typed value work when the macro-pasted value does not?
The pasted value may contain hidden characters or have a different data type. Compare the cells with ISNUMBER and ISTEXT, and use TRIM and CLEAN to check for common extra characters.
Does cell formatting alone cause VLOOKUP to return #N/A?
A visible format difference does not necessarily change the underlying value. Check the actual data type and contents as well as the display format; a values-only paste does not automatically convert text to numbers.
Why should I use FALSE in VLOOKUP?
Set the fourth argument to FALSE, as in =VLOOKUP(F2,$C$2:$D$100,2,FALSE), when you need an exact match. Approximate matching requires a suitably sorted lookup column and can return an unexpected result.
How do I preserve leading zeros in lookup IDs?
Store the IDs as text in both the macro output and the lookup table. If a value has already been converted to a number, restore the zeros from the source or apply a known fixed-width rule before matching.




