Fix the Excel VLOOKUP #NAME? Error When Pasting or Using VBA
Question details
Fix a VLOOKUP formula that works when entered manually but returns #NAME? when pasted or inserted through VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user copies or inserts a VLOOKUP formula that references a named range and appends an empty string.
- Observed behavior
- The manually entered formula works, but the pasted or VBA-inserted formula returns #NAME?.
Identify the destination cell and confirm that the DataR650 named range exists and contains at least 13 columns. Compare the formula that works when entered manually with the exact formula text being pasted or supplied to VBA.
Simplify the VLOOKUP Formula and Check Its Named Range
Use this approach to isolate differences in the pasted formula and verify that Excel can resolve the table reference.
In the reported case, removing the trailing &"" resolved the issue. That expression is valid Excel syntax in general, so also check the name and the exact text passed to VBA if the error persists.
In Excel, open Formulas > Name Manager and find DataR650. Check its scope and Refers To range, and confirm that the range has at least 13 columns.
Select the destination cell and enter =IFERROR(CLEAN(VLOOKUP($C2,DataR650,13)),""). This removes the trailing &"" used in the original formula.
Inspect the formula bar after pasting. If Excel resolves the intended name as DataR650, use that name in the copied formula rather than an incorrect worksheet-qualified reference such as Rescheduled!DataR650. Worksheet-scoped names may require a sheet qualifier.
In the VBA editor, replace the formula assignment with an expression such as Range("D2").Formula = "=IFERROR(CLEAN(VLOOKUP($C2,DataR650,13))," & Chr(34) & Chr(34) & ")". Change D2 to the intended destination cell, run the macro, and inspect the resulting formula in Excel.

Work With VLOOKUP Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility, making it easy to move everyday workbook tasks. It does not fix VBA formula insertion in Microsoft Excel.

Frequently Asked Questions
Why does Excel show #NAME? for a VLOOKUP formula?
Excel may not recognize a function or named range in the formula. Check the formula bar for spelling differences, then open Formulas > Name Manager to verify that DataR650 exists and is available in the destination sheet.
Is Rescheduled!DataR650 always an invalid reference?
No. A sheet qualifier can be appropriate for a worksheet-scoped name. Check the name's scope in Formulas > Name Manager and use the reference that resolves to the intended range in your workbook.
How do I insert an empty string in a VBA formula?
The formula text must contain two double-quote characters for an Excel empty string. In a VBA assignment using Range("D2").Formula, you can build those characters with Chr(34) & Chr(34), then inspect the formula bar after running the macro.
Does IFERROR hide zero values returned by VLOOKUP?
No. IFERROR replaces errors with the value you specify, but zero is not an error. Use a separate formula condition or an appropriate cell number format if you need to hide zero results.
How can I make VLOOKUP search for an exact match?
Add FALSE as the fourth argument, for example VLOOKUP($C2,DataR650,13,FALSE). Without that argument, VLOOKUP uses approximate matching, which requires the lookup column to be sorted appropriately.




