Excel 中多條件 VLOOKUP 實用指南

VLOOKUP() 是 Excel 中使用最廣泛的函數之一。它允許您從表格的特定欄位中查找並檢索資料。這是合併來自不同工作表資料或查找相關資訊的基本工具。

但 VLOOKUP() 有一個眾所周知的限制：它只能基於單一條件進行搜尋。當您需要匹配多個條件才能找到正確的資料時，這就成了一個問題——這在任何嚴肅的試算表工作中都是常見情況。

在本文中，我們將探討如何克服這個限制。我們將介紹經典的基於公式的技術，然後引入一種現代、由 AI 驅動的方法，可以在幾秒鐘內完成任務，無需任何公式。如果您是 Excel 新手，掌握這些概念對於高效的資料管理至關重要。

VLOOKUP() 基礎知識

在處理多個條件之前，讓我們快速回顧一下基本的 VLOOKUP() 語法：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

在上述公式中：

• lookup_value 是您要搜尋的值。
• table_array 是包含資料的儲存格範圍。查詢值必須位於此範圍的第一欄。
• col_index_num 是您要從中檢索值的範圍內的欄位編號。
• range_lookup 是一個邏輯值（TRUE 表示近似匹配，FALSE 表示精確匹配）。您幾乎總是使用 FALSE）。

讓我們看看它的實際應用。我們有一個員工資料表，想使用員工的 ID 號碼來查找其名字。

我們在儲存格 F6 中輸入 ID 4032。在儲存格 G6 中，我們使用以下公式：

=VLOOKUP(F6, A2:D11, 2, FALSE)

Excel 在我們表格的第一欄中找到 4032，移動到第 2 欄，並返回名字 "David"。簡單有效。但如果我們需要使用兩個或更多條件來查找一個值呢？

如何處理多個條件：AI 方法 vs. 手動方法

面對多條件查詢時，您有兩條路徑：現代、由 AI 驅動的方法，或傳統、基於公式的方法。讓我們從最簡單、最快速的解決方案開始。

現代方法：使用 AI 助手進行多條件查詢

與其建立複雜的公式，您可以使用像 Excelmatic 這樣的 AI Excel 助手來為您完成工作。過程非常簡單：上傳您的試算表，並用簡單的語言提出您的問題。

讓我們來看一個常見的業務問題：根據客戶、產品和日期查找特定的銷售金額。

與其編寫公式，您只需將此檔案上傳到 Excelmatic 並提問：

"James 在 02-Jul-24 購買 Laptop 的銷售金額是多少？"

Excelmatic 理解您的請求，掃描資料，並立即給您答案。

以下是比較：

• 傳統方法： 需要建立一個新的「輔助」欄位，串聯來自三個不同欄位的資料，仔細編寫一個與新串聯格式匹配的 VLOOKUP 公式，並處理潛在的資料類型問題（例如日期變成數字）。
• Excelmatic 方法： 只需提一個問題。

AI 方法消除了對中間步驟、複雜語法和故障排除的需求。它更快、更不易出錯，並讓您專注於您想回答的問題，而不是您需要建立的公式。

手動方法：使用公式進行多條件查詢

如果您更喜歡自己建立解決方案，或者想了解其背後的機制，這裡有一些經典的基於公式的技術。

方法 1：使用輔助欄位的 VLOOKUP()

這是最常見的手動方法。您建立一個「輔助」欄位，將您的多個條件組合成一個單一的、唯一的值。然後，您對該欄位執行標準的 VLOOKUP()。

使用我們的銷售資料範例，我們想根據 客戶姓名、產品 和 日期 查找銷售金額。

步驟 1：建立輔助欄位

插入一個新欄位（例如，在 A 欄），並合併條件。我們將使用 & 運算子連接來自 客戶姓名、產品 和 日期 的值。最好使用像 | 這樣的分隔符號，以防止錯誤匹配（例如，"AB" & "C" 與 "A" & "BC" 相同）。

新 A 欄中的公式將是：

=C2&"|"&D2&"|"&E2

當您串聯日期時，Excel 通常會將其轉換為其底層的序列號。為了保持日期格式可讀並確保正確的查詢，最好使用 TEXT() 函數。

改進後的公式是：

=C2&"|"&D2&"|"&TEXT(E2, "DD-MM-YY")

將此公式向下拖曳以填滿整個欄位。

步驟 2：執行 VLOOKUP

現在，您可以使用一個標準的 VLOOKUP()，它引用您的條件儲存格，並在新的輔助欄位中尋找匹配項。

=VLOOKUP(H2&"|"&I2&"|"&TEXT(J2, "DD-MM-YY"),$A$2:$F$11, 6, 0)

這個方法可行，但它需要修改您的原始資料，這並不總是理想的。

方法 2：使用 CHOOSE() 函數的 VLOOKUP()

一種更高級的技術涉及使用 CHOOSE 函數建立一個「虛擬」輔助欄位，這樣您就不必更改您的表格。CHOOSE 可以在記憶體中為 VLOOKUP 建立一個新的表格陣列。

CHOOSE 的語法是：CHOOSE(index_num, value1, [value2], ...)。

讓我們使用這個學生成績資料集：

要在沒有輔助欄位的情況下查找學生在特定季度的成績，我們可以使用這個陣列公式：

=VLOOKUP($E2&"|"&F$1, CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15), 2, FALSE)

以下是它的工作原理：

• CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15) 建立一個虛擬的兩欄表格。第一欄是學生姓名和季度的串聯，第二欄是成績。
• 然後 VLOOKUP 搜尋這個虛擬表格。

這很強大，但也很複雜，並且可能難以除錯。

VLOOKUP 的替代方案：INDEX() 和 MATCH()

幾十年來，INDEX() 和 MATCH() 的組合一直是進階查詢的首選方法。它比 VLOOKUP() 更靈活，並且可以透過陣列公式原生處理多個條件。

• MATCH() 查找值在範圍內的位置。
• INDEX() 返回範圍內特定位置的值。

讓我們使用這個員工資料集來查找 John（25 歲，住在芝加哥）的職位。

以下是我們的條件：

儲存格 G5 中的公式是：

=INDEX(C2:C5,MATCH(1,(G3=A2:A5)*(G4=B2:B5)*(G6=D2:D5),0))

這是一個陣列公式，因此在舊版 Excel 中您可能需要按 Ctrl + Shift + Enter。

這個公式的工作原理是為每個條件建立 TRUE/FALSE 值的陣列，將它們相乘以找到所有條件都為 TRUE 的列（其值為 1），然後使用 MATCH 來查找該列的位置。它非常強大，但學習曲線陡峭。

最後總結

我們已經介紹了多條件查詢的經典技術——輔助欄位和強大的 INDEX/MATCH 組合。掌握這些公式無疑會讓您在處理複雜資料集時更有信心。

然而，資料分析領域正在不斷發展。像 Excelmatic 這樣的 AI 工具提供了一種範式轉移，將焦點從「我該如何編寫公式？」轉移到「我想向我的資料提出什麼問題？」。透過在後台處理複雜的機制，這些工具使您能夠比以往更快、更直觀地發現洞察。

準備好跳過公式，從您的資料中獲得即時答案了嗎？立即免費試用 Excelmatic，體驗 AI 驅動的資料分析如何改變您的工作流程。

無論您堅持使用公式還是擁抱 AI 助手，目標都是一樣的：將您的資料轉化為答案。

常見問題

VLOOKUP() 可以返回多個值嗎？

VLOOKUP() 本身無法返回多個值。為此，您需要使用其他函數，例如 Microsoft 365 中的 FILTER，或者在陣列公式中使用 INDEX()、SMALL() 和 ROW() 的組合。

如何使用 VLOOKUP() 建立雙向查詢？

您可以結合 VLOOKUP() 和 MATCH() 來建立雙向查詢，其中 MATCH() 動態地為 VLOOKUP() 查找欄位索引編號。然而，雙向的 INDEX/MATCH/MATCH 通常更穩健。

如何在 Excel 中執行不區分大小寫的查詢？

預設情況下，VLOOKUP()、MATCH() 和 XLOOKUP() 都是不區分大小寫的。對於區分大小寫的查詢，您需要在陣列公式中使用像 EXACT() 這樣的函數。