Excel无所不能的XLOOKUP,XLOOKUP函数不同场景的应用方法

文章介绍了XLOOKUP函数在Office365中的应用,通过6个实际案例展示了它如何替代并超越VLOOKUP和LOOKUP函数,包括单一条件查找、通配符查找、反向查找、横向查找、多条件查找和查找最新日期的数据。XLOOKUP的灵活性和功能强大使其成为新的热门选择,即使在WPS表格和Office低版本中可通过VBA自定义实现。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

今天跟大家分享最近非常流行的XLOOKUP函数,Office365体验版中的新函数。XLOOKUP函数之所以能流行起来是因为它兼容了VLOOKUP/LOOKUP/HLOOKUP等多个函数的功能,说XLOOKUP是集才华于一身一点也不过分。吹捧了半天相信大家已经等不及要认识他了吧,一起来学习一下吧!


 

注:WPS表格和Office低版本是没有这个函数的。

下面就让我们用6个工作中的案例来讲解一下这个函数的用法吧!大家来看看谁更胜一筹?

案例1:根据姓名查找对应年龄

单一的条件根据姓名查找对应的年龄数据,VLOOKUP第一个申请出战,还是熟悉的配方,熟悉的味道。直接在H4单元格中输入公式=VLOOKUP(G4,C4:E11,3,0)就可以查找出对应姓名的年龄。


 

看完了VLOOKUP函数的表演,LOOKUP函数也给大家展示了一把,同样是在H4单元格中输入公式=LOOKUP(1,0/(C4:C11=G4),E4:E11)也是可以查找对应姓名的年龄。

 

XLOOKUP看了前面两位仁兄的表演后,默默的在H4单元格中写下了公式=Xlookup(G4,C4:E11,E4:E11)。


 

随后XLOOKUP函数来了一个延伸板的动态查找,因为XLOOKUP函数的查找值第一参数可以是一个值,也可以是一组值,所以直接写成=Xlookup(G4:G6,C4:E11,E4:E11)就可以批量查找出多个姓名对应的年龄,此技巧对VLOOKUP和LOOKUP而言算不上什么伤害,因为常规大家写完公式都会下拉公式填充,都一样可以查找出多个字段值。

 

第一轮回合结束后虽然三个函数打成了平手,但是XLOOKUP明显略胜一筹。接着我们来看看第二个回合。

案例2:查找姓名中包含“二”的年龄

第二回合是通配符查找的案例,VLOOKUP函数在面对通配符*和?查找出来的结果是不一样的,因为*是代表对个内容的通配符,而?是代表单个字的通配符,如下图:


 

案例中的姓名名称有两位和三位不等的存在,当查找通配符带*的时候就会查找到“申德二”对应的年龄,查找通配符带?对应的姓名就是“满二”的年龄。

XLOOKUP函数也可以兼容通配符查找,不过XLOOKUP函数查找的通配符遇到多个结果时返回的是第一个结果值。


 

案例3:根据姓名从右向左查询部门

VLOOKUP函数反向查找需要使用的IF(1,0)这个我们在前面的文章有过专门的讲解,直接在H4单元格中输入公式=VLOOKUP(G4,IF({1,0},C4:C11,B4:B11),2,0)


 

LOOKUP函数面对不管从左往右还是从右往左查找,匹配条件列是动态可调整的,这点LOOKUP较比VLOOKUP灵活度高。说完后LOOKUP函数在H4单元格写下公式=LOOKUP(1,0/(C4:C11=G4),B4:B11)

两位前辈在前面表演完后接下来XLOOKUP也不藏着掖着了,非常熟练的在H4单元格写下公式=Xlookup(G4,C4:C11,B4:B11)


 

第三轮如果是从公式的理解和长短上来评价,XLOOKUP胜!因为公式越长越不便于理解记忆。

案例4:根据部门查找对应人数

第四回合是考验大家横向查找的应变能力,此时VLOOKUP函数把他的好搭档HLOOKUP叫来了,因为VLOOKUP擅长的是纵向查找,对于横向查找HLOOKUP函数是大家认可的“大师”。

只见HLOOKUP二话没说就在B7单元格中写下公式=HLOOKUP(B6,3:4,2,0)


 

XLOOKUP函数见对方叫来“帮手”一点都不害怕,随手也在B7单元格中写下公式=Xlookup(B6,B3:E3,B4:E4)


 

本轮回合因为VLOOKUP函数弃权叫HLOOKUP代为参加,所以XLOOKUP胜!

案例5:根据部门和姓名以及性别查找年龄

提到多条件查找,VLOOKUP函数从上一轮输了后,决定还是他自己出场,于是酝酿了好一会儿在J4单元格中写下公式=VLOOKUP(G4&H4&I4,IF({1,0},B4:B11&C4:C11&D4:D11,E4:E11),2,0)

 

LOOKUP函数前面一轮缺勤没有参加,遇到多条件查找是它的拿手技巧,于是也在J4单元格中写下公式=LOOKUP(1,0/(B4:B11=G4)*(C4:C11=H4)*(D4:D11=I4),E4:E11)


 

XLOOKUP函数见他们都写了好长一串,于是照着VLOOKUP的步骤在J4单元格中写下公式=Xlookup(G4&H4&I4,B4:B11&C4:C11&D4:D11,E4:E11)

 

第五轮大家不相上下,再次打成平手,XLOOKUP函数果然是长江后浪推前浪,和两个老“油条”函数竟然能打成平手。瞬间成为了场上大家重点看好的黑马。

案例6:查找最新日期的产品单价

由于比赛时间关系,考官出了最后一个案例就是查找最新日期的产品单价,这时VLOOKUP还在想怎么应对的时候LOOKUP函数申请出战,直接在I4单元格输入公式=LOOKUP(1,0/(C4:C11=H4),(D4:D11))

 

Xlookup见状时间不多也没保留,直接在I4单元格中写下公式=Xlookup(H4,C4:C11,D4:D11,0,-1)

 

到此六个回合的PK就结束了,大家作为考官来评价一下哪个函数更厉害?最后给大家分享一下低版本Office的同学如果不想升级软件,可以使用VBA自定义一个XLOOKUP函数哦,方法如下:

VBA自定义XLOOKUP函数

 

### XLOOKUP 函数与 HLOOKUP 函数的区别 #### XLOOKUP 函数的使用方法 `XLOOKUP` 是一种更为灵活和强大的查找函数,在 Excel 2019 及更新版本中可用。此函数允许在一个范围内搜索特定值并返回相应位置的另一个范围内的对应值。 语法如下: ```excel =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) ``` - `lookup_value`: 要查找的值。 - `lookup_array`: 查找区域。 - `return_array`: 返回结果所在的数组或单元格区域。 - `[if_not_found]`: 如果未找到匹配项,则显示的内容,默认为 #N/A 错误消息。 - `[match_mode]`: 匹配模式选项,可选参数。 - `[search_mode]`: 搜索方向设置,可选参数。 示例如下: 假设 A 列包含产品 ID,B 列包含价格列表,要基于给定的产品 ID 找到其对应的售价,可以这样写公式: ```excel =XLOOKUP(E2,A:A,B:B,"Not Found",0) ``` 这里 E2 单元格放置着待查的产品编号[^3]。 #### HLOOKUP 函数的使用方法 相比之下,`HLOOKUP` 主要是用来执行水平查找的任务,即当数据是以横向排列的方式存储时适用。该函数会在表格的第一行寻找指定的关键字,并向下移动至设定好的列数来获取目标数值。 基本结构如下所示: ```excel =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) ``` - `lookup_value`: 需要在最上面一行里定位的目标关键字。 - `table_array`: 整个二维矩阵形式的数据集。 - `row_index_num`: 表明希望从哪一行提取最终答案;注意这里的索引是从顶部算起的正整数。 - `[range_lookup]`: 是否启用近似匹配标志位,默认开启 (TRUE),通常建议关闭以获得精确的结果(FALSE)。 举例来说,如果有一个销售记录表横跨多列,其中第一行为月份名称,下面几列为不同产品的销售额度,那么就可以利用 `HLOOKUP` 来按月统计某类商品的具体收入情况[^4]。 #### 关键区别总结 - **灵活性**: `XLOOKUP` 支持双向查找(既可以垂直也可以水平),而 `HLOOKUP` 仅限于水平方向上的检索。 - **易用性**: `XLOOKUP` 提供更多自定义选项以及更直观的操作方式,简化了许多复杂的查询需求处理过程。 - **兼容性**: 对于较旧版 Microsoft Office 用户而言,可能不具备内置支持 `XLOOKUP` 功能的情况,此时仍需依赖传统的 `VLOOKUP` 或者 `HLOOKUP` 等替代方案[^5]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值