(聲明：本篇文章是為了做實驗，而由 ChatGPT Agent自動產生，AI產生的文章對你仍會有幫助？)
為什麼需要 XLOOKUP 和 XMATCH？
🔍 在 Excel 中，搜尋與比對資料一直是常用操作。傳統的 VLOOKUP 和 HLOOKUP 雖然方便，但是有諸多限制：只能從左往右搜尋、插入或刪除欄位會破壞公式、無法回傳左側資料，也不支援反向查找。Excel 在 2020 年提出的 XLOOKUP 和 XMATCH 函數，正是為了改善這些缺點而設計。XLOOKUP 可以在任何方向搜尋，支援精確與模糊匹配，還有錯誤處理；XMATCH 則能取得符合條件的相對位置，不需要再用 MATCH + INDEX 組合，且功能更加完整。
XLOOKUP 函數介紹
XLOOKUP 是 Excel 中的一個強大查詢函數，用法彈性高，被視為 VLOOKUP 和 HLOOKUP 的下一代替代品。它的基本詞法如下：
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| 參數 | 說明 |
|---|---|
| lookup_value | 要搜尋的值，可以是單一儲存格、文字、數字，或是由公式產生的結果。 |
| lookup_array | 要搜尋的區域，不必局限於表格的第一欄，可以選擇任何一列或一欄作為索引範圍。 |
| return_array | 找到匹配值後要回傳的資料範圍。它的大小應與 lookup_array 一致。 |
| [if_not_found] | （可選）當搜尋不到符合條件的值時，顯示的自訂訊息或預設值；若忽略此參數，預設會返回錯誤 #N/A。 |
| [match_mode] | （可選）用來控制匹配方式：0 為精確匹配（預設）；-1 為模糊匹配，回傳小於或等於的最大值；1 回傳大於或等於的最小值；2 支援萬用字元的模糊匹配。 |
| [search_mode] | （可選）用來控制搜尋方向：1 為從第一筆開始往下搜尋（預設）；-1 從最後一筆往上搜尋；2 進行二分搜尋（升凸），-2 進行二分搜尋（降凸）。 |
通過 match_mode 與 search_mode 這兩個參數，XLOOKUP 能處理大多數查表的需求，例如往回搜尋、進行區間值查找或加速搜尋速度。
XLOOKUP 實際範例
以下是一個範例，我們有一份產品資料表，包含產品代號、名稱與價格，我們希望輸入代號就能查到對應的名稱和價格：
| 代號 (A列) | 產品 (B列) | 價格 (C列) |
|---|---|---|
| P001 | 無線滑鼠 | 350 |
| P002 | 藍牙鍵盤 | 650 |
| P003 | 監視器 | 4990 |
| P004 | 外接硬碟 | 2200 |
| P005 | 耳機 | 780 |
假設要在儲存格 E2 輸入代號，並在 F2 顯示產品名稱、G2 顯示價格，可使用：
=XLOOKUP(E2, A2:A6, B2:B6, "找不到產品") // 查詢產品名稱
=XLOOKUP(E2, A2:A6, C2:C6, "找不到價格") // 查詢價格當輸入「P003」時，兩個公式會分別返回「監視器」和「4990」。如果輸入不存在的代號，例如 P100，則會顯示「找不到產品」與「找不到價格」。
還有一些進階應用，例如：
- 使用萬用字元找尋包含某字串的品名：
=XLOOKUP("*\u6ed1\u9f20*", B2:B6, C2:C6, , 2)會返回第一個品名含「滑鼠」的價格。 - 反向搜尋：
=XLOOKUP(P002, A2:A6, B2:B6, , , -1)會從資料表底部開始找尋對應值，適用於資料存在重複值且想取得最後一筆的情況。 - 區間比對：若價格列表為區間上限，可利用 match_mode=-1 取得小於或等於的區段。
XMATCH 函數介紹
XMATCH 則是 MATCH 函數的升級版，它返回符合搜尋條件的項目在範圍中的相對位置。它的強化之處在於支援模糊匹配、萬用字元，以及多種搜尋方向，讓我們不必通過 INDEX + MATCH 組合即可完成複雜查找。
其基本詞法如下：
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| 參數 | 說明 |
|---|---|
| lookup_value | 要搜尋的目標值，可為文字、數字或邏輯值。 |
| lookup_array | 要搜尋的區域，它可以是一列或一欄，也可以是一個集合。 |
| [match_mode] | （可選）與 XLOOKUP 相同，可選擇精確匹配或模糊匹配：0 為精確匹配（預設）；-1 為小於或等於；1 為大於或等於；2 為支援萬用字元。 |
| [search_mode] | （可選）控制搜尋方向：1 從首筆開始、-1 從最後一筆開始、2 使用二分搜尋（升凸）、-2 二分搜尋（降凸）。 |
XMATCH 實際範例
假設同樣有上面的產品代號表，我們想知道某個代號在清單中的位置，例如代號 P003 是清單中的第幾筆：
=XMATCH("P003", A2:A6, 0) // 返回 3透過 XMATCH 得到的位置，可結合 INDEX 函數快速取得對應欄位的資料：
=INDEX(B2:B6, XMATCH("P003", A2:A6)) // 返回監視器XMATCH 也可以配合條件模糊匹配，例如取得最後一個價格小於 1000 的產品代號：
=XMATCH(1000, C2:C6, -1) // 返回 5，代表耳機位於第 5 筆與以往的 MATCH 最大差異在於 XMATCH 對萬用字元和搜尋方向的支援，讓我們可以輕鬆處理更複雜的查找邏輯。
XLOOKUP 與 XMATCH 的結合應用
在資料分析或動態報表中，XLOOKUP 與 XMATCH 可以互相搭配使用。以下是一個應用場景：我們有成績表，希望查找某學生的分數，同時取得他的名次。
| 學生 (A列) | 分數 (B列) |
|---|---|
| 阿宏 | 88 |
| 怡蒲 | 95 |
| 志明 | 72 |
| 佩真 | 88 |
| 小威 | 68 |
我們可以用以下公式查詢：
- 查詢分數：
=XLOOKUP("6021", A2:A6, B2:B6)// 返回怡蒲的分數為 95 - 查詢名次：
=XMATCH(XLOOKUP("6021", A2:A6, B2:B6), SORT(B2:B6,,-1))
此公式先利用 XLOOKUP 取得怡蒲的分數，再使用 XMATCH 在降凸排序後的分數序列中找出其排名。
如果我們需要對表格做動態篩選與排序，例如在銷售報表中随意切換產品類型與區域，也可以用 XMATCH 找出相對位置後再傳遞至 XLOOKUP 或 INDEX，建立靈活的儀表板。
常見問題與注意事項
儘管 XLOOKUP 和 XMATCH 功能強大，仍有幾點要注意：
- 這兩個函數僅支援新版 Excel (Microsoft 365 或 2021)；较舊的版本可能無法使用。
- match_mode 選擇為 2 時，會啟用萬用字元（
*、?）匹配，此時必須確保 lookup_value 為文字；若輸入數字，可能產生意外結果。 - 使用二分搜尋（search_mode=2 或 -2）時，需要確保 lookup_array 已排序，否則回傳結果可能不正確。
- 當 return_array 與 lookup_array 尺寸不一致時，會回傳 #VALUE! 錯誤；請務必保持兩者維度相同。
- 在大型資料集上進行大量查詢時，XLOOKUP 的效率優於傳統函數，但仍建議避免在 volatile 函數或陣列公式中重複呼叫，以免影響效能。
善用 XLOOKUP 與 XMATCH，可以簡化工作表的維護與公式撰寫，尤其是在處理跨表格、跨工作表的查找時特別便利。
經由這篇教學，你應該能對 XLOOKUP 及 XMATCH 的用法有更完整的認識。無論是在學術譜稿、公司報表，或是個人記帳與名單整理，都能嘗試使用這兩個函數來提升效率，讓 Excel 不再只是靜態資料的呈現工具，而是智慣型的資料查詢利器。
