Excel 多重條件 INDEX MATCH 使用指南

幾年前我處理行銷活動分析時，需要比較多個區域的銷售表現。資料分散在多個 Excel 工作表，我必須將特定產品銷售數字彙整到單一摘要報告。起初我嘗試手動搜尋複製資料，但實際操作比想像困難——只要錯一行，整份報告就可能全盤皆錯。

這時我發現了 INDEX MATCH。雖然花了幾次嘗試才寫對公式，但當看到它能輕鬆定位並提取所需數字後，就成為我的例行工具。很長一段時間，它都是我處理複雜查詢的首選方案。

如今雖然 INDEX MATCH 仍是重要技能，但環境正在改變。AI 驅動工具現在能達成相同結果卻無需編寫任何公式。本文將說明如何用傳統 INDEX 與 MATCH 函數掌握多條件查詢，同時展示像 Excelmatic 這樣的現代工具如何用極短時間達成相同目標。

INDEX MATCH 回顧

INDEX MATCH 是兩種 Excel 函數組合的簡稱，協作執行進階查詢。雖然也可稱為 INDEX(MATCH())，但本文將統一使用 INDEX MATCH。現在讓我們分別了解：

INDEX() 函數根據指定範圍內的位置擷取儲存格值。語法如下：

=INDEX(array, row_num, [column_num])

其中：

• array 是要擷取值的儲存格範圍
• row_num 是要傳回值的陣列列號
• column_num (選用) 是要傳回值的陣列欄號

MATCH() 函數識別值在範圍內的相對位置。語法如下：

=MATCH(lookup_value, lookup_array, [match_type])

其中：

• lookup_value 是要尋找的值
• lookup_array 是函數搜尋值的範圍
• match_type 為選用參數。0 表示完全匹配，也是我們最常使用的選項

如何結合 INDEX() 與 MATCH()

透過將 MATCH() 嵌套在 INDEX() 內，可建立動態查詢。舉例說明：假設要在資料集中尋找 "David Wilson" 的部門。與其在 INDEX() 中硬編碼列號，不如使用 MATCH() 來確定：

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

上述公式中，MATCH("David Wilson", A2:A6, 0) 傳回 4，即列位置。而 INDEX(C2:C6, 4) 從範圍 C2:C6 的第 4 列擷取值，結果為 "Seattle"。

INDEX MATCH 與 VLOOKUP() 比較

了解 INDEX() 和 MATCH() 運作原理後，來看看為何 INDEX MATCH 通常比常見的 VLOOKUP() 更佳：

• 靈活性： 不同於 VLOOKUP() 要求查詢欄位必須位於最左側，INDEX MATCH 允許從任意欄位檢索資料，不受位置限制
• 穩定性： 使用 VLOOKUP() 的公式在插入或刪除欄位時可能失效，因為它們依賴靜態欄位索引號。INDEX MATCH 更穩健，因其直接引用傳回欄位
• 效能： 在大型資料集上 INDEX MATCH 可能更快，因它僅處理查詢和傳回欄位，而 VLOOKUP() 可能選取更大的資料陣列
• 清晰度： 使用 INDEX MATCH 時無需手動計算欄位編號。您指定查詢欄位和傳回欄位，使公式更易閱讀

多條件 INDEX MATCH：傳統方法

我經常需要處理包含重複項的資料集，根據多個條件尋找值是常見任務。INDEX MATCH 能處理這些情境，但需要更進階的公式結構。讓我逐步引導您完成。

設定多條件資料

首先建立資料集，確保以表格形式妥善組織，每個欄位標題清晰。每列應代表唯一記錄，每欄應包含特定資料屬性。

例如以下範例資料集：

編寫多條件公式

標準 INDEX MATCH 公式僅適用單一條件。要處理多條件，需將其改為陣列公式。

基本語法如下：

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2), 0))}

其中：

• return_range 是將傳回值的範圍
• criteria1, criteria2 是要滿足的條件
• range1, range2 是要檢查條件的欄位範圍
• * 運算子作為 AND 條件，建立 1 和 0 的陣列。MATCH(1, ...) 接著尋找首個所有條件為真（結果為 1）的列

讓我們看兩種應用方法。

方法 1：使用輔助欄

若不熟悉陣列公式，輔助欄能簡化流程。方法是將多個條件合併到單一欄位，然後用於標準 INDEX MATCH 查詢。例如要根據員工姓名和部門尋找職務，可建立輔助欄串接「名字」與「部門」：

=A2&B2

此輔助欄簡化了 INDEX MATCH 公式。無需複雜陣列公式，可用更簡單的方法引用輔助欄：

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

方法 2：使用陣列公式

若不想在表格新增額外欄位，可直接使用陣列公式處理多條件。例如以下是不用輔助欄尋找 Alice 在 HR 部門薪資的方法。

使用以下公式：

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

• (F4=A2:A11) 在姓名欄檢查 "Alice"
• (F5=B2:B11) 在部門欄檢查 "HR"
• * 運算子確保 MATCH 僅尋找兩個條件皆為 TRUE 的列

重要： 在舊版 Excel 中，必須按 Ctrl+Shift+Enter 確認此為陣列公式。Excel 會自動加上大括號 {}。在具動態陣列的新版本中，直接按 Enter 即可。

更簡單快速的方法：AI 多條件查詢

雖然陣列公式功能強大，但編寫複雜且除錯困難。這正是 Excelmatic 這類 AI 工具改變遊戲規則之處。

Excelmatic 是能理解自然語言的 Excel AI 代理。無需建構公式，只需提問即可。

要解決相同問題——尋找 Alice 在 HR 部門的薪資——使用 Excelmatic 的流程如下：

1. 上傳 Excel 檔案 至 Excelmatic 平台
2. 在聊天介面提出問題，就像詢問同事一樣：

   名叫 Alice 的員工在 HR 部門的薪資是多少？

Excelmatic 會立即掃描資料、理解多條件（"Alice" 和 "HR"）、執行查詢並給出答案。

對比顯而易見：

• INDEX MATCH 陣列公式： =INDEX(D2:D11, MATCH(1, ("Alice"=A2:A11) * ("HR"=B2:B11), 0))
  • 需要了解 INDEX、MATCH、陣列語法和邏輯運算子
  • 若範圍錯誤或忘記按 Ctrl+Shift+Enter 容易出錯
• Excelmatic： "名叫 Alice 的員工在 HR 部門的薪資是多少？"
  • 無需公式知識
  • 直觀、快速且零錯誤

這種 AI 驅動方法讓您專注於想解答的問題，而非如何建構公式的技術細節。

多條件 INDEX MATCH 的進階應用

INDEX MATCH 還能實現更多功能，但正如我們將看到的，這些複雜情境透過 AI 助理也會變得異常簡單。

嵌套 INDEX MATCH 處理複雜查詢

有時需要同時匹配列與欄的條件。例如在這個顯示各區域產品類別銷售額的資料集中，標準查詢無法運作。

我想查找東區的家具銷售額。為此需要匹配列中的 "Furniture" 和欄中的 "East"。這需要嵌套 INDEX MATCH 公式：

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

運作原理如下：

• 主 INDEX() 函數查詢資料範圍 B2:D4
• 第一個 MATCH(D6, A2:A4, 0) 尋找 "Furniture" 的列號
• 第二個 MATCH(D7, B1:D1, 0) 尋找 "East" 的欄號

INDEX 接著傳回該列與欄交會處的值：450。

雖然有效，但此公式更加複雜。使用 Excelmatic 時，請求同樣只是簡單提問：

東區的家具銷售額是多少？

AI 會自動處理二維查詢，省去建構和除錯嵌套公式的麻煩。

常見挑戰與疑難排解

我初學 INDEX MATCH 時遇到不少錯誤。以下介紹這些錯誤及解決方法——以及 AI 方法如何幫助您完全避開這些問題。

處理 INDEX MATCH 公式錯誤

#N/A 和 `#VALUE! 等錯誤很常見。

• #N/A 錯誤發生在 MATCH() 函數找不到查詢值時。可能原因包含拼寫錯誤、多餘空格（可用 TRIM() 函數修正）或引用錯誤範圍
• #VALUE! 錯誤常出現在多條件陣列公式中，若您忘記按 Ctrl+Shift+Enter（在舊版 Excel 中）

AI 在疑難排解中的優勢

使用像 Excelmatic 這樣的工具時，這些公式特定錯誤會消失。您無需除錯 #N/A、#VALUE! 或 `#REF! 錯誤，因為無需編寫公式。如果工具找不到資料，會用自然語言告知（例如「在資料集中找不到 'Alice'」），而非顯示難以理解的錯誤代碼。這使得整個資料分析過程更寬容且易於使用。

結語

掌握 INDEX MATCH 是每位專業 Excel 使用者的必經之路。它讓您能執行靈活穩健的查詢，遠超 VLOOKUP 的能力範圍。理解如何建構陣列公式和嵌套查詢是寶貴技能，能深化您對 Excel 運作原理的理解。

然而，資料分析的目標是獲取洞察，而非花費數小時編寫和除錯公式。像 Excelmatic 這樣的現代 AI 工具代表著試算表生產力的下一階段進化。它們讓您能透過簡單提問直接獲得答案。

準備好改變您的 Excel 資料處理方式？立即試用 Excelmatic，體驗 AI 驅動多條件查詢的威力。只需用自然語言描述需求，讓 Excelmatic 處理所有技術複雜性——無需學習公式、無需除錯錯誤，只有推動業務決策的即時洞察。

我的建議是：學習 INDEX MATCH 來理解邏輯原理。但在日常工作中，請擁抱 AI 的速度與簡便。讓機器處理語法，使您能專注於資料傳達的訊息。

INDEX MATCH 常見問答

如何處理 INDEX MATCH 區分大小寫？

預設情況下 INDEX MATCH 不區分大小寫。要實現區分大小寫，需在陣列公式中結合 EXACT 函數：

=INDEX(return_range, MATCH(TRUE, EXACT(lookup_value, lookup_range), 0))

記得按 Ctrl+Shift+Enter。

如何處理 INDEX MATCH 公式錯誤？

用 IFERROR 函數包裹公式，以傳回自訂訊息而非錯誤：

=IFERROR(INDEX(..., MATCH(...)), "查無結果")

使用 INDEX MATCH 與 XLOOKUP() 處理多條件有何差異？

XLOOKUP() 是 Excel 中較新、更易用的函數，可取代 INDEX MATCH 處理多種查詢。對於多條件，XLOOKUP 通常比 INDEX MATCH 陣列公式更簡單。但兩者仍需要您學習並編寫特定公式語法。像 Excelmatic 這樣的 AI 工具則更進一步，完全免除編寫公式的需求，改以自然語言操作。