Use a Workbook Path Stored in an Excel Cell for VLOOKUP
Question details
Use a workbook path stored in a cell to perform a VLOOKUP against another workbook with Excel VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA routine reads a full file path from a worksheet, opens or identifies the source workbook, and looks up a value in its data.
- Observed behavior
- The lookup depends on a valid path, the correct workbook and range, error handling, and appropriate workbook cleanup. The reported information does not establish a specific error.
Check that the path cell contains the full local file path, including the workbook extension, and confirm the source sheet name and lookup columns. Use a macro-enabled workbook and enable macros only for code you trust.
Open the Source Workbook and Handle the VLOOKUP Result in VBA
Use this macro pattern to retain a reference to the source workbook, handle lookup errors, and close it only if the macro opened it.
The example reads the path from Sheet1!B1 and the lookup value from Sheet1!B2 in the macro workbook. It searches columns A:B on Sheet1 of the source workbook and writes the result to Sheet1!B3.
This is a macro to run from Excel, not a worksheet function to call from a cell. Adjust the sheet names, cells, lookup range, and return-column index to match your workbooks.
In the macro workbook, enter the full source workbook path in Sheet1 cell B1 and the lookup value in B2. Confirm that the lookup key is in column A and the value to return is in column B of the source Sheet1.
Press Alt+F11, choose Insert > Module, and paste the following code: Sub LookupFromPath() Dim target As Worksheet, source As Workbook, candidate As Workbook Dim filePath As String, result As Variant, errorText As String Dim openedHere As Boolean On Error GoTo Failed Set target = ThisWorkbook.Worksheets("Sheet1") filePath = Trim$(CStr(target.Range("B1").Value)) If Len(filePath) = 0 Then Err.Raise vbObjectError + 1, , "Enter a workbook path in B1." For Each candidate In Application.Workbooks If StrComp(candidate.FullName, filePath, vbTextCompare) = 0 Then Set source = candidate Exit For End If Next candidate If source Is Nothing Then If Dir$(filePath) = "" Then Err.Raise vbObjectError + 2, , "The source file was not found." Set source = Workbooks.Open(Filename:=filePath, UpdateLinks:=0, ReadOnly:=True) openedHere = True End If result = Application.VLookup(target.Range("B2").Value, source.Worksheets("Sheet1").Range("A:B"), 2, False) If IsError(result) Then target.Range("B3").Value = "No match or lookup error" Else target.Range("B3").Value = result End If If openedHere Then source.Close SaveChanges:=False Exit Sub Failed: errorText = Err.Description On Error Resume Next If openedHere Then source.Close SaveChanges:=False On Error GoTo 0 MsgBox errorText, vbExclamation, "Lookup failed" End Sub
Return to Excel, open Developer > Macros, select LookupFromPath, and click Run. Check B3 for the result; if the macro reports an error, verify the path, source sheet name, and range.

Check an Existing VLOOKUP Macro for Path and Range Errors
Use these checks if you want to fix your current code rather than replace it.
Work with Lookup Tables in WPS Spreadsheet
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files and continue working with lookup tables, while checking VBA macro compatibility separately.

Frequently Asked Questions
What should the path cell contain?
Enter the full local path to the source workbook, including its filename and extension, such as C:\Data\Source.xlsx. Check that the file exists at that location.
Why does VLOOKUP return an error even when the workbook opens?
Check the source sheet and range, make sure the lookup value is in the range's first column, and verify the return-column index. With an exact match, VLOOKUP also returns an error when it cannot find the value.
Why use a Variant for the VLOOKUP result?
Application.VLookup can return an Excel error value instead of a normal result. Store it in a Variant and check IsError(result) before using or writing the result.
Should the macro close a workbook that was already open?
Usually not. Track whether the macro opened the workbook and close it only in that case, so an existing user session is left open.
Can WPS Spreadsheet run this Excel VBA macro unchanged?
Not necessarily. VBA availability and compatibility depend on the WPS version and environment. Test the macro in your setup; WPS does not directly fix an Excel VBA error.




