這兩年有購入新電腦或公司使用 office 產品的人，應該都發現 Microsoft 出了 office 應用程式訂閱版本：Microsoft 365。當中增加了許多新函數，今天我們要來介紹其中一個在工作中很實用的函數：XLOOKUP。
XLOOKUP改善了 VLOOKUP、HLOOKUP 的缺點，並整合了 INDEX、MATCH 的功能，可以說是進階版的 LOOKUP。 這邊我簡單示範一下 XLOOKUP 常用的案例。
Excel 範例檔案下載：Excel-XLOOKUP.xlsx
延伸閱讀：【Excel】雙條件、多條件查詢(Multiple Conditions)
XLOOKUP
=XLOOKUP(a, b, c)
a：尋找值
b：尋找範圍
c：回傳範圍
回傳：該尋找值對應的回傳值
最基本的用法就如同 VLOOKUP、HLOOKUP 一樣，而且改善了只能比對第一欄的問題。這邊就直接進入範例：找出戴資穎的世界排名，並填入［G2］。
=XLOOKUP(尋找值,尋找範圍,回傳範圍)第一個參數尋找值，要尋找「戴資穎」，因此要填入 F2。
第二個參數尋找範圍，要尋找尋找值 位於資料範圍，在這是全部的球員，因此填入 $C$2:$C$11。
第三個參數回傳範圍，要回傳的對應資料，要取得戴資穎的「排名」，所以填入$A$2:$A$11。
這樣就完成一開始的目標：找出戴資穎的世界排名，並填入［G2］。
這邊有個注意事項：如果尋找範圍、回傳範圍 這兩個參數資料來源的筆數(列數)不同，會回傳 #VALUE!，看到也不用擔心，趕緊確認一下資料範圍的正確與否。
XLOOKUP：找不到對應值
=XLOOKUP(a, b, c, [d])
a：尋找值
b：尋找範圍
c：回傳範圍
[d]：無相符資料時的回傳值，若留空則回傳：#N/A
回傳：該尋找值對應的回傳值，若無相符值回傳自訂文字
這裡和第一個範例其實差別不大，就是在後面多了一個參數，此參數並非必填，因此上一題範例中，我並沒有使用該參數：找出Mr. Y的世界排名，並填入［G3］。
=XLOOKUP(尋找值,尋找範圍,回傳範圍,找不到訊息)第一個參數尋找值，要尋找「戴資穎」，因此要填入 F2。
第二個參數尋找範圍，要尋找尋找值 位於資料範圍，在這是全部的球員，因此填入 $C$2:$C$11。
第三個參數回傳範圍，要回傳的對應資料，要取得戴資穎的「排名」，所以填入$A$2:$A$11。
第四個參數找不到訊息，這裡直接輸入文字，因此要使用雙引號把文字包起來，”未列入排名”。當然此參數也可以指向特定的儲存格(Cell)之值，只要該參數直接填該儲存格即可。
XLOOKUP：回傳列陣
=XLOOKUP(a, b, c, [d])
a：尋找值
b：尋找範圍
c：回傳範圍
[d]：無相符資料時的回傳值，若留空則回傳：#N/A
回傳：該尋找值對應的回傳值
XLOOKUP 還有提供一個極為強大的功能，它可以回傳多筆資料，這邊稱為陣列(Array)。我只使用一次函數，即可在對應位置回傳出對應資料。如圖範例我只在［G2］使用的函數，但是［H2］和［I2］都有回傳值，其回傳欄位數量與函數中選取範圍相關：找出世界排名第一名的國家、球員、積分，分別填入［G2］、［H2］、［I2］。
=XLOOKUP(尋找值,尋找範圍,回傳範圍,找不到訊息)第一個參數尋找值，要尋找「1」，因此要填入 F2。
第二個參數尋找範圍，要尋找尋找值 位於資料範圍，在這是全部的排名，因此填入 $A$2:$A$11。
第三個參數回傳範圍，要回傳的對應資料，要取得「國家」、「球員」、「積分」，所以填入B2:D11。
共選取了三欄(B, C, D欄)，因此回傳值也會有三欄資料。
這樣就完成本範例的目標：找出世界排名第一名的國家、球員、積分，分別填入［G2］、［H2］、［I2］。其用法和第一個案例十分相似，大家可以自行試試看，改變第三個參數，看看會有甚麼不同的結果，有任何想法都可以在下方留言和大家討論！
SUM + XLOOKUP：加總所有值
=SUM(XLOOKUP(a, b, c))
a：尋找值
b：尋找範圍
c：回傳範圍
前一個範例有說過，XLOOKUP 可以回傳陣列(Array)，而只要是陣列都可以使用基本的統計函數去運算，像是總和(SUM)、平均(AVERAGE)、標準差(STDEVA)…等。這裡我使用總和為大家做簡單的示範：計算亞洲各國2019至2021的年度人均GDP總和，並填入［G5］。
=SUM(XLOOKUP(尋找值1,尋找範圍,回傳範圍):XLOOKUP(尋找值2,尋找範圍,回傳範圍))第一個參數尋找值1，要尋找「2019」至「2021」，這裡填入起始的 2019。
第二個參數尋找範圍，要尋找尋找值 位於資料範圍，在這是年份，因此填入 B1:D1。
第三個參數回傳範圍，要回傳的對應資料，要取得GDP值，所以填入B2:D11。
共選取了三欄(B, C, D欄)，因此回傳值也會有三欄資料。
第四個參數尋找值2，要尋找「2019」至「2021」，這裡填入結束的 2021。
尋找值1 和尋找值2 分別所對應的 XLOOKUP 皆會回傳一個陣列(Array)，如下圖所示(尋找值1 的回傳值)。
將兩個 XLOOKUP 用冒號(:)*來連接。使用SUM函數將兩者包起來，就可以將冒號所連結的陣列(Array)內每一個值加總起來：計算亞洲各國2019至2021的年度人均GDP總和，並填入［G5］。如果想算平均值，只需要把 SUM 函數改成 AVERAGE 即可。
*Excel中冒號(:)代表連續性的資料。如 B1:B10 代表［B1］到［B10］這10個儲存格。而 B1:C10 則代表［B1］到［C10］這20個儲存格。如下圖所示此題範例：冒號連接後的回傳值。
XLOOKUP：根據級距尋找資料
=XLOOKUP(a, b, c, d, [e])
a：尋找值
b：尋找範圍
c：回傳範
d：無相符資料時的回傳值
[e]：判斷模式
| [e] | 判斷模式 |
| 0 或者省略 | 完全相符。如果找不到，則回傳#N/A |
| 1 | 完全相符。如果找不到，則回傳下一個較小的值 |
| -1 | 完全相符。如果找不到，則回傳下一個較大的值 |
| 2 | 萬用字元比對 |
這裡來介紹第五個參數判斷模式(match mode)，這個也為非必填參數。這裡有個小提醒：如果要使用這個參數，前一個參數就必須填，因為函數會逐一判斷讀取參數，因此前一項的無相符資料時的回傳值，就會變成必填。所得淨額為500,000時，稅率為多少，並填入［E2］。
=XLOOKUP(尋找值,尋找範圍,回傳範圍,找不到訊息,判斷模式)第一個參數尋找值，要尋找「500,000」，因此要填入 D2。
第二個參數尋找範圍，要尋找尋找值 位於資料範圍，在這是所得級距，因此填入 $A$2:$A$6。
第三個參數回傳範圍，要回傳的對應稅率，填入$B$2:$B$6。
第四個參數找不到訊息，這裡我直接填入0，當然也可以使用文字訊息。
第五個參數判斷模式，這裡填入-1，這代表如果沒有對應到500,000的話，會去尋找下一個比較小的對應值就是［A2］的 0 所對應的 5%。假設我填入 1 ，就會去尋找下一個比較大的對應值就是［A4］的 1,260,000 所對應的 12%。
所得淨額為500,000時，稅率為多少，並填入［E2］，就可以得到答案為 0.05。
XLOOKUP：二維查詢
=XLOOKUP(a, b, XLOOKUP(c, d, e))
a：尋找值1
b：尋找範圍1
c：尋找值2
d：尋找範圍2
e：回傳範圍
最後一個常用範例，就是二維資料查詢，XLOOKUP 完美取代了 INDEX、MATCH 的複合函數功能，單靠 XLOOKUP 即可完成複雜的二維查詢。回傳台灣2019年人均GDP，並填入［J2］。
=XLOOKUP(尋找值1,尋找範圍1,XLOOKUP(尋找值2,尋找範圍2,回傳範圍))第一個參數尋找值1，要尋找「台灣」，填入 $I2。大家可以想想我的錢號($)使用方式。
第二個參數尋找範圍1，要尋找尋找值1 位於資料範圍，這裡是國家列表，$A$2:$A$14。
第三個參數尋找值2，要尋找「2019」年，填入 J$1。大家可以想想我的錢號($)使用方式。
第四個參數尋找範圍2，要尋找尋找值2 位於資料範圍，這裡是年份，$B$1:$G$1。
第五個參數回傳範圍，回傳對應GDP值，填入 $B$2:$G$14。
這樣就完成：回傳台灣2019年人均GDP，並填入［J2］。
大家可以想想看為什麼我要使用兩個 XLOOKUP ：第二個 XLOOKUP 的回傳值是一個陣列(Array)，而在把這個回傳陣列(Array)放入第一個 XLOOKUP 的第三個參數，當作第一個 XLOOKUP 的回傳範圍資料去尋找對應的值。這裡用運了上方所教 XLOOKUP 回傳列陣 的方法，這裡比較多層，大家可能要思考一下，我也會放上我的 Excel 範例檔案，供大家參考。
補充：多條件查詢
XLOOKUP 還可以做多條件的查詢，這邊我和其他多條件查詢一起寫在另篇文章：【Excel】雙條件、多條件查詢(Multiple Conditions)
Excel 範例檔案下載：Excel-XLOOKUP.xlsx
更多 Excel 文章請至 文章導覽 尋找，也可利用上方「搜尋(Search...)」功能
有任何莫非工具的問題，歡迎至「表單」提問
Excel 是不是很容易
和墨菲一起用 Excel 把工作變簡單
萬用字元比對
| 萬用字元 | 尋找 |
| ? (問號) | 任何單一字元 例如，ad?pt 會找到 “adapt” 、 “adopt” 和 “adept” |
| * (星號) | 任何字元數 例如，*land 會找到 “Switzerland” 、 “Iceland” 和 “Land” |
| ~ (波狀符號) | 若尋找值剛好為 ? 或 * 或 ~ ，須在前方多一個~符號 例如，awesome~? 會找到 “awesome?” |
Pingback: 莫非工具 | 文章導覽 - 墨菲莫非