Formula Errors

How to Fix Excel VLOOKUP #NAME? Errors After Copying Sheets

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the cell showing #NAME?

Click the cell, then look at the formula bar to note the exact named range after VLOOKUP, such as FYtwentyone.

2
Open Name Manager

Go to Formulas > Name Manager or press Ctrl+F3 to open the Name Manager dialog.

3
Compare and restore the named range

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.

4
Recalculate the worksheet

Press F9 to refresh the workbook and confirm the #NAME? error is gone.

Expected result: The VLOOKUP formula returns the lookup value instead of showing #NAME? once the named range matches the formula exactly.
WPS Office Spreadsheet formula fix

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. 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. 2. Open Name Manager: Go to Formulas > Name Manager or press Ctrl+F3 to list the named ranges currently available in the workbook.
  3. 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. 4. Recalculate and save: Press F9 to refresh formulas, confirm the error is cleared, and save the file as .xlsx.
Repairs #NAME? errors by restoring VLOOKUP named ranges and formula referencesOpens, edits, and saves .xlsx workbooks with full Microsoft Excel compatibilityFree and lightweight Office suite with a familiar spreadsheet interfacePreserves copied worksheets, sheet names, and named ranges when repairing formulas
microsoft office alternative - wps office

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.