How to lookup/find values in another workbook?

Configuring lookup references using VLOOKUP may be confusing, especially if you frequently change the source file's path, name, or worksheet. In such scenarios, automating the lookup process using VBA can be a more streamlined solution, allowing you to look up values even when the source workbook is closed and automating range selection and data return.

Follow these steps to use VBA for cross-workbook lookups:

1. Press Alt + F11 simultaneously to open the Microsoft Visual Basic for Applications editor window.

2. In the VBA editor, click Insert > Module, then copy and paste the following code into the module window:

VBA: Vlookup data and return values from another closed workbook

Private Function GetColumn(Num As Integer) As StringIf Num <=26 ThenGetColumn = Chr(Num +64)
ElseGetColumn = Chr((Num -1) \26 +64) & Chr((Num -1) Mod26 +65)
End IfEnd FunctionSub FindValue()
Dim xAddress As StringDim xString As StringDim xFileName As VariantDim xUserRange As RangeDim xRg As RangeDim xFCell As RangeDim xSourceSh As WorksheetDim xSourceWb As WorkbookOn Error Resume NextxAddress = Application.ActiveWindow.RangeSelection.AddressSet xUserRange = Application.InputBox("Lookup values :", "Kutools for Excel", xAddress, Type:=8)
If Err <>0 Then Exit SubSet xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx",1, "Select a Workbook")
If xFileName = False Then Exit SubApplication.ScreenUpdating = FalseSet xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
For Each xRg In xUserRangeSet xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
If Not (xFCell Is Nothing) ThenxRg.Offset(0,2).Formula = xString & GetColumn(xFCell.Column +1) & "$" & xFCell.RowEnd IfNextxSourceWb.Close FalseApplication.ScreenUpdating = TrueEnd Sub

Important details:

• The code returns the matched value in a column offset by2 columns from the lookup range. For example, if you select column B, results appear in column D.
• If you want the result to appear in a different column, change the number 2 in xRg.Offset(0,2).Formula to another value (e.g., 1 for the next column, 3 for the third column to the right).
• Choose the correct workbook and worksheet when prompted; the code will always use the first worksheet in the selected file. Adjust the code if your source sheet is not the first one.
• Always save your file before running unfamiliar macros. Macros cannot be undone after execution.

3. Execute the macro by pressing the F5 key or clicking the Run button. A dialog box titled "Kutools for Excel" will ask you to select the range of cells whose values you want to look up.

4. After selecting your range, click OK. Soon after, another dialog box will appear, prompting you to choose the source workbook (even if it is closed). Browse and select the appropriate file, then click Open to confirm.

When the macro completes, the corresponding values from the source workbook will be returned to the target column in your active sheet.If some values are missing, check that lookup values in the active sheet exactly match those in the source data (case and leading/trailing spaces matter for an exact match).

Advantages: Handles closed workbooks, avoids hardcoded file paths in formulas, and provides flexibility in selecting source files on the fly.
Considerations: Macros must be enabled; VBA may not work on protected sheets or with non-Excel files. Save your workbook with macro support (*.xlsm) if you plan on using this method frequently.

If you encounter any errors, ensure you do not have typos in sheet or file names, the selected range is appropriate, and the file paths are accessible. For debugging, consider stepping through the code line by line using the VBA editor.

In addition to the classic VLOOKUP approach and VBA, there are alternative ways to perform cross-workbook lookups in Excel. These may be preferable in certain situations where your data structure is different, you prefer formulas over macros, or you need more flexibility (such as searching leftwards or using multiple criteria).

Using INDEX and MATCH Functions Across Workbooks

With the combination of INDEX and MATCH, you can look up values in any direction—left, right, above, or below—in another workbook. This is especially useful when the column you want to retrieve data from is not to the right of the lookup column (a limitation of VLOOKUP).

Scenario: Suppose you want to retrieve a price from another open workbook, where the fruit name may not be the first column.

1. In your destination workbook, select the cell where you want to display the result (e.g., C2), and enter the formula below (replace workbook, sheet, and range as needed):

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2. Press Enter. Then copy or fill the formula to other rows as needed by dragging the Fill Handle.

Parameter explanation:

• [Price.xlsx]Sheet1!$B$1:$B$24: The range where prices are stored.
• B2: The fruit name to look up.
• [Price.xlsx]Sheet1!$A$1:$A$24: The range to search for your lookup value.
• The 0 at the end ensures an exact match.

Strengths: Works for left or right lookups; accommodates more flexible layouts.
Tips: Avoid moving or renaming source files without updating your formula.

Using XLOOKUP for Cross-Workbook Lookup (Excel365 and Later)

If you use Excel365 or Excel2021, the new XLOOKUP function is even more flexible. It allows you to easily find exact matches, support left-side lookups, and automatically handle missing values without errors.

To use it:
In the cell where you want the result, enter:

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Press Enter and copy the formula as needed. Here, "Not found" can be changed to any custom text you wish to display if a lookup fails.

Benefits: More flexible than VLOOKUP and easier to manage; avoids many common errors with legacy formulas. However, XLOOKUP is only available in newer Excel versions.

For complex requirements such as multi-criteria matching, searching in merged sheets, or avoiding performance issues in very large files, consider organizing source data in structured tables or using Excel's Power Query tool to link data between multiple workbooks efficiently.