如何在另一個工作簿中查詢或搜尋特定值？

使用 VLOOKUP 設定查詢參照時，常因來源檔案的路徑、名稱或工作表頻繁變更而令人困擾。在此類情境下，透過 VBA 自動化查詢流程將更為流暢——即使來源活頁簿已關閉，也能自動完成範圍選擇並傳回所需資料。

請依照下列步驟使用 VBA 執行跨活頁簿查詢：

1. 同時按下 Alt+F11 鍵，即可開啟 Microsoft Visual Basic for Applications 編輯器視窗。

2. 在 VBA 編輯器中，點選插入＞ 模組，然後將下列程式碼複製貼上至模組視窗：

VBA：從另一個已關閉的活頁簿查詢資料並返回值

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal 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
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

重要細節：

• 此程式碼會將符合的數值傳回至查詢範圍向右偏移兩欄的位置。例如，若您選取 B 欄，結果將顯示於 D 欄。
• 若希望結果顯示於其他欄位，請將 2 改為其他數值（例如）1 表示下一欄，3 表示向右第三欄），並套用至 xRg.Offset(0,2).Formula 中的對應位置。
• 當出現提示時，請選擇正確的活頁簿與工作表；此程式碼一律會使用所選檔案中的第一個工作表。若您的來源工作表並非第一個，請調整程式碼。
• 執行不熟悉的巨集前，務必先儲存檔案，因為巨集執行後將無法復原。

3. 按下 F5 鍵，或點擊執行按鈕以執行巨集，隨即會彈出「Kutools for Excel」對話方塊，提示您選取欲查詢數值的儲存格範圍。

4. 選取範圍後，按一下確定。隨即會出現另一個對話方塊，提示您選擇來源活頁簿（即使該活頁簿已關閉）。請瀏覽並選取正確的檔案，然後按一下開啟以完成確認。

巨集執行完畢後，來源活頁簿中的對應數值將自動填入您目前工作表的目標欄位。若部分數值遺漏，請確認目前工作表中待檢索的數值區域是否與來源資料完全一致（比對時會嚴格區分大小寫，並受前後空格字元影響）。

優點：可處理已關閉的活頁簿、避免在公式中硬式編碼檔案路徑，並能於執行時靈活選擇來源檔案。
注意事項：必須啟用巨集；VBA 在受保護的工作表或非 Excel 檔案上可能無法運作。若打算經常使用此功能，請將活頁簿儲存為支援巨集的格式（*.xlsm）。

若遇到任何錯誤，請確認工作表或檔案名稱拼寫正確、所選區域適當，且檔案路徑可正常存取。如需偵錯，建議在 VBA 編輯器中逐行逐步執行程式碼。

除了傳統的 VLOOKUP 與 VBA 之外，Excel 還提供其他跨活頁簿查詢的方式。根據您的資料結構、偏好使用公式而非巨集，或需要更高彈性（例如向左搜尋或多條件查詢），這些替代方案可能更為合適。

跨活頁簿使用 INDEX 與 MATCH 函數

結合 INDEX 與 MATCH 函數，您就能在另一個活頁簿中向任意方向（左、右、上、下）查詢數值；尤其當您需要擷取的資料欄位不在查詢欄位右側時（這正是 VLOOKUP 的限制），此方法格外實用。

情境：假設您想從另一個已開啟的活頁簿中擷取價格，但水果名稱不一定位於第一欄。

1. 在目標活頁簿中，選取要顯示結果的儲存格（例如 C2），並輸入下列公式（請依需求替換活頁簿、工作表與範圍）：

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

2. 按下 Enter 後，視需要向下拖曳填滿控制點，即可複製或填滿公式。

參數說明：

• [Price.xlsx]Sheet 1!$B$1:$B$24：儲存價格的範圍。
• B2：要查詢的水果名稱。
• [Price.xlsx]Sheet 1!$A$1:$A$24：搜尋查詢值的範圍。
• 結尾的0可確保完全符合。

優勢：支援向左或向右查詢，適用於更彈性的版面配置！
提示：移動或重新命名來源檔案時，務必同步更新公式，以免出錯！

使用 XLOOKUP 執行跨活頁簿查詢（適用於 Excel 365 及更新版本）

若您使用 Excel 365 或 Excel 2021，全新的 XLOOKUP 函數更具彈性：不僅能輕鬆找出完全相符的資料、支援向左查詢，還能自動處理遺漏值，避免錯誤產生。

使用方式：
在要顯示結果的儲存格中輸入：

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

按下 Enter，並視需要複製公式。此處的「Not found」可替換為任何您希望在查詢失敗時顯示的自訂文字。

優點：比 VLOOKUP 更靈活、更易管理，還能避免許多舊版公式常見的錯誤；但 XLOOKUP 僅適用於較新版本的 Excel。

針對多重條件比對、跨合併工作表搜尋，或避免超大檔案造成的效能問題等複雜需求，建議將原始數據整理為結構化表格，或善用 Excel 內建的 Microsoft Power Query 工具，高效串聯多個活頁簿中的資料。