XLOOKUP，終於來了！
2022/09/25閱讀時間約 14 分鐘
在今年的八月更新，除了已命名函式之外，Google 試算表還推出了 XLOOKUP 這個新函式。XLOOKUP 有 VLOOKUP 跟 HLOOKUP 的功能，讓你可以縱橫查表，在指定範圍內找符合項目，傳回一筆對應的結果。
有了 XLOOKUP，查表會更有彈性，讓你擺脫 VLOOKUP 跟 HLOOKUP 的一些限制，還能做更細緻的比對和搜尋。如果你已經是 VLOOKUP 的老手，也不妨試試看這個新函式，相信你會有不同的體驗；如果你還是試算表的新手，或許之後查表的時候，只需要用到 XLOOKUP 就好！
這邊列出 XLOOKUP 的幾個特點：
- XLOOKUP 結合 VLOOKUP 跟 HLOOKUP，可以縱向查表、橫向查表。
- 沒有在 VLOOKUP 裡「想找的欄位在關鍵字的左邊，無法查表」的限制、也沒有在 HLOOKUP 裡「想找的欄位在關鍵字的上面，無法查表」的限制。
- 可以回傳一個儲存格、也可以回傳一列或一欄。如果你有傳回多列或多欄的需求，請考慮用 QUERY 或 FILTER。
- 有四種比對模式可以做更細緻的比對。
- 有四種搜尋模式可以做更彈性的搜尋。
- 如果 XLOOKUP 沒找到值，可以指定一個結果顯示，像是「沒有符合項目」、「Not found」、或任何你想要指定的值。
今天也會附上示範檔案給大家參考，一起體驗看看 XLOOKUP 帶來的強大威力！
語法說明
=XLOOKUP(關鍵字, 搜尋範圍, 結果範圍, [找不到關鍵字時回傳的值], [比對模式], [搜尋模式])
其中 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：用二分法搜尋整個已按遞減方式排序好的範圍。
這邊二分法的兩個模式，受限在範圍得在有按遞增或遞減的方式排序的時候才能用，就執行效率上可能在大筆資料查表的時候是好一點點，但應用上我們幾乎遇不到排序好的表，且執行效率也不會跟一般的搜尋模式差非常多，所以我們通常就不會用這兩個模式了。
同時關於 XLOOKUP，有兩件事情要注意：
- 搜尋範圍只能是單欄或單列，兩個以上的欄或列是不行的。
- 結果範圍的欄數或列數，必須和搜尋範圍的欄數或列數相同。
舉例來說：
- 搜尋範圍有 1 欄 10 列，那麼結果範圍就要有 10 列（但可以有很多欄）。
- 搜尋範圍有 20 欄 1 列，那麼結果範圍就要有 20 欄（但可以有很多列）。
練習時間
歡迎打開這邊的試算表，複製一份，一起練習看看吧！
這是是一張電器批發商的出貨清單：
我們來用 XLOOKUP 做各種各樣的搜尋吧！
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，我們下個教學見！
留言0
查看全部
