Excel新查引用函数xlookup用法详解
一、概念
XLOOKUP函数是Excel中最常用的函数之一，它是一个查函数，用于在一列或一行的数据范围内按照指定的条件进行查匹配，并返回对应的值。
二、功能
XLOOKUP函数的主要功能是在指定的数据范围内按照指定的条件进行查匹配，并返回对应的值。它可以实现以下典型功能：按照指定的条件进行查匹配，返回对应的值，如果匹配不到，则返回指定的默认值。
三、语法
XLOOKUP函数的语法如下：
XLOOKUP(lookup_value, lookup_range, col_index_num, [match_mode])
参数解析：
lookup_value：要查的值；
lookup_range：要查的数据范围；
col_index_num：返回的数据列数；
match_mode：匹配模式，可选参数，默认为0。
四、案例
下面通过一个具体的案例来演示XLOOKUP函数的使用方法。
假设我们有以下的数据表格，其中A列是姓名，B列是年龄，C列是性别：
A
B
C
1
张三
25
男
2
李四
30
女
3
王五
28
男
4
赵六
lookup函数返回值不对22
女
5
孙七
35
男
现在，我们想要查年龄为28岁的人的性别。可以使用以下公式：
=XLOOKUP(28, B2:B5, 3)
这个公式的意思是：在B2:B5这个数据范围内查值为28的数据行，并返回对应行的第3列（即C列）的值。如果到了匹配的行，则返回对应的值；如果没有到匹配的行，则返回#N/A。在这个例子中，公式会返回“男”。
五、注意事项
1.XLOOKUP函数只能在一列或一行的数据范围内进行查匹配，不能在一个多列或多行的数据范围内进行查匹配；
2.如果要查的数据范围是按照行排列的，则需要使用转置函数将其转换为按照列排列；
3.如果要查的数据范围是按照列排列的，则需要使用HLOOKUP函数；
4.如果要查的数据范围是按照行和列排列的，则需要使用INDEX和MATCH函数组合。
六、高级用法
案例一：多条件查
假设我们现在要根据「姓名」和「部门」两个条件，查询每个人的「在职状态」。可以在J4单元格输入以下公式：
=XLOOKUP(I4&J4, C4:C19&B4:B19, E4:E19)
这个公式的意思是：要查的值为I4和J4的组合，查区域为C4:C19和B4:B19的组合，返回的区域为E4:E19。
案例二：逆向查
假设要根据「部门」查「姓名」。由于姓名是已知信息，部门是未知信息，因此需要将查值和查范围互换位置。具体操作如下：
在J4单元格输入以下公式：
=XLOOKUP(J4, B4:B19, C4:C19)
这个公式的意思是：查值为J4，查范围为B4:B19，返回值是C4:C19列中与J4匹配的行。
如果查值为空，则会返回最后一条记录。例如，在J5单元格中输入以下公式：
=XLOOKUP(I5, B5:B20, C5:C20, 0, -1)
这个公式的意思是：查值为I5，查范围为B5:B20，返回值是C5:C20列中与I5匹配的行。由于匹配模式为0和-1，因此如果查值不存在，则会返回最后一条记录。
案例三：模糊查
假设要查姓名中包含“张”的所有员工的状态。可以使用通配符“*”实现模糊查。具体操作如下：
在J4单元格输入以下公式：
=XLOOKUP("*张*", I4:I19, E4:E19, 2)
这个公式的意思是：查值为任意以“张”开头的字符串，查范围为I4:I19，返回值是E4:E19列中与查值匹配的行。由于匹配模式为2，因此可以使用通配符进行模糊查。
案例四：精确查
假设要根据「姓名」查询每个人的「在职状态」，并且要求姓名完全匹配。可以使用MATCH函数实现精确查。具体操作如下：
在J4单元格输入以下公式：
=XLOOKUP(I4, C4:C19, E4:E19, 0)
这个公式的意思是：查值为I4，查范围为C4:C19，返回值是E4:E19列中与I4完全匹配的行。由于匹配模式为0，因此只有当查值完全匹配时，才会返回对应的值。
案例五：横向查
XLOOKUP函数通常用于纵向查，但也可以进行横向查。要进行横向查，需要将查范围和返回值范围转置，即将它们转换为列向量而不是行向量。可以使用转置函数实现此目
的。
假设要在以下表格中根据姓名查对应的年龄和性别：
A
B
C
1
姓名
年龄
性别
2
张三
25
男
3
李四
30
女
4
王五
28
男
5
赵六
22
女
6
孙七
35
男
要查姓名为“王五”的人的年龄和性别，可以使用以下公式：
=XLOOKUP("王五", A2:A6, B2:D6, 2)
这个公式的意思是：在A2:A6范围内查值为“王五”的行，并返回对应的B2:D6范围内的第2
列的值，即年龄。由于XLOOKUP函数只能进行纵向查，因此需要将B2:D6转置为列向量。可以使用转置函数实现此目的。
案例六：区间查
XLOOKUP函数还可以进行区间查，即根据一个值查它所在的区间，并返回该区间的值。要进行区间查，需要使用XLOOKUP函数的第四个参数，即匹配模式。匹配模式为1表示升序匹配，为-1表示降序匹配。如果查值在查范围的左侧或右侧，则返回小于或大于查值的最近的值。
假设有以下数据表格，其中A列是分数，B列是等级：
A
B
1
分数
等级
2
60
及格
3
70
中等
4
80
良好
5
90
优秀
要根据分数查等级，可以使用以下公式：
=XLOOKUP(75, A2:B5, 2, 1)
这个公式的意思是：在A2:B5范围内查小于等于75的最大值，并返回对应的等级。由于匹配模式为1，因此XLOOKUP函数将按照升序匹配。如果分数小于60，则返回“不及格”；如果分数大于等于90，则返回“优秀”。
案例七：跨表查
XLOOKUP函数还可以进行跨表查，即在不同的工作表中查数据。要进行跨表查，需要使用INDIRECT函数将工作表名转换为单元格引用。INDIRECT函数的语法如下：
INDIRECT(ref_text, [a1_style])
其中，ref_text是要转换的单元格引用或工作表名，a1_style是一个可选参数，用于指定单元格引用的样式。如果a1_style为0或省略，则使用A1样式；如果a1_style为1，则使用R1C1样式。
假设有两个工作表，一个名为“数据”，一个名为“结果”。要在“结果”工作表中根据“数据”工作表中的姓名查对应的年龄和性别，可以使用以下公式：
=XLOOKUP(A2, INDIRECT("数据!A2:A6"), INDIRECT("数据!B2:D6"), 2)
这个公式的意思是：在“数据”工作表的A2:A6范围内查值为A2单元格中的姓名的行，并返回对应的B2:D6范围内的第2列的值。由于需要在不同的工作表中查数据，因此需要使用INDIRECT函数将工作表名转换为单元格引用。
以上就是Excel新函数Xlookup的常见用法，希望对您有所帮助！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。