How to Fix Excel VLOOKUP #NAME? Errors After Copying Sheets
Question details
Resolve a VLOOKUP #NAME? error that appears after copying fiscal-year worksheets when formulas reference a new named range such as FYtwentyone.
- Product
- Microsoft Excel / WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- User copied fiscal-year worksheets and used a new named range such as FYtwentyone in VLOOKUP formulas; after copying, formulas return a #NAME? error.
- Observed behavior
- Formulas display #NAME? instead of the expected lookup value, typically because the named range does not exist, is misspelled, or contains invalid characters or invisible spaces.
Open the workbook in WPS Office Spreadsheet and enable editing. Have the exact worksheet name and named range used in the failing VLOOKUP formula available so you can compare them character by character.
Verify and Restore the Named Range Used by VLOOKUP
Use this first when copied worksheets reference a new or missing named range such as FYtwentyone and the formula returns #NAME?.
Named ranges may not transfer correctly when worksheets are copied between workbooks or created from a template. The #NAME? error usually means Excel or WPS Office cannot find a name that exactly matches the formula.
Click the cell, then look at the formula bar to note the exact named range after VLOOKUP, such as FYtwentyone.
Go to Formulas > Name Manager or press Ctrl+F3 to open the Name Manager dialog.
Find the named range in the list. If the name is missing, click New, type the exact name used in the formula, set the Refers to range by selecting the cells, and click OK.
Press F9 to refresh the workbook and confirm the #NAME? error is gone.
Copy the Exact Worksheet or Range Name to Remove Hidden Spaces
Use this when the named range exists but VLOOKUP still shows #NAME? because of invisible spaces, spelling differences, or inconsistent sheet naming.
Fix VLOOKUP #NAME? Errors in WPS Office Spreadsheet
Open your Excel workbook in WPS Office Spreadsheet, restore the named range or correct the formula reference, and get clean VLOOKUP results without changing your file format.
- 1. Open the workbook in WPS Office Spreadsheet: Launch WPS Office Spreadsheet, click File > Open, and select the affected Excel workbook that shows the VLOOKUP #NAME? error.
- 2. Open Name Manager: Go to Formulas > Name Manager or press Ctrl+F3 to list the named ranges currently available in the workbook.
- 3. Restore or edit the named range: Click New or Edit, type the exact name used in the VLOOKUP formula such as FYtwentyone, set the Refers to range, and click OK.
- 4. Recalculate and save: Press F9 to refresh formulas, confirm the error is cleared, and save the file as .xlsx.

Frequently Asked Questions
Why does my VLOOKUP show #NAME? after I copy a worksheet?
The error usually appears because the named range used in the VLOOKUP formula did not copy with the worksheet or the new name does not match exactly. Open Formulas > Name Manager in WPS Office to see which names exist in the workbook, and restore the missing named range.
How do I open Name Manager in WPS Office Spreadsheet?
In WPS Office Spreadsheet, open the workbook and go to the Formulas tab > Name Manager, or press Ctrl+F3. The dialog lists all defined names and lets you create, edit, or delete them.
Can a named range be called FYtwentyone?
Yes, FYtwentyone is a valid Excel/WPS defined name because it starts with a letter and contains only letters and numbers. Avoid spaces and symbols; Excel names must start with a letter, underscore, or backslash.
How do I copy a sheet name into a VLOOKUP formula?
Double-click the worksheet tab to enter rename mode, press Ctrl+A to select the current sheet name, then press Ctrl+C to copy it. Press Esc to cancel. In the formula, paste the copied name; if it contains spaces, wrap it in single quotes, such as 'FY2021 TB'!A1:C10.
Does WPS Office support Excel VLOOKUP and named ranges?
Yes, WPS Office Spreadsheet opens .xlsx files and supports VLOOKUP, defined names, and the Name Manager. You can repair the formula and save back to Excel format without losing compatibility.




