Function Problems

Fix Excel VLOOKUP #N/A for Macro-Pasted Values

Adam DavisAdam Davis Nov 14, 2026 868 views

Question details

Find out why VLOOKUP returns #N/A for values inserted by a macro when manually typed values work.

Fix Excel VLOOKUP #N/A for Macro-Pasted Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Clean the table keys

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.

2
Place return values beside the keys

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.

3
Clean the pasted value

In F2, enter =TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160)," ")))&"" to normalize the macro-pasted value in the same way as the table keys.

4
Run an exact-match lookup

Enter =VLOOKUP(F2,$C$2:$D$100,2,FALSE) in an empty cell. FALSE requires an exact match instead of an approximate match.

Normalize lookup values and use an exact VLOOKUP match
Check identifiers with leading zeros: Treat identifiers such as 00123 as text on both sides. If the macro has already converted 00123 to the number 123, cleaning the value will not restore the missing zeros.
Work with lookup formulas in WPS

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. 1. Open the workbook: In WPS Spreadsheets, choose Open and select the Excel workbook containing the lookup table.
  2. 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. 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. 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.
Clean common hidden characters from lookup keysUse exact-match VLOOKUP formulas in spreadsheetsOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.