XLOOKUP 兩個條件一起找：接成一個鍵與相乘兩種寫法

XLOOKUP 的查找值只有一個，那「品項是青茶、杯型是 L」這種兩個條件要同時符合的查詢，該怎麼寫？

基本語法在 XLOOKUP 入門，找不到、近似比對那些狀況在 XLOOKUP 進階陷阱，這篇只處理兩個條件一起找。

公式以 Google 試算表為準。XLOOKUP 在 Excel 365、2021、2024 有，Excel 2019 以前沒有；Excel 的動態陣列不需要 ARRAYFORMULA，下面公式貼到 Excel 時要把 ARRAYFORMULA( 和對應的右括號拿掉，否則會出現 #NAME?。Excel 的參數名稱是 lookup_value、lookup_array、return_array、if_not_found，位置與 match_mode、search_mode 的值和 Sheets 相同。

沿用 INDEX 與 MATCH 進階陷阱 的價目表，放在 A1:C9，每個品項有 M 和 L 兩種杯型：

F1 放品項「青茶」，F2 放杯型「L」。我想找的是青茶大杯的單價。

只用一個條件會拿到哪一筆

先照入門篇的寫法，只用品項去找：

來看看結果：35。XLOOKUP 預設由上往下找，找到第一個青茶（第 6 列，中杯）就停了，大杯的 42 根本沒被看到。不報錯，數字又看起來很合理，這就是最容易漏掉的地方。

寫法一：把兩欄接成一個鍵

查找範圍和查找值都用同樣的方式把品項和杯型接起來，中間放一個資料裡不會出現的分隔字元：

來看看結果：42。

• F1&"|"&F2：把查找值接成 青茶|L。
• A2:A9&"|"&B2:B9：A、B 兩欄一列一列接起來，變成 烏龍奶茶|M、烏龍奶茶|L……每一列都不重複。
• ARRAYFORMULA：讓接字串這件事一次對整欄去做。

分隔字元不要省。品項 A1 加杯型 2，和品項 A 加杯型 12，直接接起來都是 A12，加一個 | 才不會撞在一起。我自己偏好這種寫法，因為公式一眼就看得出「兩個值接起來找」。

寫法二：兩個條件相乘，找第一個 1

另一種寫法是讓每一列自己判斷「兩個條件是不是都成立」，再去找第一個成立的：

來看看結果：42。

• A2:A9=F1：A 欄每一格是不是青茶，得到一欄 TRUE / FALSE。
• B2:B9=F2：B 欄每一格是不是 L。
• 兩欄相乘：TRUE 當 1、FALSE 當 0，兩個都成立的列才是 1。
• XLOOKUP(1, …)：找第一個 1，回傳同一列 C 欄的單價。

三個條件就再多乘一組。這個寫法不用把兩欄接起來，條件是數字比較、日期範圍的時候也能用，例如再乘上 (D2:D9>=G1)。

找不到的時候

如果 F2 改成價目表裡沒有的杯型 S，兩種寫法都會得到 #N/A。想顯示文字就用第 4 個參數：

這次會顯示「查無此組合」。

資料有重複時，要第一筆還是最後一筆

價目表調價後，有人在第 10 列新增了一筆「青茶、L、40」，沒有把舊的刪掉。範圍拉到第 10 列，用預設的由上往下找，拿到的還是舊價 42。

要最新的那一筆，就把第 6 個參數 search_mode 設成 -1，改成由下往上找：

來看看結果：40。第 5 個參數 match_mode 填 0，是完全相同的比對，也就是預設值，這邊只是為了把位置讓給第 6 個參數。

明明有這筆卻查無：多餘的空格

兩個條件相乘的寫法，只要其中一個條件差一個空格就整個查無。例如 F2 不小心打成 L （L 後面多一個空格），B2:B9=F2 每一列都是 FALSE，結果是 #N/A。

用 TRIM 把輸入的條件修一下。接成鍵的寫法也會被空格影響，同樣可以對 F1、F2 各包一層 TRIM，寫成 TRIM(F1)&"|"&TRIM(F2)：

資料欄本身有多餘空格，或編號 001 是文字、條件卻是數字 1，也會遇到一樣的狀況，修法在 XLOOKUP 進階陷阱 的「文字和數字」與「多餘的空格」兩節。

什麼時候不用 XLOOKUP

兩個條件都符合的組合可能有好幾筆、而且每一筆都要的時候，換成 FILTER：

這題只有一筆，結果是 42。如果有重複，FILTER 會全部列出來，不像 XLOOKUP 只拿一筆。Sheets 的 FILTER 可以直接用逗號分隔多個條件（都要成立），Excel 的 FILTER 沒有這種寫法，要把條件相乘：=FILTER(C2:C9, (A2:A9=F1)*(B2:B9=F2))，而且 Excel 的第 3 個參數是 if_empty（沒有符合時顯示的內容）。只想合計數字的話，SUMIFS 更短，不過它遇到重複會把幾筆加在一起（例如 42＋40＝82），細節在 SUMIFS 進階陷阱。

兩個條件一起找，我平常先寫接成鍵的版本，條件變多、或要比大小的時候再換成相乘。試著用你自己的價目表跑跑看吧！

我是喜特先生，Mr. Sheet，我們下個教學見！