XLOOKUP是什麼？適用版本與優勢
XLOOKUP是Excel中現代化的查找函數，設計用來取代傳統的VLOOKUP與HLOOKUP，並解決其多項限制。XLOOKUP能在任意方向（橫向或縱向）搜尋資料，支援多欄回傳、錯誤自訂訊息，更具彈性與效率。
支援的Excel版本
- Microsoft 365（含Web版）
- Excel 2021及更新版本
- Excel for Mac（部分版本）
備註：Excel 2019及更舊版本不支援XLOOKUP。若遇到無法使用，可考慮INDEX/MATCH組合或升級Excel版本。
XLOOKUP的主要優勢
| 功能比較 | XLOOKUP | VLOOKUP/HLOOKUP |
|---|---|---|
| 查找方向 | 橫向、縱向皆可 | 只能單一方向 |
| 支援多欄回傳 | 是 | 否 |
| 查找左側資料 | 是 | 否 |
| 錯誤處理 | 可自訂if_not_found訊息 | 需結合IFERROR |
| 參數簡潔 | 直觀、易懂 | 較為繁瑣 |
| 效能 | 更快、彈性高 | 較慢 |
實務情境舉例：
在專案管理、銷售報表、人事資料等多變動的資料表中，XLOOKUP能快速查找並回傳所需資訊，無論資料位於查找欄位的左側或右側，皆能輕鬆處理。
XLOOKUP語法與參數詳解
基本語法結構
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
六大參數說明與常用設定
| 參數 | 必填 | 說明 | 範例 |
|---|---|---|---|
| lookup_value | 是 | 要查找的值 | “王小明” |
| lookup_array | 是 | 查找範圍（欄或列） | A2:A100 |
| return_array | 是 | 查到後要回傳的資料範圍 | C2:C100 |
| if_not_found | 否 | 查無資料時要顯示的訊息 | “查無此人” |
| match_mode | 否 | 比對模式（0:完全、-1:小於、1:大於、2:通配符） | 0 |
| search_mode | 否 | 搜尋方向（1:由上至下、-1:由下至上） | 1 |
參數應用實例：
假設A2:A100為員工姓名，C2:C100為部門，查找「王小明」的部門：
=XLOOKUP("王小明", A2:A100, C2:C100, "查無此人", 0, 1)
XLOOKUP基礎應用範例
單一條件查找（含圖例/表格）
範例情境：查詢產品價格
| 產品名稱 | 價格 |
|---|---|
| A | 100 |
| B | 150 |
| C | 200 |
查詢產品B的價格：
=XLOOKUP("B", A2:A4, B2:B4, "未找到")
結果：150
if_not_found錯誤處理
if_not_found參數能自訂查無資料時的訊息，避免出現#N/A錯誤。
範例：
=XLOOKUP("D", A2:A4, B2:B4, "查無此產品")
結果：查無此產品
常見錯誤訊息
-
N/A：查無資料且未設定if_not_found
-
VALUE!：參數格式錯誤
-
REF!：return_array範圍不正確
除錯建議：
檢查查找值、範圍是否一致，return_array長度需與lookup_array相同。
XLOOKUP進階應用技巧
雙向查找（行列交叉查找）
情境：查詢指定員工在指定月份的銷售額
| 一月 | 二月 | 三月 | |
|---|---|---|---|
| 小明 | 1000 | 1200 | 1100 |
| 小華 | 900 | 1300 | 1250 |
查詢小華在二月的銷售額：
=XLOOKUP("小華", A2:A3, XLOOKUP("二月", B1:D1, B2:D3))
結果：1300
一次回傳多欄/多行資料
情境：查詢員工所有資料
| 姓名 | 部門 | 職稱 | 電郵 |
|---|---|---|---|
| 王小明 | 行政 | 經理 | [email protected] |
| 李小華 | 業務 | 專員 | [email protected] |
查詢王小明的全部資訊：
=XLOOKUP("王小明", A2:A3, B2:D3)
結果：{行政, 經理, [email protected]}
結合其他函數（如IF、SUM、FILTER）
情境：查找並判斷是否為特定部門
=IF(XLOOKUP("王小明", A2:A3, B2:B3)="行政", "是行政部", "非行政部")
結合SUM計算指定員工的總銷售額：
=SUM(XLOOKUP("王小明", A2:A10, C2:E10))
多條件查找（結合&、ARRAY）
情境：查找同時符合姓名與部門的員工編號
| 姓名 | 部門 | 編號 |
|---|---|---|
| 王小明 | 行政 | 001 |
| 王小明 | 業務 | 002 |
查找王小明在業務部的編號：
=XLOOKUP("王小明業務", A2:A3&B2:B3, C2:C3)
輸入公式時需以陣列公式（按Ctrl+Shift+Enter）輸入。
XLOOKUP常見問題與除錯
常見錯誤訊息解析
| 錯誤訊息 | 可能原因 | 解決建議 |
|---|---|---|
| #N/A | 查找值不存在、範圍不一致 | 檢查查找值、設定if_not_found |
| #VALUE! | 參數格式錯誤、陣列長度不符 | 確認lookup_array與return_array長度一致 |
| #REF! | return_array範圍超出表格 | 檢查範圍設定 |
效能與限制
- 效能：XLOOKUP處理大量資料時效能優於VLOOKUP，特別是在多欄查找或反向查找情境。
- 限制：僅支援新版本Excel，舊版需用INDEX/MATCH或升級。
舊版Excel無法使用時的替代方案
- INDEX/MATCH組合：
excel
=INDEX(C2:C100, MATCH("王小明", A2:A100, 0)) - VLOOKUP（僅能查找右側資料）：
excel
=VLOOKUP("王小明", A2:C100, 3, FALSE)
XLOOKUP實務應用案例
人事資料查找
情境：查詢員工工號與聯絡方式
| 姓名 | 工號 | 電話 |
|---|---|---|
| 王小明 | 001 | 0912xxxxxx |
| 李小華 | 002 | 0987xxxxxx |
查詢李小華的工號與電話：
=XLOOKUP("李小華", A2:A3, B2:C3)
結果：{002, 0987xxxxxx}
銷售/庫存報表應用
情境：查詢指定產品的庫存與負責人
| 產品 | 庫存 | 負責人 |
|---|---|---|
| A品項 | 50 | 小王 |
| B品項 | 30 | 小李 |
查詢B品項的庫存與負責人：
=XLOOKUP("B品項", A2:A3, B2:C3)
結果：{30, 小李}
XLOOKUP與其他查找函數比較
與VLOOKUP/HLOOKUP比較
| 功能/特性 | XLOOKUP | VLOOKUP/HLOOKUP |
|---|---|---|
| 查找方向 | 任意 | 單一（橫或縱） |
| 支援多欄回傳 | 是 | 否 |
| 查找左側資料 | 是 | 否 |
| 錯誤處理 | 內建 | 需結合IFERROR |
| 參數結構 | 直觀 | 較複雜 |
| 效能 | 較佳 | 較慢 |
與INDEX/MATCH、FILTER比較
| 功能/特性 | XLOOKUP | INDEX/MATCH | FILTER |
|---|---|---|---|
| 語法簡潔 | 是 | 否 | 是 |
| 多條件查找 | 支援 | 支援 | 支援 |
| 多欄回傳 | 是 | 否 | 是 |
| 舊版支援 | 否 | 是 | 否 |
| 彈性 | 高 | 高 | 高 |
結語與工具推薦
XLOOKUP大幅提升Excel查找資料的彈性與效率，無論是人事、專案、銷售或庫存管理，都能簡化流程、減少錯誤。若您在專案管理、團隊協作或自動化報表上有更高需求，建議可搭配Monday.com、ClickUp等專業工具，進行資料整合與自動化，讓Excel與團隊協作無縫接軌，提升整體工作效率。
