【Excel】雙條件、多條件查詢(Multiple Conditions)

---

在先前的文章中，我們介紹過許多資料查詢的方法，不熟悉下列函數用法的人趕快回去複習一下：

• 【Excel Function】VLOOKUP、HLOOKUP 尋找對應資料 (Find Corresponding Cell)
• 【Excel Function】INDEX、MATCH、二維資料尋找 (2D Data Search)
• 【Excel Function】XLOOKUP 尋找資料 (Find Corresponding Cell)
• 【Excel Function】FILTER 篩選；SORT、SORTBY 排序
• 【Excel Function】FILTER 多條件篩選-集合(Set theory)：and、or、not、delta

今天這篇文章，我們要來介紹多條件的查詢（不限於雙條件）。二維資料查詢也可以說是雙條件查詢，只是這篇文章要討論的是非二維資料的多條件查詢。以下圖資料為例，要找出小墨攀登玉山國家公園裡的秀姑巒山的次數，這裡我們介紹五種方法。

Excel 範例檔案下載：Excel-multiple-conditions.xlsx

---

事前認知

#DIV/0

這代表 Excel 的錯誤訊息，我們都知道任何數除以 0 是不存在的，Excel 中會以 #DIV/0 表示。

---

TRUE / FALSE

在 Excel 中，TRUE 和 FALSE 做數學運算使用，會分別被當作 1 和 0。

---

陣列輸入法 Ctrl + Shift +Enter

在舊版的 Excel 中若要回傳陣列「{…}」，在公式輸入時需要使用 Ctrl + Shift +Enter 的輸入方式，會在公式最外圍自動加上一個「{…………}」。新版(Microsoft 365)則可以直接按 Enter 即可。
下方的 VLOOKUP 方法中會有陣列的使用範例。

---

LOOKUP 多條件

=LOOKUP(1,1/((範圍1=條件1)*(範圍2=條件2)*(範圍3=條件3)),回傳範圍)
=LOOKUP(1,1/((A2:A17=K2)*(B2:B17=L2)*(C2:C17=M2)),D2:D17)

=LOOKUP(1,1/((範圍1=條件1)*(範圍2=條件2)*(範圍3=條件3)),回傳範圍)
=LOOKUP(1,1/((A2:A17=K2)*(B2:B17=L2)*(C2:C17=M2)),D2:D17)

一般人比較少使用 LOOKUP 這個函數，之後我們會再深入介紹這個函數，這邊大家簡單了解基本用法就可以囉。

第一個參數 1， 代表它會尋找小於或等於 1 的第一個值。

第二個參數為要尋找的範圍，同時 LOOKUP 會將其以遞增去排列。

第三個參數則是要回傳的對應範圍。

前言中，我們知道 TRUE 和 FALSE 在運算時會被當作 1 和 0，因此要利用這個特性來作條件的判斷。
我們把第二個參數做運算: 1/((A2:A17=K2)*(B2:B17=L2)*(C2:C17=M2))，這裡可以看到只有三個條件都符合的才會等於 1 ，其餘會等於 #DIV/0，因此 LOOKUP 就會回傳對應的資料值。

---

VLOOKUP 多條件

• 用 IF 來製作新的資料表給 VLOOKUP 來尋找

=VLOOKUP(條件1&條件2&條件3,IF({1,0},範圍1&範圍2&範圍3,回傳範圍),2,FALSE)
=VLOOKUP(I2&J2&K2,IF({1,0},A2:A17&B2:B17&C2:C17,D2:D17),2,FALSE)

=VLOOKUP(條件1&條件2&條件3,IF({1,0},範圍1&範圍2&範圍3,回傳範圍),2,FALSE)
=VLOOKUP(I2&J2&K2,IF({1,0},A2:A17&B2:B17&C2:C17,D2:D17),2,FALSE)

VLOOKUP 的第一個參數，這裡使用「&」符號來做字串連接：「小墨」&「玉山國家公園」&「玉山」會變成「小墨玉山國家公園玉山」，接著會以這個為判斷依據。

VLOOKUP 的第二個參數，原本是判斷和回傳的資料範圍，我這裡運用 IF 配合陣列「{…}」來製作出一個全新的表。

「IF({1,0},A2:A17&B2:B17&C2:C17,D2:D17)」，IF 內第一個參數是判斷項，如果 TRUE 則回傳 IF 第二個參數，相反的若是 FALSE 則回傳 IF 內第三個參數。
這裡用陣列的方式來表示有兩欄，分別是 1 和 0，分別代表 TRUE 和 FALSE。第一欄為 TRUE 所以回傳三個字串組合「A2:A17&B2:B17&C2:C17」，第二欄為 FALSE，回傳對應攀登次數「D2:D17」，下圖為 IF 回傳的資料表，左邊為 Microsoft 365，右邊為舊版的 Excel。

ps：在舊版的 Excel 要回傳陣列「{…}」需要使用 Ctrl + Shift +Enter 的輸入方式，會在公式最外圍自動加上一個「{…………}」。新版(Microsoft 365)則可以直接按 Enter 即可。

接著 VLOOKUP 的第三個參數直接使用 2，因為第二個參數所參照的資料表變成 IF 回傳的資料（下圖中間區塊），第一欄為參照欄位：文字的串接，第二欄就是我們要回傳的攀登次數，因此直接填入 2 即可。

---

---

OFFSET + MATCH 多條件

• 使用 MATCH 來回傳索引值(index)，在配合 OFFSET 去取得對應的值

=OFFSET(回傳基準,MATCH(條件1&條件2&條件3,範圍1&範圍2&範圍3,0),)
=OFFSET(D1,MATCH(I2&J2&K2,A2:A17&B2:B17&C2:C17,0),)

還不會用 OFFSET 的可以看這篇 👇 👇 👇
【Excel Function】維度轉換：OFFSET位移、ROW/ROWS行列、COLUMN/COLUMNS欄

=OFFSET(回傳基準,MATCH(條件1&條件2&條件3,範圍1&範圍2&範圍3,0),)
=OFFSET(D1,MATCH(I2&J2&K2,A2:A17&B2:B17&C2:C17,0),)

先來看 MATCH 的部分，第一個參數為要找尋的字串，這裡使用「&」符號來做字串連接：「小墨」&「玉山國家公園」&「玉山」會變成「小墨玉山國家公園玉山」，接著會以這個為判斷依據。

第二個參數為尋找的範圍，這裡同樣使用「&」符號來做字串連接「A2:A17&B2:B17&C2:C17」

第三個參數直接填入 0 即可。

這樣 MATCH 就會回傳索引值(index)，接者相回傳值放入 OFFSET 的第二個參數，把索引值當作位移量，取的對應的攀登次數。

---

INDEX + MATCH 多條件

• 使用 MATCH 來回傳索引值(index)，在配合 INDEX 去取得對應的值

=INDEX(回傳範圍,MATCH(條件1&條件2&條件3,範圍1&範圍2&範圍3,0))
=INDEX(D2:D17,MATCH(I2&J2&K2,A2:A17&B2:B17&C2:C17,0))

INDEX 和 OFFSET 的使用方式有異曲同工的感覺，這裡就簡單帶過：

• INDEX 是回傳第一個參數範圍中，對應索引值(index)的值
• OFFSET 是以一個基準來向下移動對應索引值(index)的格數

INDEX 第一個參數為要回傳的範圍，第二個參數直接放入整個 MATCH 函數（OFFSET方法中的MATCH），這樣 INDEX 就會直接回除對應的攀登次數。

=INDEX(回傳範圍,MATCH(條件1&條件2&條件3,範圍1&範圍2&範圍3,0))
=INDEX(D2:D17,MATCH(I2&J2&K2,A2:A17&B2:B17&C2:C17,0))

---

FILTER 多條件

在工作中，我發現比較少人會使用到 FILTER 函數，其實這個函數相當實用

這裡的篩選條件是：性別為男性(male)、喜歡喝奶茶(milk tea)，兩者需要同時成立

=FILTER(資料範圍,(條件A)*(條件B))
=FILTER(A2:C7,(B2:B7="male")*(C2:C7="milk tea"))

這裡只要將兩個條件相乘（*）即可，記得要括號，「(條件A)*(條件B)」

這裡簡單展示最基本的雙條件用法，詳細說明、其他變化的用法可以看這篇文章👇👇👇

【Excel Function】FILTER 多條件篩選-集合(Set theory)：and、or、not、delta

---

XLOOKUP 多條件

=XLOOKUP(1,(範圍1=條件1)*(範圍2=條件2)*(範圍3=條件3),回傳範圍)
=XLOOKUP(1,(A2:A17=K2)*(B2:B17=L2)*(C2:C17=M2),D2:D17)

這五種方法中，我最推薦的用法就是 XLOOKUP。XLOOKUP 可說是一個非常強大的函數，在 Excel 2016 以上的版本才有此函數 ，建議大家可以訂閱 Microsoft 365 或者直接買斷 Office 家用版 2021。

=XLOOKUP(1,(範圍1=條件1)*(範圍2=條件2)*(範圍3=條件3),回傳範圍)
=XLOOKUP(1,(A2:A17=K2)*(B2:B17=L2)*(C2:C17=M2),D2:D17)

XLOOKUP 第一個參數這邊使用 1，尋找 1 這個值。

第二個參數為尋找的範圍「(A2:A17=K2)*(B2:B17=L2)*(C2:C17=M2)」，這是三個條件判斷式，如果尋找值符合就會回傳 TRUE，反之回傳 FALSE，再將三個判斷果相乘。如下圖：

接著 XLOOKUP 會回傳三個判斷果相乘等於 1 的對應值，就可以取得攀登次數了。

整體而言，我認為 XLOOKUP 使用起來會更直觀、簡潔。推薦大家可以好好認識 XLOOKUP👇 👇 👇
【Excel Function】XLOOKUP 尋找資料 (Find Corresponding Cell)

---

Excel 範例檔案下載：Excel-multiple-conditions.xlsx

---

更多 Excel 文章請至 文章導覽 尋找，也可利用上方「搜尋(Search...)」功能

有任何莫非工具的問題，歡迎至「表單」提問

---

Excel 是不是很容易
和墨菲一起用 Excel 把工作變簡單