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 的負責人是誰。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=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 個，可重複使用。

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

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

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

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

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

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

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

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

如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！