时间:2017年8月2日
地点:广州市白云区国际大酒店
参赛者:『仰望~星空』、『Coffee』、『君柳』
主持人:『L-L-X』、『花花』
评委:全体围观吃瓜观众
拍摄:『蜂鸟』、『小鸟』
男:尊敬的各位领导、各位嘉宾
女:亲爱的观众朋友、媒体朋友
合:大家晚上好!
男:非常感谢大家百忙之中来参加【爱上Excel合伙人】举办的一年一度函数技能PK赛,我是今晚的主持人L-L-X。
女:我是今晚的主持人花花,首先我非常荣幸的向大家介绍,光临本次比赛的嘉宾和评委,他们是……。
男:参赛者『仰望~星空』、『Coffee』、『君柳』分别是VLOOKUP、LOOKUP和INDEX+MATCH三大查找函数的代表。
女:她们将在此次比赛中用各自代表的函数完成所有比赛。
男:此次比赛本着“学习第一,比赛第二”的原则,互相切磋,取长补短。
女:目的是为了让大家明白这三大查找函数各自的特点,从而在遇到问题时能使用更合适的、更高效的方法去解决问题。
男:本次比赛共十场
第一场:常规查找
第二场:查找返回多列数据
第三场:通配符查找
第四场:带“~”的查找
第五场:区间等级查找
第六场:逆向查找
第七场:交叉查询
第八场:合并单元格的引用问题
第九场:多条件查找
第十场:一对多查找
女:谁胜谁负由全体围观吃瓜观众投票选出
男:下面我宣布
合:比赛正式开始
第一场
女:下面进入的是我们的第一场比赛:常规查找。
请看题:查找出Leaf的爱好是什么?
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP(F2,A2:D8,4,0)
LOOKUP
公式:
=LOOKUP(1,0/(F2=A2:A8),D2:D8)
INDEX+MATCH
公式:
=INDEX(D2:D8,MATCH(F2,A2:A8,0))
女:我宣布第一场比赛结束。
男:第一场题目简单,三大函数不费吹灰之力,轻松解决。
第二场
女:接下来是第二场比赛,查找返回多列数据。
请看题:根据姓名按顺序查找相关信息
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP($F2,$A2:$D8,COLUMN(B1),0)
LOOKUP
公式:
=LOOKUP(,0/($F2=$A2:$A8),B2:B8)
INDEX+MATCH
公式:
=INDEX(B2:B8,MATCH($F2,$A2:$A8,0))
女:我宣布第二场比赛结束。
男:第二场比赛也已经结束,三大函数之间的差距貌似还没有拉开。
第三场
女:即将开始我们的第三场比赛,通配符查找。
请看题:根据简称查找对应的应付账款
男:参赛者请作答
VLOOKUP
公式:
=IFNA(VLOOKUP('*'&D2&'*',A$2:B$6,2,),'')
LOOKUP
公式:
=IFNA(LOOKUP(,0/FIND(D2,$A$2:$A$6),B$2:B$6),'')
INDEX+MATCH
公式:
=IFNA(INDEX(B$2:B$6,MATCH('*'&D2&'*',A$2:A$6,0)),'')
女:我宣布第三场比赛结束。
男:这场比赛中她们都请了帮手函数IFNA进行容错,而Lookup函数不支持通配符使用,运用了Lookup+Find组合。
第四场
女:比赛仍在火热进行中,第四场带“~”的查找。
请看题:根据姓名查找地区
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP(SUBSTITUTE(F2,'~','~~'),A2:B7,2,0)
LOOKUP
公式:
=LOOKUP(1,0/(F2=A2:A7),B2:B7)
INDEX+MATCH
公式:
=INDEX(B2:B7,MATCH(SUBSTITUTE(F2,'~','~~'),A2:A7,))
女:我宣布第四场比赛结束。
男:波形符(~)作为通配符,在查找包含其本身的值时,需在“~”前键入“~”,函数VLOOKUP和INDEX+MATCH都借助了外援函数SUBSTITUTE将“~”替换成“~~”,而函数LOOKUP却能不借助外援,轻松应对。
第五场
女:第五场是区间等级查找。
请看题:根据成绩查找对应等级
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP(B2,E$2:F$5,2)
LOOKUP
公式:
=LOOKUP(B2,E$2:F$5)
INDEX+MATCH
公式:
=INDEX(F$2:F$5,MATCH(B2,E$2:E$5,1))
女:我宣布第五场比赛结束。
男:该场比赛中VLOOKUP省略了第四参数,近似匹配;
LOOKUP使用了数组形式,注意数组中的值是按升序排列;
INDEX+MATCH组合中MATCH使用了模糊查找,查找小于或等于查找值的最大值,查找区域是按升序排列。
女:比赛到目前为止已经进行了一半,前半场轰轰烈烈,后半场也将更加精彩!
男:大家可以稍事休息,听听歌,左手土豆,右手L-L-X,喝着咖啡,顶着星空,左手搭右手喝着咖啡再抬头仰望星空
第六场
女:一段时间的休息之后请大家回到各自的座位,接下来是第六场比赛,逆向查找。
请看题:根据爱好查找相关信息
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP($F2,CHOOSE({1,2},$D2:$D7,A2:A7),2,0)
LOOKUP
公式:
=LOOKUP(,0/($F2=$D2:$D7),A2:A7)
INDEX+MATCH
公式:
=INDEX(A2:A7,MATCH($F2,$D2:$D7,))
女:我宣布第六场比赛结束。
男:该场赛题似乎给函数VLOOKUP出了难题,她不得不借助外援,比如请出了CHOOSE({1,2})。
第七场
女:第七场比赛,交叉查询。
请看题:根据业务员和月份查找对应的销售额
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP(F2,A2:D7,MATCH(G2,A1:D1,0),0)
LOOKUP
公式:
=LOOKUP(,0/(A2:A7=F2),OFFSET(A2:A7,,MATCH(G2,B1:D1,0)))
INDEX+MATCH
公式:
=INDEX(B2:D7,MATCH(F2,A2:A7,),MATCH(G2,B1:D1,))
女:我宣布第七场比赛结束。
男:INDEX+MATCH组合似乎就是为该种查询而生。
第八场
女:第八场是合并单元格的引用问题。
请看题:根据客服引用对应的直播课程
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP('座',OFFSET(A2,,,MATCH(G2,B2:B16,)),1,1)
LOOKUP
公式:
=LOOKUP('座',OFFSET(A2,,,MATCH(G2,B2:B16,)))
INDEX+MATCH
公式:
=INDEX(A2:A16,MATCH('座',OFFSET(A2,,,MATCH(G2,B2:B16,)),1))
女:我宣布第八场比赛结束。
男:该场比赛中她们三心有灵犀似的都用了帮手OFFSET与MATCH。
第九场
女:比赛越来越激烈了,第九场多条件查找。
请看题:根据业务员和区域查找对应的销售额
男:参赛者请作答
VLOOKUP
公式:
=VLOOKUP(E2&F2,IF({1,0},A$2:A$9&B$2:B$9,C$2:C$9),2,0)
数组公式,按Ctrl+Shift+Enter>三键结束。
LOOKUP
公式:
=LOOKUP(,0/(E2&F2=A$2:A$9&B$2:B$9),C$2:C$9)
INDEX+MATCH
公式:
=INDEX(C$2:C$9,MATCH(E2&F2,A$2:A$9&B$2:B$9,))
数组公式,按Ctrl+Shift+Enter>三键结束。
女:我宣布第九场比赛结束。
男:多条件查找都运用了连接符“&”,用VLOOKUP和INDEX+MATCH都是数组公式。
第十场
女:接下来是我们的最后一场比赛,一对多查找。
请看题:根据条件查找出对应的所有地区
男:参赛者请作答
VLOOKUP
公式:
=IFERROR(VLOOKUP(D$2&ROW(A1),IF({1,0},A$2:A$10&COUNTIF(INDIRECT('A2:A'&ROW($2:$10)),D$2),B$2:B$10),2,),'')
数组公式,按Ctrl+Shift+Enter>三键结束。
LOOKUP
公式:
=IFERROR(LOOKUP(,0/(D$2&ROW(A1)=A$2:A$10&COUNTIF(INDIRECT('A2:A'&ROW($2:$10)),D$2)),B$2:B$10),'')
INDEX+MATCH
公式:
=IFERROR(INDEX(B$2:B$10,MATCH(D$2&ROW(A1),A$2:A$10&COUNTIF(INDIRECT('A2:A'&ROW($2:$10)),D$2),)),'')
数组公式,按Ctrl+Shift+Enter>三键结束。
女:我宣布第十场比赛结束。
男:果然好戏在后头,这一场比赛看把我紧张的汗都流出来了,没想到她们都能解决。
女:LOOKUP面对此题仍面不改色,轻松拿下,而VLOOKUP和INDEX+MATCH都是数组公式,要按
男:不管黑猫白猫,抓到老鼠就是好猫,虽然历经波澜,借助外援,但总归还是解决了问题。
女:到目前为止,十场比赛都已经圆满结束,接下来是我们的投票环节。
男:请所有评委本着公平公正公开的原则为本次比赛投上您神圣的一票,选出您心目中认为能高效解决您问题的一个。
有关函数VLOOKUP、LOOKUP和INDEX+MATCH的详细介绍,请点击下面链接跳转:
也可以通过如下方法查找你想要的资料:
作者:仰望~星空
联系客服