XLOOKUP函数的用法

XLOOKUP函数的用法

XLOOKUP函数，主要功能是查找匹配符合条件的数据，下面分享下，该函数的使用方法。

XLOOKUP函数的语法和参数

语法

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

参数

一、必需参数三个：

1、lookup_value，要搜索的值；

2、lookup_array，要搜索的区域或数组；

3、return_array，要返回的区域或数组。

二、可先参数三个：

1、[if_not_found]，找不到匹配值；

返回指定参数[if_not_found]；

如果未指定参数，则显示#N/A；

2、[match_mode]，指定匹配类型；

0-未找到匹配值，则显示#N/A

-1-未找到匹配值，则返回较小值

1-未找到匹配值，则返回较大值

2-通匹符

3、[search_mode]，指定搜索模式。

1-从第一项开始搜索

-1-从最后一项开始搜索

2-按升级搜索

-2-按降序搜索

以下为示例

示例一

要根据【员工】，查找【金额】。

单元格G2，输入公式：

=XLOOKUP(F2,B1:B5,C1:C5)

公式说明：查找值F2，查找区域为B1:B5，返回区域为C1:C5。

示例二

要根据【月份】，查找【金额】。

单元格H2，输入公式：

=XLOOKUP(H1,B1:E1,B2:E2)

公式说明：查找值H2，查找区域为B1:E1，返回区域为B2:E2。

示例三

要根据所有【员工】，查找所有【金额】。

单元格G2，输入公式：

=XLOOKUP(F2,$B$2:$B$5,$C$2:$C$5)

向下填充。

公式说明：查找值F2，查找区域为$B$2:$B$5，返回区域为$C$2:$C$5。

注意：公式中查找区域和返回区域为绝对引用。

示例四

要根据所有【月份】，查找所有【金额】。

单元格H2，输入公式：

=XLOOKUP(H1,$B$1:$E$1,$B$2:$E$2)

向右填充。

公式说明：查找值H1，查找区域为$B$1:$E$1，返回区域为$B$2:$E$2。

注意：公式中查找区域和返回区域为绝对引用。

示例五

要根据【员工】，逆向查找【金额】。

单元格G2，输入公式：

=XLOOKUP(F2,C2:C5,B2:B5)

公式说明：查找值F2，查找区域为C2:C5，返回区域为B2:B5。

示例六

要根据【月份】，逆向查找【金额】。

单元格H2，输入公式：

=XLOOKUP(H1,A2:E2,A1:E1)

公式说明：查找值H1，查找区域为A2:E2，返回区域为A1:E1。

示例七

要根据【员工】，查找【金额】，没有匹配值。

单元格G2，输入公式：

公式一：=XLOOKUP(F2,$B$2:$B$5,$C$2:$C$5,"not found")

公式说明：查找值F2，查找区域为$B$2:$B$5，返回区域为$C$2:$C$5，未找到匹配值显示“not found”。

公式二：=XLOOKUP(F2,$B$2:$B$5,$C$2:$C$5)

公式说明：查找值F2，查找区域为$B$2:$B$5，返回区域为$C$2:$C$5。

注意：第二个公式，因未指定第四个参数，则默认显示“#N/A”。

示例八

要根据【分值】区间，查找【积分】。

单元格E3，输入公式：

=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（即未找到匹配值，则返回较大值）；

示例九

要根据【地区】，查找【金额】，这里涉及相同值查找。

单元格F2，输入公式：

公式一：=XLOOKUP(E2,A2:A5,B2:B5)

公式说明：查找值E2，查找区域为A2:A5，返回区域为B2:B5。

公式二：=XLOOKUP(E2,A2:A5,B2:B5,,,-1)

公式说明：查找值E2，查找区域为A2:A5，返回区域为B2:B5，搜索模式为-1。

注意：公式一，省略搜索模式参数，则默认从第一项开始搜索；公式二，指定搜索模式为-1，则从最后一项开始搜索；

示例十

要根据两【地区】之间，计算所有【金额】之和

单元格F2，输入公式：

=SUM(XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5):XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,,-1))

公式说明：第一个XLOOKUP（区间起始），查找值E2，查找区域为$A$2:$A$5，返回区域为$B$2:$B$5，第二个XLOOKUP（区间终止）查找值E2，查找区域为$A$2:$A$5，返回区域为$B$2:$B$5，搜索模式为-1。

注意：这里使用函数嵌套构建区间。

示例十一

要根据【月份】和【项目】，查找交叉值

单元格G2，输入公式：

=XLOOKUP(G$1,$B$1:$D$1,XLOOKUP($F2,$A$2:$A$4,$B$2:$D$4))

向右填充

公式说明：查找值G$1，查找区域为$B$1:$D$1，返回区域为XLOOKUP($F2,$A$2:$A$4,$B$2:$D$4)。

注意：参数的相对和绝对引用；

结束

以上就是，XLOOKUP函数的用法，有兴趣的朋友可以进一步研究。