Use Excel VLOOKUP with a Worksheet Name from Another Workbook
Question details
Use a cell value to select a worksheet in another workbook for an Excel VLOOKUP, without macros.

- 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.
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.
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.
In Excel, open Other.xlsx alongside the workbook where you want the result. Keep Other.xlsx open while the formula calculates.
Enter the value to find in A2 and the exact source worksheet tab name in B2.
Select the result cell and enter =VLOOKUP(A2,INDIRECT("'[Other.xlsx]"&B2&"'!A2:D100"),4,FALSE). Press Enter.
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.

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. Open both files: Open the lookup workbook and Other.xlsx in WPS Spreadsheets.
- 2. Enter the inputs: Type the lookup value in A2 and the exact source worksheet name in B2.
- 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. 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.

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.




