Excel | XLOOKUP函數的八大用法

韓老師自己的書，納入國家十三五與十四五規劃教材：

【問題】

在Excel2019及以前的版本中，如果用VLOOKUP實現逆向查找、多條件查找、從下向上查找以及如果查找不到則返回特定值等功能，是比較麻煩的，而在Excel2021版本中新增的XLOOKUP函數可以輕而易舉的實現這些功能。

【函數簡介】

功能：按行查找表或區域中的項。

語法：=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])。

中文語法：=XLOOKUP(要搜索的值,要搜索的數組或區域, 要返回的數組或區域,未找到返回值返回的指定文本, 匹配類型,搜索模式)。

• lookup_value：必需，要搜索的值，如果省略，將返回搜索的數組或區域中的空白單元格。

• lookup_array：必需，搜索值所有的數組或區域。

• return_array：必需，返回值所在的數組或區域

• if_not_found：可選，如果未找到有效的匹配項，需要提供的返回文本。如果如果未提供返回文本，則返回#N/A。

• match_mode：可選，指定匹配類型。類型有四種：

• 0：完全匹配。如果未找到，則返回#N/A。這是默認選項。

• -1：完全匹配。如果沒有找到，則返回下一個較小的項。

• 1：完全匹配。如果沒有找到，則返回下一個較大的項。

• 2：通配符匹配，其中*,?和~有特殊含義。

• search_mode：可選，指定要使用的搜索模式。模式有四種：

• 1：從第一項開始執行搜索，這是默認選項。

• -1：從最后一項開始執行反向搜索。

• 2：按升序排序的二進制搜索。如果未排序，將返回無效結果。

• -2：按降序排序的二進制搜索。如果未排序，將返回無效結果。

其中后兩種搜索模式的執行，依賴于返回值所有數據或區域的排序方式。

【實現方法】

1）基本查找

在G3單元格輸入公式“=XLOOKUP(F3,B3:B15,D3:D15)”，按Enter鍵，完成運算,即可即可查找到F3單元格內指定姓名得分，如圖B-1所示。

2）逆向查找

在G3單元格輸入公式“=XLOOKUP(F3,B3:B15,A3:A15)”，按Enter鍵，完成運算，即可即可查找到F3單元格內指定姓名所屬部門，如圖B-2所示。

查找值所有的“姓名”列，在信息表中，位于返回值“所屬部門”的右側，這種返回值位于查找值左側的查找方式稱為逆向查找。

3）查找錯誤

在G3單元格輸入公式“=XLOOKUP(F3,B3:B15,C3:C15,"查無此人")”，按Enter鍵，完成運算，如圖B-3所示。

在信息表“姓名”列中，沒有查找值“徐五”，指定返回值為“查無此人”。

4）模糊查找

在E2單元格輸入公式“=XLOOKUP("G"&"*",A2:A8,B2:B8,,2)”，按Enter鍵，完成運算，即可查找出開頭為“G”型號系列的銷量，如圖B-4所示。

公式中的"G"&"*"，表示以“G”開頭的型號系列。公式第5個參數為“2”，即按通配符進行數據匹配。

5）區間查找

對成績劃分等級，劃分等級的標準是：85分及以上為優秀、70到84分為良好，60到69分為合格，60分以下為不合格。

在C2單元格輸入公式“=XLOOKUP(B2,{0,60,70,85},{"不合格","合格","良好","優秀"},,-1)”，按Enter鍵，完成運算，并將公式向下填充，即得所有成績對應的等級，如圖B-5所示。

公式中的第5個參數為“-1”，即按完全匹配，如果沒有找到成績對應的等級，則返回下一個較小成績對應的等級。

6）從下向上查找

在E2單元格輸入公式“=XLOOKUP(D2,$A$2:$A$18,$B$2:$B$18,,,-1)”，按Enter鍵，完成運算，并將公式向下填充，即得所有商品對應的最大進貨數量，如圖B-6所示。

公式中的第6個參數為“-1”，從最后一項開始執行自下而上搜索。

特別注意：此時的數據表中的“進貨數量“一定是按照自小而的升序排列的。

7）多條件查找

在G2單元格輸入公式“=XLOOKUP(E2&F2,A2:A13&B2:B13,C2:C13)”，按Enter鍵，完成運算，即得指定倉庫指定商品的進貨數量，如圖B-7所示。

8）多行多列查找

在C18單元格輸入公式“=XLOOKUP(B18,$C$3:$C$15,$D$3:$G$15)”，按Enter鍵，完成運算，即得指定姓名的各項信息，如圖B-8所示。