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所示。