🧠 Excel XLOOKUP 函數完整教學：新世代的查找公式
目錄
📘 為什麼你應該使用 XLOOKUP 函數
在 Excel 的世界裡，資料查找一直是最常見、也最容易出錯的操作之一。傳統上，我們使用 VLOOKUP 或 HLOOKUP 來搜尋表格中的資料，但這兩個函數都有明顯的限制──例如只能往右查找、無法自訂錯誤訊息、且在插入或刪除欄位時容易導致公式失效。
隨著資料結構越來越複雜，這些舊式查找方式已經難以應付現代分析需求。XLOOKUP 函數正是為了解決這些問題而誕生的新一代工具。它結合了 VLOOKUP、HLOOKUP、INDEX 和 MATCH 的功能，不僅能任意方向查找，還能自動回傳對應結果、支援模糊比對，並在查無資料時提供友善的回應訊息。
⚙️ XLOOKUP 基本語法與參數說明
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| 參數 | 說明 |
|---|---|
lookup_value | 要查找的值，例如「產品名稱」或「員工編號」 |
lookup_array | 要搜尋的範圍 |
return_array | 要返回結果的範圍 |
[if_not_found] | 查無資料時顯示的訊息（可省略） |
[match_mode] | 比對模式：0 精確、-1 小於最接近、1 大於最接近、2 萬用字元 |
[search_mode] | 搜尋方向：1 從上往下、-1 從下往上 |
🧾 模擬資料表（示範練習用）
請將以下表格貼至 Excel A1:E11 範圍中：
| 產品編號 | 產品名稱 | 類別 | 庫存 | 價格 |
|---|---|---|---|---|
| A1001 | 蘋果 | 水果類 | 120 | 25 |
| A1002 | 香蕉 | 水果類 | 80 | 20 |
| A1003 | 牛奶 | 乳製品 | 50 | 45 |
| A1004 | 餅乾 | 點心類 | 200 | 35 |
| A1005 | 咖啡豆 | 飲品類 | 60 | 120 |
| A1006 | 鳳梨 | 水果類 | 30 | 55 |
| A1007 | 優格 | 乳製品 | 90 | 40 |
| A1008 | 薯片 | 點心類 | 150 | 28 |
| A1009 | 綠茶 | 飲品類 | 70 | 60 |
| A1010 | 柳橙 | 水果類 | 110 | 30 |
🔍 實用範例教學
範例 1：依產品名稱查價格
=XLOOKUP("香蕉", B2:B11, E2:E11)
結果 → 20
說明：在產品名稱欄（B2:B11）中找到「香蕉」，回傳對應價格（E2:E11）。
範例 2：反向查找（依編號找名稱）
=XLOOKUP("A1003", A2:A11, B2:B11)
結果 → 牛奶
說明：從編號欄（A2:A11）找到 A1003，回傳名稱欄（B2:B11）。可向左或向右查找。
範例 3：查不到時顯示提示訊息
=XLOOKUP("A9999", A2:A11, B2:B11, "查無此商品")
結果 → 查無此商品
說明：找不到 A9999 時不顯示錯誤（#N/A），而是顯示自訂訊息。
範例 4：近似比對（找最接近的值 ≤ 40）
=XLOOKUP(40, E2:E11, B2:B11, , -1)
結果 → 優格
說明：在價格欄（E2:E11）尋找 ≤ 40 的最大值（正好 40），回傳對應的名稱「優格」。
範例 5：反向查價格（由價格找產品編號）
=XLOOKUP(120, E2:E11, A2:A11)
結果 → A1005
說明：輸入價格 120，回傳對應的產品編號。
🧩 XLOOKUP 與 VLOOKUP 的差異比較
| 功能 | XLOOKUP | VLOOKUP |
|---|---|---|
| 查找方向 | 任意方向（上、下、左、右） | 只能往右查 |
| 插入欄位安全性 | 不受影響 | 容易錯誤 |
| 查無資料處理 | 可自訂訊息 | 需搭配 IFERROR |
| 支援近似比對 | 是 | 有限制 |
| 搜尋效率 | 快且穩定 | 較慢 |
| 可讀性 | 高（語法清晰） | 需靠欄位編號判斷 |
💡 進階應用技巧（易懂＋實務）
🔸 1. 結合 FILTER：一次找出多筆資料
=FILTER(A2:E11, C2:C11="水果類")
結果 → 僅顯示「水果類」的所有列（例如：蘋果、香蕉、鳳梨、柳橙）。
說明：新增或刪除水果類商品時，結果會自動更新，適合做動態報表。
🔸 2. 結合 SUM：快速統計分類總庫存
=SUM(FILTER(D2:D11, C2:C11="水果類"))
結果 → 340
說明：水果類庫存相加（120+80+30+110=340）。用於分類統計或即時庫存儀表板。
🔸 3. XLOOKUP + IFERROR：美化錯誤訊息
=IFERROR(XLOOKUP("芭樂", B2:B11, E2:E11), "查無此商品")
結果 → 查無此商品
說明：當查詢名稱不存在（例如「芭樂」）時，不顯示 #N/A，而是輸出友善提示。
🔸 4. 建立「價格查詢小工具」（輸入儲存格查價）
假設使用者在 F2 輸入產品名稱：
=XLOOKUP(F2, B2:B11, E2:E11, "無此產品")
範例 → 當 F2 =「餅乾」時，結果 = 35。
說明：常用於報價單、進銷存查價或表單查詢。
🧠 小結
XLOOKUP 是 Excel 函數演進的重要里程碑——不僅取代舊有查找函數，更讓資料對應邏輯清晰、可靠且可擴展。
💬 一句話重點：XLOOKUP 讓 Excel 不再只是表格，而是能快速關聯資料的高效工具。
留言