Function Problems

Use Excel VLOOKUP with a Worksheet Name from Another Workbook

Steve KSteve K Nov 18, 2026 868 views

Question details

Use a cell value to select a worksheet in another workbook for an Excel VLOOKUP, without macros.

Use Excel VLOOKUP with a Worksheet Name from Another Workbook
Product
Microsoft Excel
Device & OS
not provided
Scenario
A lookup formula needs to use a worksheet tab name supplied in a cell and search a range in another workbook.
Observed behavior
The lookup should return a matching value from the worksheet named in the cell.
Before you start

Open both workbooks in Excel and confirm the source workbook's file name and worksheet tab name. Make sure the lookup value is in A2 and the source sheet name is in B2, or adjust the formula to match your cells.

Solution 1Recommended

Combine VLOOKUP and INDIRECT to reference the external worksheet

Use this formula when the source workbook is open and a cell contains the worksheet name.

INDIRECT builds the external range reference from the workbook name and the worksheet name in B2. VLOOKUP then searches the first column of that range and returns a value from the fourth column.

1
Open the source workbook

In Excel, open Other.xlsx alongside the workbook where you want the result. Keep Other.xlsx open while the formula calculates.

2
Enter the lookup inputs

Enter the value to find in A2 and the exact source worksheet tab name in B2.

3
Enter the formula

Select the result cell and enter =VLOOKUP(A2,INDIRECT("'[Other.xlsx]"&B2&"'!A2:D100"),4,FALSE). Press Enter.

4
Adapt the reference

Replace Other.xlsx and A2:D100 in the formula with your source file name and range. Change 4 if the return value is in a different column of that range.

Combine VLOOKUP and INDIRECT to reference the external worksheet
Keep the source workbook open: Excel INDIRECT cannot resolve this external reference when the source workbook is closed. If you see #REF!, check that the workbook is open and that its file name, sheet name, and range are correct.
Work with spreadsheet formulas

Look up data from another workbook in WPS Spreadsheets

Use VLOOKUP and INDIRECT in WPS Spreadsheets to build a reference from a worksheet name in a cell and retrieve a matching value from an open workbook.

  1. 1. Open both files: Open the lookup workbook and Other.xlsx in WPS Spreadsheets.
  2. 2. Enter the inputs: Type the lookup value in A2 and the exact source worksheet name in B2.
  3. 3. Add the lookup formula: Select a result cell and enter =VLOOKUP(A2,INDIRECT("'[Other.xlsx]"&B2&"'!A2:D100"),4,FALSE), then press Enter.
  4. 4. Check the result: Keep the source workbook open and adjust the file name, range, and return-column number in the formula to match your data.
Use a cell to select the source worksheet without a macroWork with Microsoft Excel XLSX filesUse familiar spreadsheet formulas and menusGet started with WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Can INDIRECT reference a closed workbook in Excel?

No. Open the source workbook in Excel before calculating an INDIRECT formula that refers to it.

Why does the formula return #REF!?

Check that the source workbook is open and that Other.xlsx, the worksheet name in B2, and the A2:D100 range match the source file.

Why does VLOOKUP return #N/A?

With FALSE, VLOOKUP requires an exact match in the first column of the selected range. Check the value in A2 and the source range's first column for differences.

How do I return a value from a different column?

Change the 4 in the VLOOKUP formula to the position of the desired column within the selected range. For A2:D100, column A is 1 and column D is 4.