如何在 Excel 中運用雙向查詢公式？

作者Sun修改日期2025-09-09

雙向查詢能讓您從表格中精準擷取特定列與欄交叉處的數值。當您擁有結構清晰的資料集，其中包含明確的列標籤與欄標題，並需要根據這兩項條件快速定位某個特定值時，這項技術格外實用。例如，若您正在管理銷售報表、出勤記錄或預算表，並希望立即找出特定日期與員工識別碼所對應的資料，Excel 的雙向查詢功能就能高效達成目標。下方截圖即呈現典型應用情境：系統已成功傳回列「AA-3」與欄「5-Jan」交叉處的數值。
顯示 Excel 中雙向查詢範例表格的螢幕截圖

使用公式進行雙向查詢

雙向查詢的 VBA 巨集

使用公式進行雙向查詢

在 Excel 中執行雙向查詢，是快速取得指定列與欄標題交叉處數值的直覺方法，特別適合結構清晰的表格。這種查詢方式廣泛應用於多種情境，例如依日期比對員工紀錄、根據地區與月份提取預算數據，或查詢特定學生在特定科目的測驗成績。

雖然公式靈活又便利，但主要限制在於表格結構必須固定。若需要更動態或自動化的解決方案，其他方法可能更適合——以下將為您介紹更多選項。

若要使用公式執行雙向查詢，請依下列步驟操作：

1. 列出您打算搜尋的欄標題與列標題，確保標題準確且格式一致，以避免因多餘空格或格式差異而造成查詢錯誤。以下為標示清晰的表格範例：
顯示 Excel 表格的螢幕截圖，其中包含用於雙向查詢的指定列與欄標題

2. 在您希望顯示結果的儲存格中，依表格版面輸入下列任一公式：

公式 1：INDEX 與 MATCH 組合

=INDEX(A1:I8,MATCH(L1,A1:A8,0),MATCH(L2,A1:I1,0))

此公式透過比對指定標題，精準定位列與欄的索引，並傳回兩者交叉處的數值。

公式 2：適用於數值表格的 SUMPRODUCT

=SUMPRODUCT((A1:A8=L1)*(A1:I1=L2),A1:I8)

當您的資料僅包含數值時，SUMPRODUCT 的表現最佳；若用於文字結果，可能無法傳回預期的輸出。

公式 3：搭配 MATCH 的 VLOOKUP

=VLOOKUP(L1,$A$1:$I$8,MATCH(L2,B1:I1,0)+1,FALSE)

此方法會先查詢列，再透過 MATCH 函數決定欄位的偏移量。

提示：

(1)參數說明：

A1:A8 是列標籤的範圍，L1 是您要尋找的特定列標籤；
A1:I1 是欄標題的範圍，L2 是目標欄標題；
A1:I8 為整個表格範圍；請依您的資料需求調整這些參照。

(2) 若您的待檢索值區域為文字且使用 SUMPRODUCT，將會傳回 0. 此時建議改用 INDEX/MATCH 組合，輕鬆解決問題！

輸入公式時，請確保 L1（用於列）與 L2（用於欄）中的標題值與表格內的標題完全一致，必要時需包含大小寫。

顯示 Excel 中雙向查詢範例公式的螢幕截圖

3. 按下 Enter 鍵確認公式，您選取的儲存格將立即顯示指定列標籤與欄標題交叉處的值！

注意事項與疑難排解：

若公式傳回 #N/A 等錯誤，請再次確認您的標題未包含多餘空格，且大小寫完全一致。
跨儲存格複製公式時，請視需要將相對參照改為絕對參照，並使用 $ 符號加以固定。
若您的表格規模龐大或經常變動，建議考慮使用動態定義的名稱範圍，或採用下方所述的 VBA 等替代方案，以提升擴充性與靈活性。

雙向查詢的 VBA 巨集

當公式型雙向查詢受限時（例如需支援不區分大小寫的搜尋、動態調整範圍大小，或自動執行重複查詢），自訂 VBA 巨集便成為極其實用的解決方案。對於經常處理變動表格結構，或需將查詢功能整合至自動化工作流程的使用者來說，VBA 更顯關鍵。

以下是設定並在 Excel 中使用雙向查詢 VBA 巨集的方法：

1. 前往開發人員工具 > Visual Basic，開啟 Microsoft Visual Basic for Applications 編輯器。點選插入 > 模組以新增模組，並將下列程式碼貼入其中：

Sub TwoWayLookupMacro()
    Dim tblRange As Range
    Dim rowLabel As String
    Dim colLabel As String
    Dim rowIdx As Variant
    Dim colIdx As Variant
    Dim result As Variant
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set tblRange = Application.InputBox("Select the table range for lookup", xTitleId, Type:=8)
    rowLabel = Application.InputBox("Enter the row label to find", xTitleId, Type:=2)
    colLabel = Application.InputBox("Enter the column header to find", xTitleId, Type:=2)
    
    On Error GoTo 0
    rowIdx = Application.Match(LCase(rowLabel), Application.Index(tblRange, 0, 1), 0)
    colIdx = Application.Match(LCase(colLabel), Application.Index(tblRange, 1, 0), 0)
    
    If IsError(rowIdx) Or IsError(colIdx) Then
        MsgBox "Row or column label not found. Please check your input.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    result = tblRange.Cells(rowIdx, colIdx).Value
    MsgBox "The value at the intersection is: " & result, vbInformation, xTitleId
End Sub

2. 若要執行巨集，請點選按鈕或按下 F5. 系統將提示您選取表格範圍，並輸入列與欄標籤；巨集會在彈出對話方塊中傳回交叉處的值。

實用技巧：

請確保您的表格標題位於所選區域的第一列與第一欄，以確保正確比對。
此巨集透過將輸入轉換為小寫來實現不區分大小寫的比對，有效避免常見的大小寫錯誤。
若您的表格版面有所不同，可能需要調整巨集，以確保正確索引。
針對更複雜的使用情境，可擴充 VBA 程式碼，以支援批次查詢，或直接將結果寫入 Excel 儲存格。

若遇到問題（例如找不到標題），請確認標籤與數據區域中沒有前導或尾隨空格，以及隱藏字元。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多