XLOOKUP，終於來了！

• XLOOKUP 結合 VLOOKUP 跟 HLOOKUP，可以縱向查表、橫向查表。
• 沒有在 VLOOKUP 裡「想找的欄位在關鍵字的左邊，無法查表」的限制、也沒有在 HLOOKUP 裡「想找的欄位在關鍵字的上面，無法查表」的限制。
• 可以回傳一個儲存格、也可以回傳一列或一欄。如果你有傳回多列或多欄的需求，請考慮用 QUERY 或 FILTER。
• 有四種比對模式可以做更細緻的比對。
• 有四種搜尋模式可以做更彈性的搜尋。
• 如果 XLOOKUP 沒找到值，可以指定一個結果顯示，像是「沒有符合項目」、「Not found」、或任何你想要指定的值。

語法說明

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍, [找不到關鍵字時回傳的值], [比對模式], [搜尋模式])

• 關鍵字：要搜尋的關鍵字。可以是儲存格或文字。 例如：A2、B3、"喜特先生"。 也可以搭配萬用字元搜尋，有「*」、「?」跟「~」這三個可用，等等介紹！ 例如："台灣"、"??冰淇淋"、"*"&B3。
• 搜尋範圍：關鍵字所在的範圍。從～到～，用「～：～」的格式呈現。 這個範圍必須是單一的列或欄。 例如：A1:A50（從 A1 到 A50）、B2:2（從 B2 到第 2 列的最後一欄）。
• 結果範圍：結果所在的範圍。從～到～，用「～：～」的格式呈現。 這個範圍也必須是單一的列或欄。 例如：C1:C50（從 C1 到 C50）、D3:3（從 D3 到第 3 列的最後一欄）。
• 找不到關鍵字時回傳的值：如果 XLOOKUP 查表時沒找到資料，回傳這個值。這個是選填的參數，不寫也沒關係。預設是回傳「#N/A」錯誤。
• 比對模式：尋找關鍵字的比對模式，你可以用「0、1、-1、2」這四個數字告訴 XLOOKUP 你要的模式是什麼。這個也是選填，預設是「0」，分別代表：

 0：完全比對。

 1：完全比對、或尋找大於關鍵字的最接近值。

-1：完全比對、或尋找小於關鍵字的最接近值。

 2：使用萬用字元比對。如果你想啟用萬用字元搜尋，這邊要指定 2。

• 搜尋模式：在搜尋範圍搜尋的模式，可以用「1、-1、2、-2」這四個數字告訴 XLOOKUP 你要的模式是什麼。這個也是選填，預設是「1」，分別代表：

 1：從第一個項目開始逐一搜尋到最後一個項目。

-1：從最後一個項目開始逐一搜尋到第一個項目。

 2：用二分法搜尋整個已按遞增方式排序好的範圍。

-2：用二分法搜尋整個已按遞減方式排序好的範圍。

• 搜尋範圍只能是單欄或單列，兩個以上的欄或列是不行的。
• 結果範圍的欄數或列數，必須和搜尋範圍的欄數或列數相同。

• 搜尋範圍有 1 欄 10 列，那麼結果範圍就要有 10 列（但可以有很多欄）。
• 搜尋範圍有 20 欄 1 列，那麼結果範圍就要有 20 欄（但可以有很多列）。

練習時間

1. 搜尋「訂單號碼」Order0001 的負責人是誰。

如果我們用 VLOOKUP 的想法做做看的話，可能就會發現這第一題就已經有詐了！因為訂單號碼在 G 欄，但負責人在 F 欄，訂單號碼在負責人左邊，這樣就不能用 VLOOKUP 了。

不過用 XLOOKUP 就可以解決這個問題。把剛剛的語法搬出來：

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍)

• 關鍵字："Order0001"。
• 搜尋範圍：G:G，關鍵字在 G 欄。
• 結果範圍：F:F，負責人在 F 欄。

那麼組合起來後就是：

=XLOOKUP("Order0001", G:G, F:F)

來看看執行結果：

2. 搜尋「項目」自動掀蓋垃圾桶的第一筆訂單資訊。

這次我們試試看用 XLOOKUP 回傳一整列資訊好了。一樣把語法搬出來，依序填入資料：

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍)

• 關鍵字："自動掀蓋垃圾桶"。
• 搜尋範圍：B:B，項目在 B 欄。
• 結果範圍：A:H，因為我們想得到整筆訂單資訊。

綜合起來，就會得到：

=XLOOKUP("自動掀蓋垃圾桶", B:B, A:H)

結果：

3. 搜尋「訂單號碼」Order9999 的客戶是誰。

再來我們多加一個條件，「如果找不到這筆資料時，那就回傳『無此訂單資料』」。這時就在 XLOOKUP 寫完結果範圍後，再多指定一個值就好。假設我們想找找 Order9999 這筆訂單號碼的客戶是誰：

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍, [找不到關鍵字時回傳的值])

• 關鍵字："Order9999"。
• 搜尋範圍：G:G，項目在 G 欄。
• 結果範圍：H:H，客戶在 H 欄。
• 找不到關鍵字時回傳的值："無此訂單資料"。

組合在一起：

=XLOOKUP("Order9999", G:G, H:H, "無此訂單資料")

不出所料，在表格裡面沒有 Order9999 這筆訂單，所以就出現：

這麼一來易讀性也會提高，就會馬上知道這筆資料不存在了。如果不設定這個的話，就會出現「#N/A」的錯誤。

4. 搜尋「總計」大於且最接近 7500 塊的第一筆訂單資訊。

我們再深入一點，試試看 XLOOKUP 的比對模式，一共有這幾個：

• 0：完全比對。
• 1：完全比對、或尋找大於關鍵字的最接近值。
• -1：完全比對、或尋找小於關鍵字的最接近值。
• 2：使用萬用字元比對。如果你想啟用萬用字元搜尋，這邊要指定 2。

不確定這些模式是怎麼運作的嗎？我們先看前三個模式好了。

打個比方，在搜尋範圍裡有 8500、8000、7000 這三個數字。我們如果：

• 用 0 號模式找 7500 ... #N/A（或你定義的錯誤訊息）。
• 用 1 號模式找大於且最接近 7500 的數字 ... 8000。
• 用 -1 號模式找小於且最接近 7500 的數字 ... 7000。

回去看題目，我們想找總計（E 欄）大於且最接近 7500 塊的訂單，這時候就可以用 1 這個比對模式。一樣把語法搬出來，填空看看：

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍, [找不到關鍵字時回傳的值], [比對模式])

• 關鍵字：7500。
• 搜尋範圍：E:E，總計在 E 欄。
• 結果範圍：A:H，因為我們想得到整筆訂單資訊。
• 找不到關鍵字時回傳的值：可以跳過不填。
• 比對模式：1。

組合起來：

=XLOOKUP(7500, E:E, A:H, , 1)

結果：

可以看到總計顯示 8000，比 7500 大，且是整筆資料裡面最接近 7500 的數字了。

5. 搜尋「日期」早於且最接近 2022/9/4 的第一筆訂單資訊。

再進階一點點！比對模式除了可以用在數字上，日期也是可以的。只要搭配 DATEVALUE 函式，把日期轉換成數字，就可以用比對模式來查找資料。

日期轉成數字後有個特性，就是日期越早、數字越小；日期越晚、數字越大。舉例來說 2022 年 1 月 1 號透過 DATVALUE 轉換後，就會變成 44562、2022 年 9 月 30 號則會變成 44834。9 月 30 號的比較晚，數字比較大、而 1 月 1 號比較早，數字也比較小。

了解這個特性後，我們來把語法搬過來寫寫看：

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍, [找不到關鍵字時回傳的值], [比對模式])

• 關鍵字：DATEVALUE("2022/9/4")。
• 搜尋範圍：A:A，日期在 A 欄。
• 結果範圍：A:H，因為我們想要回傳整筆訂單資訊。
• 找不到關鍵字時回傳的值：可以跳過不填。
• 比對模式：-1，想找比關鍵字（2022/9/4）更早的日期，尋找小於關鍵字的最接近值。

組合起來：

=XLOOKUP(DATEVALUE("2022/9/4"), A:A, A:H, , -1)

結果：

6. 搜尋「訂單號碼」是 Order001... 開頭第一筆訂單資訊。

再來看看最後一個比對模式，就是使用萬用字元做模糊查詢。這特別是在你不確定關鍵字是什麼的時候，幫助到你的好方法！

萬用字元有「*」、「?」跟「~」這三個：

• 「*」：不確定的字元數有 0 個或無數個。

例如關鍵字寫了「椰果*」，搜尋結果可能會出現「椰果」、「椰果紅茶」、「椰果奶茶」、「椰果紅茶去冰微糖」等文字。

• 「?」：不確定的字元數有 1 個，可重複使用。

例如關鍵字寫了「椰果??」，會出現「椰果紅茶」、「椰果奶茶」，但不會出現「椰果紅茶去冰微糖」，因為這超過指定的 2 個字元。

• 「~」：想搜尋星號（*）或是問號（?）這些字、而不是當成萬用字元使用的時候，可以放「~」在前面。

例如關鍵字寫了「~*喜特先生*」，會出現「*喜特先生」、「*喜特先生的試算表教學」。

以這次的題目來說，我們想要找「Order001」開頭的第一筆訂單資料，那麼關鍵字就是「Order001*」了。你如果確定 001 後面只會有一個字元，你也可以下「Order001?」。

再把語法搬出來，填入看看：

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍, [找不到關鍵字時回傳的值], [比對模式])

• 關鍵字："Order001*"。
• 搜尋範圍：G:G，訂單在 G 欄。
• 結果範圍：A:H，因為我們想要回傳整筆訂單資訊。
• 找不到關鍵字時回傳的值：可以跳過不填。
• 比對模式：2，啟用萬用字元搜尋模式。

組合起來：

=XLOOKUP("Order001*", G:G, A:H, , 2)

結果：

7. 搜尋「項目」折疊式檯燈的最新一筆訂單資訊。

最後，來試試搜尋模式！所謂的「搜尋模式」，就是指定 XLOOKUP 該先從第一個項目開始往最後一個項目找、還是先從最後一個項目開始往第一個項目找。

拿 VLOOKUP 來打比方，它的搜尋模式是先由上方的第一個項目到下方最後一個項目，找到關鍵字後、按照索引傳回結果，而這也是 XLOOKUP 的預設 1 號模式。但如果你有先從最後一個項目往回找的需求，XLOOKUP 也可以辦得到，也就是 -1 號模式。

在剛剛的這張表，你會發現日期最舊的訂單在上面、最新的訂單在下面：

我們就可以試著用搜尋模式，搜尋「項目」折疊式檯燈的最新一筆（也就是日期最晚的那筆）訂單資訊。我們用 -1 號模式，讓 XLOOKUP 優先找到最新的資訊後就傳回結果。上語法～

=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍, [找不到關鍵字時回傳的值], [比對模式], [搜尋模式])

• 關鍵字："折疊式檯燈"。
• 搜尋範圍：B:B，項目在 B 欄。
• 結果範圍：A:H，因為我們想要回傳整筆訂單資訊。
• 找不到關鍵字時回傳的值：可以跳過不填。
• 比對模式：不適用，可以跳過不填。
• 搜尋模式：-1，想要讓 XLOOKUP 從最後一個項目往回找，也就是由下往上找。

組合在一起：

=XLOOKUP("折疊式檯燈", B:B, A:H, , , -1)

來看看結果：

---

如果你一路跟著我的解說搭配試算表練習、看到這邊，你真的非常認真，給你一個大大的鼓勵，辛苦了！

比起舊的 VLOOKUP 和 HLOOKUP，我覺得 XLOOKUP 更彈性，可以擺脫以往由左至右、由上至下的搜尋模式，也可以由右至左、由下至上、交叉查表也沒問題，一次傳回一個陣列也是個很驚艷的功能。

如果你喜歡這次的文章，歡迎你透過這些方法支持我：

・按下愛心、按下儲存

・留言告訴我你的想法

・加入喜特先生的官方沙龍，即時看到我發布的教學

・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案

・追蹤喜特先生的 Facebook

・按這邊小額贊助我的創作！

想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！

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