|
一、语法和参数 1. 语法： =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 语法解释：=XLOOKUP(查找值,查找数组,返回数组,未找到值,匹配模式,搜索模式) 2. 参数 1. 必需参数三个： ① lookup_value，要搜索的值； ② lookup_array，要搜索的区域或数组； ③ return_array，要返回的区域或数组。 2. 可选参数三个： ① [if_not_found]，找不到匹配值； 返回指定参数[if_not_found]； 如果未指定参数，则显示#N/A； ② [match_mode]，指定匹配类型； 0 未找到匹配值，则显示#N/A -1 未找到匹配值，则返回较小值 1 未找到匹配值，则返回较大值 2 通匹符 ③ [search_mode]，指定搜索模式。 1 从第一项开始搜索 -1 从最后一项开始搜索 2 按升序搜索 -2 按降序搜索 三、函数示例
1. 纵向查找
以下图表格为例，根据姓名查找得分。可以输入公式：
=XLOOKUP(D2,A2:A11,B2:B11) 整个公式的含义是，使用XLOOKUP函数，查找“林冲”在姓名列的位置，并返回得分列的相应位置对应的得分。 如果使用VLOOKUP函数的话，公式为： =VLOOKUP(D2,A2:B11,2,0) 2. 横向查找 以下图表格为例，姓名行在上面，得分行在下面。根据姓名查找得分。可以输入公式： =XLOOKUP(A6,A1:K1,A2:K2) 公式说明：查找值H2，查找区域为B1:E1，返回区域为B2:E2。 VLOOKUP函数不能横向查找，如果使用HLOOKUP函数的话，公式为： =HLOOKUP(A6,B1:K2,2,0) 3. 逆向查找
如果需要查找的目标值在左边，需要进行逆向查找。如下图，姓名列在右边，得分列在左边。根据姓名从右向左查找得分。单元格G2，输入公式： =XLOOKUP(F2,C2:C5,B2:B5) 公式说明：查找值F2，查找区域为C2:C5，返回区域为B2:B5。 使用VLOOKUP函数也能实现逆向查找，但是比较复杂，公式如下： =VLOOKUP(E2,IF({1,0},$C$2:$C$11,$B$2:$B$11),2,0) 4. 查询失败匹配值 如果查询的结果没有匹配值，查询失败默认显示“#N/A”。如果给公式加上第四个参数：匹配值，则查询失败会显示匹配值。 如下图，要根据姓名，查找得分，输入公式： 公式1：=XLOOKUP(D3,A3:A12,B3:B12) 公式说明：查找值F2，查找区域为A3:A12，返回区域为B3:B12。这个公式因未指定第四个参数，则默认显示“#N/A”。 公式2：=XLOOKUP(D3,A3:A12,B3:B12,'无')
公式说明：查找值D3，查找区域为A3:A12，返回区域为B3:B12，未找到匹配值 则显示“无”。 5. 区间查找
如下图，要根据【分值】区间，查找【积分】。输入公式，向下填充： =XLOOKUP(D3,(ROW($A$1:$A$11)-1)*10,$B$3:$B$13,,1) 公式说明：查找值D3，查找区域为(ROW($A$1:$A$11)-1)*10（构建数组），返回区域为$B$3:$B$13，指定匹配类型1；
注意：查找区域使用函数嵌套构建数组，指定匹配类型1（即未找到匹配值，则返回较大值）； 5. 指定搜索模式（有重复值的数据查找） 我们可以指定查找模式：从第一项往后查找、从最后往前查找。 如下图，要根据姓名，查找得分，这里出现了相同值。输入公式： =XLOOKUP(E2,A2:A5,B2:B5,,,-1) 公式说明：查找值E2，查找区域为A2:A5，返回区域为B2:B5，搜索模式为-1。（因为这个参数是第六参数，所以，需要加多2个逗号“，”；这个能理解吗？） 如果不输入任何参数的话，默认是从第一项开始往后查找，结果就是94。 6. 交叉查找 如下图，要根据姓名和季度，查找交叉值。输入公式，向右填充： =XLOOKUP(H2,$B$2:$E$2,XLOOKUP($G3,$A$3:$A$12,$B$3:$E$12))
公式说明：查找值H2，查找区域为$B$2:$E$2，返回区域为XLOOKUP($G2,$A$3:$A$12,$B$3:$E$12)。 注意：因为需要向右填充，所以要注意参数的相对和绝对引用； 以上就是，XLOOKUP函数的常用的几种用法，有兴趣的朋友可以进一步研究。
|
