【查找和引用函数】XLOOKUP函数的 使用方法

XLOOKUP函数的
使用方法

此函数需更新至WPS Office最新版本使用

WPS Office官网:https://platform.wps.cn/


XLOOKUP函数是一个查找函数。

它的语法是XLOOKUP(查找值,查找数组,返回数组,未找到值,匹配模式,搜索模式)


中插页_01.jpg


微信图片_20210809150042.png

一、纵向查找

以此表格为例,若我们想查找小郑的分数。

单击「公式」选项卡-「插入函数」按钮,找到XLOOKUP函数。

在「查找值」中输入小郑姓名单元格E13;

「查找数组」中输入需要查找的数组姓名列;

「返回数组」中输入将要返回的分数列;

整个公式的含义是,使用XLOOKUP函数,查找小郑在成绩单的位置并返回对应的分数。


2.gif



二、横向查找

VLOOKUP函数不能横向查找,此时就可以使用XLOOKUP函数。

以此表格为例,若我们想查找小周的分数。

在「查找值」中输入小周姓名单元格B7,「查找数组」中输入需要查找的数组姓名行

「返回数组」中输入将要返回的分数行。

点击确定,就可以横向查找小周的分数了。


2.2.gif


三、反向查找

VLOOKUP函数不能反向查找,此时就可以使用XLOOKUP函数。

若我们想查找学号1103是哪位同学。

在「查找值」中输入学号单元格;「查找数组」中输入需要查找的学号列;「返回数组」中输入将要返回的姓名列,点击确定,就可以反向找到学号1103的同学是哪位了。


5.gif


四、整行查找

XLOOKUP函数可以查找整行数据。若我们想查找小王同学的学号、考试成绩、

首先选中G4和H4需要填写数据的单元格区域、

在「查找值」中输入小王单元格;

「查找数组」中输入需要查找的姓名列;「返回数组」中输入将要返回的学号与成绩列。

最后使用Ctrl+Shift+Enter键,使用数组填充的方式,将学号与考试成绩批量填充到单元格区域中。

3.gif


五、整列查找

XLOOKUP函数可以查找整列数据,若我们想查找这四位同学的成绩。

首先选中G3至G6需要填写数据的单元格区域。

在「查找值」中输入四位同学的单元格区域;「查找数组」中输入需要查找的姓名列;「返回数组」中输入将要返回的成绩列。

最后使用Ctrl+Shift+Enter键,使用数组填充的方式,就可以将成绩批量填充到单元格区域中。

4.gif

六、倒序查找

XLOOKUP函数可以根据查找值在返回数组中倒着查找。

演示文稿1_01.jpg

以此表格为例,若想知道蔡秋水同学最后一次的铅球成绩。

在「查找值」中输入查找名字;「查找数组」中输入需要查找的姓名列;「返回数组」中输入将要返回的分数列;在「搜索模式」中输入参数对应的参数。

「搜索模式」参数如图所示,因为我们想要查找最后一次成绩,所以选择倒序查找搜索模式,输入-1。

点击确定,就可以得出蔡秋水同学最后一次的跳高成绩。

6.gif


七、多条件查找

XLOOKUP函数支持多条件查找,想要查找高一(1)班参加跳高项目的同学名字。

在「查找值」中输入查找条件,高一(1)班&跳高,并用&将两个条件连接。

「查找数组」中输入需要查找班级列和项目列,并用&将两个查找区域连接。

「返回数组」中输入将要返回的姓名列。

就可以找到高一(1)班参加跳高项目的同学名字了。

7.gif


八、通配符查找

XLOOKUP函数可以使用通配符进行查找,若我们想查找1班李同学的报名项目。

在「查找值」中输入*李*&*1*,*是通配符的一种符号,代表任意字符。

关于通配符的更多用法可以在WPS学堂里搜索学习。

「查找数组」中输入姓名列和班级列,「返回数组」中输入将要返回的项目列。

在「匹配模式」中输入参数对应的参数。

中插页_02.jpg

「匹配模式」参数如图所示,因为我们想要通过通配符查找,所以输入参数2。

点击确定,就可以得出李同学的报名项目。

8.gif



九、交叉行列查找

当遇到表格行列过多,无法精准的判断返回条件的区域,可以使用XLOOKUP函数行列交叉查找。

如我们先知道徐传民同学在保龄球项目中,第6次的成绩。

输入公式=XLOOKUP,在查找值中输入“徐传民”单元格;在查找区域中输入次数区域。

在返回数组中,应该填写需要返回的行或列的区域,但是由于数据过多,无法判断返回数组的区域位置,可以再嵌套一层XLOOKUP函数,输入XLOOKUP(E12,C2:L2,C3:L9)。

意思是用XLOOKUP函数查找6次在次数行的位置,并返回对应的分数区域。

这样就可以查找到徐传民在保龄球项目中,第6球的分数了。

9.gif


十、匹配模式

XLOOKUP函数语法中的「匹配模式」可以帮助我们更好的进行表格查找。

「匹配模式」的参数如表所示:下面就给大家演示如何使用匹配模式,匹配最小项。

以保龄球分数表格为例,那如何根据评分规则,对分数进行评判呢。

选中需要填写的单元格区域,输入公式=XLOOKUP

「查找值」中输入总分区域列;

「查找数组」中输入评分规则的分数区域列;「返回数组」中输入要返回的等级列。

在「匹配模式」中输入参数对应的参数。

中插页_02.jpg

「匹配模式」参数如图所示,因为我们想要匹配最小项,所以输入参数-1。

最后使用数组快捷键Ctrl+Shift+Enter批量填充到单元格区域中。

10.gif


十一、查找不到返回指定值

当使用VLOOKUP函数进行查找时,当表格中不包含查找值会返回#N/A。

而XLOOKUP函数可以直接返回为指定内容。

以此表格为例,若我们想查找王安妮的报名项目。

在「查找值」中输入需要查找条件;「查找数组」中输入需要查找的姓名列;「返回数组」中输入将要返回的项目列;「未找到值」中输入未找到值时显示的内容。

这样,当报名表中没有找到王安妮的报名信息,就会返回指定的文本内容了。

11.gif



广东珠海
浏览 115098
78
90
分享
90 +1
33
78 +1
全部评论 33
 
li
请教下,=VLOOKUP(A2&"*",数据源!B:E,4,0) 可以找出数据,为什么 =XLOOKUP(A2&"*",数据源!B:B,数据源!E:E) 出来是N/A?
· 湖南省
回复
 
糖
练习文档怎么下载不了
· 陕西省
回复
 
陈常敬
这个必须得给一个赞,给我们的帮助太大了!
· 山东省
回复
 
赵二
非常实用!
· 辽宁省
1
回复
赵二
收藏学习。
· 辽宁省
1
回复
 
MTS
5分25秒处,返回的应该是1班李同学的项目,而不是5班
· 广西
1
回复
 
Wendy
为什么没有显示搜索模式?
· 广东省
1
回复
 
WAT
为什么课程示例文档和练习文档不一致
· 陕西省
3
回复
 
简易
· 贵州省
1
回复
 
๑ŐεŐ๑
打卡
· 广东省
1
回复
 
暖°凌落
这个公式遇到无效引用怎么办呢?有大神解答下吗
· 广东省
1
回复
微达
+1
· 海南省
回复
 
心灵
什么情况下数组需要添加大括号,什么情况不需要添加大括号
· 山西省
1
回复
 
WPS_1652601789
练习文档不全
· 重庆
1
回复
 
子非鱼
这个文字解说和操作不符
· 广东省
1
回复
 
百合花
“查找1班李同学的报名项目”,如果1班李同学参加了多个项目,那么这个公式就只能显示出1班李同学参加的第一个项目,其它的项目怎么才能都显示出来呀?
· 湖北省
2
回复
紫欣正在路上~
这时候应该要用到filter函数了
· 广东省
1
回复
 
亂雲飛渡
· 广东省
1
回复
 
典雅书业 WPS
查找结果出现#SPILL! 怎么办
· 河南省
2
回复
 
劲峰
要是有素材就更好了
· 河北省
1
回复
初心
有素材的,https://www.wps.cn/learning/course/detail/id/330330.html 不过好像有点区别
· 山东省
1
回复
 
Ozyman
你这是更新了啥啊,之前都能用的,现在用不了了
· 广东省
2
回复
 
焦玲玲
如果交叉查找 ,同一个人有好几个分数,且不是按顺序排列的,怎样显示最大数或最小数
· 河南省
1
回复
紫欣正在路上~
涉及排序可能要用到sort函数
· 广东省
1
回复
 
飘飘荡荡无处安放
跨表格搞不好了,以前可以的
· 湖北省
2
回复