Excel xlookup使用指南

xlookup语法

=XLOOKUP(查找值,查找范围,结果范围,[容错值],[匹配方式],[查询模式]

#  只有前三个是必须的，后面均可省略。

参考来源：Excel星球

1) 单条件查询

如下图所示，B:D列是数据明细，需要根据F列姓名查询相关电话号码。

Search Phone

公式如下：

G2输入公式▼ =XLOOKUP(F2,B:B,D:D)

F2是查找值，B列是查找范围，D列是结果范围，公式的意思也就是在B列查找F2，找到后返回D列对应的结果。

2) 容错查询

如下图所示，B:D列是数据明细，需要根据F列姓名查询相关电话号码，但和上一个案例所不同的是，如果查无结果，需要返回指定值：查无结果。

容错

公式如下：

G2输入公式▼ =XLOOKUP(F2,B:B,D:D,”查无”)

XLOOKUP的第4参数可以指定容错值，当查无结果时避免返回错误值#N/A，省去了外围再嵌套一个IFERROR函数。

3) 模糊条件查询

如下图所示，A:B列是数据明细，需要根据F列姓名的简称查询相关特长。这是一个模糊查询的示例，比如查找星光，对应的结果为看见星光。

模糊条件查询

公式如下：

E2输入公式▼ =XLOOKUP(““&D2&”“,A:A,B:B,”查无”,2)

XLOOKUP的查找值是"*"&D2&"*"，*是通配符，可以代替0到多个字符串，"*"&D2&"*"也就指包含D2的字符串。  
但和VLOOKUP所不同的是，XLOOKUP默认不支持通配符匹配，只有将第5参数设置为常数2时，才支持通配符匹配。
XLOOKUP的第5参数可以指定匹配方式。

3.2 Picture

4) 区间查询

如下图所示，F:G列是评分标准，60以下不及格，80以下及格等，需要根据该评分标准，对C列的成绩计算评级。

区间查询

公式如下：

D2输入公式▼=XLOOKUP(C2,F2:F5,G2:G5,””,-1)

XLOOKUP第5参数为-1，指定了匹配方式是’精确匹配或下一个较小的项’，比如查找84，找不到精确匹配，则寻找比它小的项，也就是80，然后取其对应结果:’良好’。

这儿的XLOOKUP等同于LOOKUP函数▼

=LOOKUP(C2,F:G)

但和LOOKUP所不同的是，XLOOKUP函数不要求查找区域首列数据升序排列，即便把F:G列的数据打乱了，也不妨碍它寻找’精确匹配或下一个较小的项’的计算规则▼

4.2

除此之外，XLOOKUP还支持’精确匹配或下一个较大的项’的计算规则▼=XLOOKUP(C2,F2:F5,G2:G5,””,1)第5参数指定值为1，比如查找80，找不到精确匹配，则寻找比它大的项，也就是90。

5) 查询符合条件的最后一个结果

如下图所示，A:C列是数据明细，其中日期字段升序排列。需要根据E列姓名查询相关销售额，但和前面案例所不同的是，它需要查找每个人最后一次销售额，也就是符合条件的最后一条记录。

5.1

公式如下：

F2输入公式▼ =XLOOKUP(E2,B:B,C:C,”查无”,0,-1)

XLOOKUP的第6参数可以指定查询方式，默认是从前往后找,找到即止；此外也可以从后往前找, 找到即止；如果数据源有排序，还可以执行二分法查找。

5.2

本例是寻找符合查询条件的最后一条记录，需要从后往前找, 找到即止，也就是将第6参数设置为-1。

6) 二分法查询

如下图所示，A:C列是数据源，其中姓名列有升序排序，现在需要根据E列姓名查询相关电话号码。

6.1

公式如下：

F2输入公式▼ =XLOOKUP(E2,A:A,C:C,”查无”,0,2)

第6参数指定值为2，查找方式是升序排序情况下的二分法查找。

6.2

公式如下：

F2输入公式▼ =XLOOKUP(E2,A:A,C:C,”查无”,0,2)

第6参数指定值为2，查找方式是升序排序情况下的二分法查找。

6.3

这里也可以使用公式：

=XLOOKUP(E2,A:A,C:C,”查无”)

两者相比有何不同呢？

主要是查询方式的区别。后者是从前往后找，虽然说找到即止，但效率也不是很高。后者是二分法查找，效率非常高。

比如查询看见星光，前者要从第1行开始遍历，找到第10行才找到结果，它需要找10次。而后者折半查找，只需要找3次就可以了。数据量越大后者的效率优势就越高——不过后者要求查询范围需排序处理。

7) 横向查询

如下图所示，A:D列是数据明细，需要根据F1指定的科目查询对应的成绩。

7.1

公式如下：

F2输入公式▼ =XLOOKUP(F1,B1:D1,B2:D2)

当查询范围是一个横向区域时，XLOOKUP也就可以像HLOOKUP一样，实现横向数据查询。

8) 多列数据查询

如下图所示，A:D列是数据明细，需要根据F列的姓名，查询对应的特长、电话和得分等多列数据。

8.1

公式如下：

G2输入公式▼=XLOOKUP(F2,A:

当结果范围是一个多行多列的区域时，XLOOKUP可以根据查询范围的行列特性，返回一个多行或多列的结果区域。本例中查找范围是单列(A列)，结果范围是B:D列，因此返回B:D列多列结果。

9) 交叉表查询

如下图所示，A:D列是数据明细，需要根据F列的姓名，查询对应的电话、特长和得分等多列数据。和上面的案例所不同的是，结果表的字段排列顺序和数据源不一致，也就是通常所说的交叉表查询了。

9.1

公式如下：

G2输入公式▼=XLOOKUP(F2,A2:A11,XLOOKUP(G1,B1:D1,B2:D11))

公式使用了两个XLOOKUP函数。

先说XLOOKUP(G1,B1:D1,B2:D

上面解释过，当结果范围是一个多行多列的区域时，XLOOKUP可以根据查询范围的行列特性，返回一个多行或多列的结果区域。本例中查找范围是单行(B1:D1)，结果范围是B2:D11，因此返回一个多行单列数据。

比如查找G1的值为’电话’，则返回C2:C11。以此作为第2个XLOOKUP的结果范围。

10) 多条件查询

如下图所示，A:C列是数据明细，需要根据E列的年和F列的姓名，查询对应的得分。

10.1

公式如下：

G2输入公式▼=XLOOKUP(E2&F2,A11&B2:B11,C2:C11)

XLOOKUP支持数组运算，本例中查找值为E2&F2，查找范围是年字段&姓名字段，即A11&B2:B11▼

11) 区域查询

如下图所示，A:B列是数据明细，A列日期升序排列。需要查询E1单元格指定开始日期和E2单元格指定结束日期之间的金额合计。

11.1

公式如下：

E3输入公式▼ =SUM(XLOOKUP(E1,A:A,B:B):XLOOKUP(E2,A:A,B:B))

和VLOOKUP不同，和INDEX函数相同，XLOOKUP返回的不是一个单纯的值，而是一单元格引用；因此XLOOKUP(E1,A:A,B:B)返回的是B4单元格的引用，XLOOKUP(E2,A:A,B:B)返回B8单元格的引用，B4:B8也就是目标金额区域，最后使用SUM函数求和即可。

12) 动态表查询

如下图所示，一张工作簿包含了2017年、2018年、2019年等多张工作表，现在需要根据B1单元格指定的工作表名称，在其中查询A列相关人名的得分。

12

公式如下：

B4输入公式▼=XLOOKUP(A4,INDIRECT(B1&”!A:A”),INDIRECT(B1&”!B:B”))

公式使用INDIRECT函数根据B1单元格指定的工作表名称构建引用范围，其中查找范围是指定表的A列，结果范围是指定表的B列。