當我們需要查找資料時，最常使用的是 VLOOKUP(垂直查找) 或 HLOOKUP(水平查找) 函數。自 OFFICE 365 開始， Excel 新增查找函數 XLOOKUP ，其強大的功能可以取代 VLOOKUP 及 HLOOKUP 函數。本文說明 XLOOKUP 函數的複雜操作。另外可以參考基本操作及進階操作。
內容目錄
語法說明
XLOOKUP ( lookup_value, lookup_array, return_array, [ if_not_found ], [ match_mode ], [ search_mode ] )
XLOOKUP ( 查找值, 查找範圍, 回傳值範圍, [ 查照不到的回傳值 ], [ 相符模式 ], [ 查找模式 ] )
備註: [ if_not_found ], [ match_mode ], [ search_mode ] 為選填引數，其餘皆為必填引數
- lookup_value 查找值：這是必填的引數，是我們要查找的資料值
- lookup_array 查找範圍：這是必填的引數，是我們要查找的資料範圍
- return_array 回傳值範圍：這是必填的引數，是我們要回傳的資料範圍
- [ if_not_found ] [ 查照不到的回傳值]：這是選填的引數，如果查找不到資料時，會回傳我們指定的值。預設回傳 #N/A 錯誤
- [ match_mode ] [ 相符模式 ]：這是選填的引數，有下列 4 種模式，預設為 0：
- 0：完全符合，如果查找不到，回傳 #N/A 錯誤
- -1：完全符合，如果查找不到，回傳下一個較小的值
- 1：完全符合，如果查找不到，回傳下一個較大的值
- 2：使用萬用字元。可參考 Microsoft 說明
- [ search_mode ] [ 查找模式 ]：這是選填的引數，有下列 4 種模式，預設為 1：
- 1：從第一筆資料開始查找
- -1：從最後一筆資料開始查找
- 2：二分搜尋法(升序)
- -2：二分搜尋法(降序)
複雜操作範例
XLOOKUP 函數基本操作範例可以參考 Excel XLOOKUP 函數 | 基本操作說明及範例，進階操作可以參考 Excel XLOOKUP 函數 | 進階操作說明及範例
資料分群
當想要將數值資料分群時，直覺會想到使用 IF 函數。但是當群組級距很多時，IF 函數可能會變得非常複雜。我們可以利用 XLOOKUP 函數的【相符模式】，將數值資料進行分群。不僅執行效率高，函數語法的複雜度也比 IF 函數低，有利於我們閱讀以及維護函數。
語法
= XLOOKUP ( 查找值 , 查找範圍, 回傳範圍, ,-1 )
原理
數值資料分群的原理，是設定 XLOOKUP 函數的【相符模式】，我們可以設定查找小於或是大於的值，回傳分群的對應值
範例
我們在 XLOOKUP 函數的【相符模式】輸入 -1，查找建立好的級距表，就可以分組銷售金額
- 建立級距表
- 【相符模式】輸入 -1
多條件查找
當查找的條件超過一個，需要同時符合多個條件時，只需要調整 XLOOKUP 函數【查找範圍】引數，不用搭配其他函數，就可以達到多條件查找
語法
= XLOOKUP ( 1, (條件1範圍=條件1) * (條件2範圍=條件2) * (條件3…), 回傳值範圍 )
原理
多條件查找的原理，是透過每個條件的回傳結果相乘，如果都符合條件，值等於 1；反之，值等於 0。
- 符合指定條件時，函數回傳 TRUE，數值等於 1；不符合指定條件時，函數回傳 FALSE，數值等於 0
- 將回傳的每個數值相乘
- 如果每個條件都符合，相乘結果為數值 1；如果其中一個條件不符合，相乘結果為數值 0
- 【查找值] 輸入 1，當【查找範圍】回傳 1，代表所有條件都滿足，也就是查找到我們需要的資料
範例
我們現在有 3個條件來查找銷售金額，分別如下：
| 條件 | 條件儲存格 | 查找範圍 |
|---|---|---|
| 銷售人員 | F2 | A2:A13 |
| 地區 | G2 | B2:B13 |
| 銷售水果 | H2 | C2:C13 |
函數如下：
= XLOOKUP (1, (A2:A13=F2) * (B2:B13=G2) * (C2:C13=H2), D2:D13)
二維度查找
XLOOKUP 函數可以”二維查找”，也就是查找指定欄/列交叉的資料。
語法
= XLOOKUP ( 查找值1 , 查找範圍1, XLOOKUP( 查找值2, 查找範圍2, 回傳範圍 ))
原理
二維查找的運作原理，是利用外部及內部的 XLOOKUP 函數進行交叉查找。
- 內部 XLOOKUP 函數查找指定值，回傳”整欄/列資料”(參考進階用法，回傳多筆資料)
- 回傳的”整欄/列資料”，作為外部 XLOOKUP 函數的【回傳值範圍】
- 外部 XLOOKUP 函數查找指定值，完成交叉查找
範例
我們要查找特定銷售人員在特定期間的銷售金額，XLOOKUP 函數二維查找如下：
= XLOOKUP ( G2, A2:A4, XLOOKUP( H2, B1:E1, B2:E4))
如果本篇文章有幫助到你，請在下方拍手圖示按 5 下。只要花幾秒鐘登入 Google 或 FB 帳號，不需任何花費就能提供我實質的回饋，支持我繼續創作，謝謝
