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

今天跟大家分享最近非常流行的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函数