目前适配版本:office365
函数XLOOKUP:在某个范围或数组中搜索匹配项,并通过第二个范围或数组返回相应的项。默认情况下使用精确匹配。
XLOOKUP(**lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
第一参数lookup_value(必须):查找值。
第二参数lookup_array(必须):查找区域。
第三参数return_array(必须):要返回的结果区域。
第四参数if_not_found(可选):如果查找不到,要返回的内容;若省略则返回#N/A。
第五参数match_mode(可选):匹配方式。为0:精确匹配,如果没有,则返回#N/A为-1:精确匹配,如果未找到,则返回下一个较小的项为1:精确匹配,如果未找到,则返回下一个较大的项为2:通配符匹配。
第六参数search_mode(可选):搜索方式。为1:从第一项到最后一项进行搜索;为-1:从最后一项到第一项进行搜索;为2:二进制文件搜索,依赖于升序排列的lookup_array。如果未排序,则将返回无效的结果。为-2:二进制文件搜索,依赖于降序排列的lookup_array。如果未排序,则将返回无效的结果。
1、常规查找**
查找姓名为“爱知趣“的数学成绩:
输入公式:=XLOOKUP(G2,B:B,D:D)
2、横向查找
查找姓名为“猪爸爸”的英语成绩:
输入公式:=XLOOKUP(J2,B2:H2,B5:H5)
横向查找使用函数HLOOKUP,若使用函数VLOOKUP进行横向查找,需要借助函数TRANSPOSE转置。
3、逆向查找
查找姓名为“大鹅”的学号:
输入公式:=XLOOKUP(G2,B:B,A:A)
使用函数VLOOKUP进行逆向查找,需要借助IF{1,0}或CHOOSE{1,2}等结构。
4、屏蔽错误值查找
根据姓名查找对应数学成绩:
输入公式:=XLOOKUP(G2,B:B,C:C,””)
姓名中没有小玉,查找不到时返回错误值#N/A。使用函数VLOOKUP进行查找,需要借助函数IFERROR进行容错;而函数XLOOKUP设置第四参数可屏蔽错误值。
5、区间等级查找
根据总分查找对应的区间等级:
输入公式:=XLOOKUP(F2,B$11:B$14,C$11:C$14,,-1)
第五参数为-1:精确匹配,如果未找到,则返回下一个较小的项。例如F2单元格总分为224,在区域B11:B14中未找到224,所以返回下一个较小的项即180对应的等级“中”。
6、查找最后一次采购单价
根据品名查找其对应的最后一次采购单价:
注意是按采购日期的升序进行排列的情况下
输入公式:=XLOOKUP(E2,B:B,C:C,,,-1)
使用函数VLOOKUP进行查找,当查找区域出现两个或更多值与查找值匹配时,函数VLOOKUP返回第一次出现的对应值,即可返回第一次采购单价。若需查找最后一次采购单价,要将数据按照采购日期的降序进行排列。
XLOOKUP可直接设置第六参数为-1,从下往上进行查找。
7、查找返回多列
根据姓名查找返回其对应的语文、数学、英语成绩:
输入公式:=XLOOKUP(G2,B:B,C:E)
使用函数VLOOKUP需借助函数COLUMN实现返回多列。
8、多条件查找
根据班级和姓名查找其对应的数学成绩:
输入公式:=XLOOKUP(G2&H2,A2:A8&B2:B8,D2:D8)
使用函数VLOOKUP进行多条件查找,也需要借助IF{1,0}等等结构,而使用函数XLOOKUP是不是要简单多了呢?