如何在另一个工作簿中查找/搜索值？

在VLOOKUP函数中配置保存路径、文件名和工作表可能会有些困惑。此方法将介绍一种VBA来轻松解决这个问题。

1. 按 Alt + F11 键打开Microsoft Visual Basic for Applications窗口。

2. 单击 插入 > 模块，然后将以下VBA代码粘贴到打开的模块窗口中。

VBA：从另一个关闭的工作簿中VLOOKUP数据并返回值

Private Function GetColumn(Num As Integer) As String
If Num <= 26 Then
GetColumn = Chr(Num + 64)
Else
GetColumn = Chr((Num - 1) \ 26 + 64) & Chr((Num - 1) Mod 26 + 65)
End If
End Function
Sub FindValue()
Dim xAddress As String
Dim xString As String
Dim xFileName As Variant
Dim xUserRange As Range
Dim xRg As Range
Dim xFCell As Range
Dim xSourceSh As Worksheet
Dim xSourceWb As Workbook
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xUserRange = Application.InputBox("Lookup values :", "Kutools for Excel", xAddress, Type:=8)
If Err <> 0 Then Exit Sub
Set xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Application.ScreenUpdating = False
Set xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
For Each xRg In xUserRange
Set xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
If Not (xFCell Is Nothing) Then
xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row
End If
Next
xSourceWb.Close False
Application.ScreenUpdating = True
End Sub

注意：此VBA将在选定列后两列的列中返回值。例如，我在应用此VBA时选择了B列，值将返回到D列。如果您需要更改目标列，请找到代码 xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row ，并替换 2 为您需要的其他数字。

3. 按 F5 键或单击 运行 按钮以运行此VBA。

4. 在打开的对话框中，请指定您要查找的数据区域，并单击 确定 按钮。

5. 现在，请在打开的“选择工作簿”对话框中选择您要查找值的工作簿，并单击 打开 按钮。

现在，所有选定的值都在指定的关闭工作簿中被查找，并且相应的值在指定列中返回。见截图：