一、语法和参数

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函数的常用的几种用法，有兴趣的朋友可以进一步研究。

图片

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。