Function Problems

Fix Excel VLOOKUP and INDIRECT #NAME? Error Across Sheets

Huma Ashraf Ch Sep 30, 2026 869 views

Question details

The user is experiencing a #NAME? error when attempting to use VLOOKUP and INDIRECT functions to reference a row on another worksheet.

How to Fix #NAME? Errors in Excel VLOOKUP with INDIRECT Across Sheets

Product: Excel
Device & OS: not provided
Scenario: Building a lookup formula referencing column B and the current row on a different worksheet, where the sheet name might be fixed or stored in a cell.
Observed behavior: The formula evaluates to a #NAME? error, which in this case is caused by a conflicting formula or invalid name already existing in the referenced column on the target sheet.

Before you start

Ensure that the worksheet you are referencing actually exists, is spelled correctly in your formula, and does not contain hidden formatting issues.

Solution 1Recommended

Clear Conflicting Formulas in the Target Column

Resolve the #NAME? error by identifying and removing invalid formulas or named ranges in the referenced worksheet.

A #NAME? error in an INDIRECT or VLOOKUP formula doesn't always mean your main formula is wrong. If the target column on the other worksheet contains a broken formula, Excel will pass that error back to your main worksheet.

Navigate to the referenced sheet

Click the worksheet tab at the bottom of your screen (e.g., 'Sheet 3') that your INDIRECT formula is trying to pull data from.

Inspect the target column

Select the specific column being referenced by your formula (e.g., Column B). Scroll through the data to look for any existing #NAME? errors or broken references.

Remove or fix broken formulas

Select any cell in that column containing an error, press the Delete key to clear it, or correct the underlying formula. Return to your main sheet to verify if the VLOOKUP formula now works.

Clear Conflicting Formulas in the Target Column

Tip: You can use the 'Find & Select' tool in the Home tab to quickly locate formulas evaluating to errors within a specific column.

Solution 2

Correctly Format the INDIRECT Syntax for Dynamic Sheet Names

Ensure dynamic sheet names with spaces are properly enclosed in single quotes within the INDIRECT function.

Advanced Formula Support

Master Complex Formulas with WPS Spreadsheet

WPS Spreadsheet offers full compatibility with standard Excel functions, making it easy to build dynamic references using VLOOKUP and INDIRECT while easily spotting and auditing formula errors.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your cross-sheet data.
2. Insert the formula: Click the cell where you need the data, type =VLOOKUP(, and use the intelligent formula prompts to properly nest your INDIRECT function.
3. Audit errors automatically: If an error occurs, use the 'Error Checking' tool under the Formulas tab to trace the exact source of the #NAME? issue across your sheets.

Fully compatible with Microsoft Excel formulas and .xlsx files.Smart formula auditing tools to quickly trace #NAME? and #REF! errors.Free, lightweight, and user-friendly interface for managing complex datasets.

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error instead of #NAME??

A #REF! error typically occurs if the text string inside the INDIRECT function does not evaluate to a valid cell reference, or if you are trying to reference an external workbook that is currently closed.

How do I reference a fixed sheet name with a dynamic row in VLOOKUP?

You can concatenate the fixed sheet name with the ROW() function. For example, use =INDIRECT("'Sheet 3'!B" & ROW()) to dynamically reference column B of the current row on Sheet 3.

Can I use VLOOKUP with INDIRECT across different workbooks?

Yes, but there is a major limitation: the INDIRECT function requires the referenced external workbook to be open in the background. If the external workbook is closed, the formula will break and return a #REF! error.

Excel & Data Problems