二维码 购物车
部落窝在线教育欢迎您!

VLOOKUP&LOOKUP双雄战(一):VLOOKUP的漂亮开局

 

作者:小花来源:部落窝教育发布时间:2019-04-16 09:45:48点击:1638

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

战斗的钟声再次打响, EXCEL大擂台终于迎来了查询双雄VLOOKUP和LOOKUP的世纪大战!在接下来的八个回合斗战中,是查询巨星VLOOKUP成功捍卫荣誉守住擂台,还是野性十足的LOOKUP首次登顶擂主一炮走红?且让我们拭目以待!

EXCEL函数江湖烽烟再起,函数大擂台迎来两位重量级选手。守擂者是号称全民偶像、人见人爱车见车载的巨星级函数VLOOKUP,挑战者则是名气不大实力强劲高手LOOKUP函数!这对与生俱来的对手,究竟会在函数擂台上擦出怎样的火花?青梅煮酒论英雄,让我们拭目以待!

ROUND 01  基本用法比拼

VLOOKUP是在表格或区域中按列查找内容的函数,它的基本语句是:

=VLOOKUP(查找值,查找区域,返回值的列号,精确/近似匹配 )

其中:参数1必须出现在参数2的首列,参数3必须在参数1和参数2的列数之间,参数4可以表示为10。举个例子,某位海迷想要根据姓名找出人物对应的绰号,于是,他写了如下公式:

=VLOOKUP(D2,A1:B12,2,0)

公式说明:

VLOOKUP函数的参数1是单元格D2——尤斯塔斯·基德,它在参数2A1:B12的首列A1:A12之中,参数3的值为2,表示返回参数2A1:B12的第二列,即B列中的绰号。参数3不能大于参数2的最大列数2。参数40,表示精确匹配。于是整个函数公式的含义可以表达为,从A1:B12的首列中找出值等于D2的单元格,返回A1:B12的第二列中与之对应的内容。

LOOKUP函数用于查询一行或一列并查找另一行或列中的相同位置的值,它的基本语句是:

=LOOKUP(查找值,查找区域,[返回区域]

其中,参数2查找区域须按升序排列。参数3返回区域不是必填项目,当参数3被省略,则以参数2查找区域的最后一行或最后一列作为返回区域,我们称这种使用方式为数组形式。但是我们通常建议使用向量形式,即保留参数3,此时查找区域和返回区域均为一列或一行,且大小相同。同样的例子,我们看看LOOKUP是怎么做的?

=LOOKUP(D9,A1:A12,B1:B12)

什么情况?我用眼睛都能看出的查询结果LOOKUP竟然算错了,莫非LOOKUP函数失灵了?当然不是,你再返回上一段看一下,没错,标红部分,参数2查找区域须按升序排列

升序排列!升序排列!升序排列!重要的事情说三遍,是谁的小眼睛还没有看老师!

SO,如果LOOKUP函数像这样不听使唤,那你有可能掉进了LOOKUP的陷阱里,此时你应该检查参数2是否是按升序排列。如下图,当A2:A12升序排列后,得到了正确结果。

PS:与VLOOKUP的遍历查询法不同,LOOKUP的查询原理是二分法,LOOKUP陷阱正与二分法有关,感兴趣的小伙伴可以看看往期教程LOOKUP函数用法全解(下)——LOOKUP函数的二分法原理》。

由此观之,在基本用法的较量中,LOOKUP的语句较复杂且需要对查找区域进行升序排列,不如VLOOKUP函数简洁实用。

第一回合,函数基本用法,VLOOKUP小胜!!!

ROUND 02  模糊包含查找比拼

有时候,我们需要查找的数据并不能直接通过LOOKUPVLOOKUP的基本用法精确匹配。换句话说,查找值和查找区域存在某种肉眼可见的对应关系,但并非完全相等。比如,对《海贼王》 这部漫画不熟悉的小伙伴可能记不全主人公路飞的全名,此时,我们如何利用路飞这个简称在对照表中找到他的绰号呢?这种问题在工作中也非常常见,比如已知供应商简称找全称、已知名字找全名等等。类似这样的问题,我们姑且称之为模糊包含查找

对付模糊包含查找问题,我们不能简单地使用VLOOKUPLOOKUP函数的基本用法来解决,不然结果是前者不知所措,后者张冠李戴!!!

那么遇到这种问题,VLOOKUPLOOKUP是不是就都束手无策了呢?当然不是,作为实力强劲的明星查询函数,怎能就此溃败!和基本用法一样,VLOOKUPLOOKUP在解决模糊包含查找问题时也是你有你的张良计,我有我的过墙梯。

先来看看VLOOKUP函数的张良计——通配符,星号"*"和问号"?"

星号"*":通配任意个字符,通常单独使用,表示此处可以没有字符,也可以有任意个字符。

问号"?":通配单个字符,可以重复使用,表示此处必须有与问号相同个数的字符。

我们将通配符置于查找值的前面或后面,用英文双引号将其圈定表示常量字符,并用文本连接符"&"连接起来,形成“"*"&B2”“"*"&B2&"?"”之类的查找值作为VLOOKUP的第一参数,其余参数与基本用法一致即可。

=VLOOKUP("*"&D3&"*",A1:B12,2,0)

公式说明:查找值中使用了通配符,"*"&D3&"*"表示D3的前后均允许存在任意字符,即只要查找区域首列A2:A12中的单元格值包含D3(路飞),就返回对应的B列值。这就是VLOOKUP的模糊包含查找之道!

我们再来看看LOOKUP函数的过墙梯——FIND函数。

由于LOOKUP函数首个参数不能使用通配符,我们只能通过文本查找函数FIND来帮助LOOKUP识别查找区域中是否包含查找值。FIND函数的基本语句是=FIND(查找文本,包含查找文本的文本),它可以返回所查找的文本在包含其的文本中首字符出现的位置,例如FIND(孙,孙悟空)=1FIND(悟空,孙悟空)=2

我们使用FIND函数来构造一个全新的查找区域,再赋予LOOKUP的第一参数一个足够大的数字,即可完成LOOKUP的模糊查找。

=LOOKUP(100,FIND(D9,A2:A12),B2:B12)

公式说明:查找值100是一个足够大的数,它一定大于FIND函数的任意一个返回值,即它大于查找区域A2:A12的最大文本长度。FIND函数一一查找D9单元格文本路飞A2:A12中的每个单元格文本中出现的位置。如果只有一个单元格包含文本“路飞”,则A2:A12单元格文本中仅有一个单元格返回数字,其余单元格均因不包含文本路飞而返回错误值#VALUE!。那么,由FIND函数构成的新查找区域仅由1个数字和多个#VALUE!组成。

由于LOOKUP的查找值100始终大于FIND的返回值,即查找值大于查找区域中的值。根据二分法原理,LOOKUP函数将返回最接近查找值且小于查找值的数所对应的单元格。在这个例子中,100作为查找值,查找区域中除了一个数字6,其他都是错误值,因此只能找到66所在的单元格是A9,而A9在返回区域B2:B12中对应的单元格是B9,所以返回B9的值“草帽”。

关键要点:LOOKUP的查找值一定要大于FIND的返回值!!!

由此观之,在模糊包含查找的较量中,VLOOKUP仅凭几个简单符号即可四两拨千斤, LOOKUP却需要劳师动众地搬来FIND救场,VLOOKUP在语句的简便性和易读性上都远超LOOKUP

第二回合,模糊包含查找,VLOOKUP完胜!!!

在这两回较量中,VLOOKUP占据上风,赢得漂亮,有了不错的开头。但LOOKUP会甘愿就此落败吗?其实他们的较量才刚刚开始,究竟LOOKUP能否扳回劣势甚至反超,敬请期待它们的第三、第四回合较量!

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

LOOKUP函数用法①《LOOKUP函数用法全解(上)——LOOKUP函数的5种用法

LOOKUP函数用法②《LOOKUP函数用法全解(下)——LOOKUP函数的二分法原理》

VLOOKUP找不到数据的原因《你一定要了解:公式没错Vlookup仍找不到数据的3大原因》