快捷搜索:  美女    名称  交警  美食

这也许是史上最好最全的VLOOKUP函数教程

关注Excel不加班置顶公众号



函数中最受欢迎的有三大家族,一个是以SUM函数为首的求和家族,一个是以VLOOKUP函数为首的查找引用家族,另外一个就是以IF函数为首的逻辑函数家族。根据二八定律,学好这三大家族的函数,就能完成80%的工作。


现在一起来学习VLOOKUP函数,让关于查找的烦恼一次全解决!


1、根据番号精确查找俗称。


=VLOOKUP(D2,A:B,2,0)


VLOOKUP函数语法:

=VLOOKUP(查找值,查找区域,返回查找区域第N列,查找模式)


VLOOKUP函数示意图。


2、屏蔽错误值错误值查找。


=VLOOKUP(D2,A:B,2,0)


VLOOKUP函数如果查找不到对应值会显示错误值#N/A,这个看起来很不美观。这时可以在外面加个容错函数IFERROR,如果是2013版本那就更好,可以用IFNA函数,这个是专门处理#N/A这种错误值。


=IFERROR(VLOOKUP(D2,A:B,2,0),"")

=IFNA(VLOOKUP(D2,A:B,2,0),"")


函数语法:

=IFERROR(表达式,错误值要显示的结果)


说白了就是将错误值显示成你想要的结果,不是错误值就返回原来的值。IFNA函数的作用也是一样,只是IFERROR函数是针对所有错误值,而IFNA函数只针对#N/A。


3、按顺序返回多列对应值。


通过上面的例子,我们知道可以通过更改第3参数,返回各项对应值如:

=VLOOKUP($A13,$A$1:$F$10,2,0)

=VLOOKUP($A13,$A$1:$F$10,3,0)


如果项目少,更改几次参数也没什么,但项目多时,肯定不方便。如图 5103所示,可以通过ROW、COLUMN产生行列号,从而得到1,2,……,n的值。


=VLOOKUP($A13,$A$1:$F$10,COLUMN(B1),0)


因为这里是同一行产生序号,所以用COLUMN函数。


4、按不同顺序返回对应值。


这回看来只能手动更改第3参数了,COLUMN完全派不上用场。


NO!每当你觉得操作繁琐时,就要停下来思考,也许Excel本身存在这个功能,只是自己一时想不到或者不知道而已。列号不管千变万化,在数据源的位置始终不变,利用这个特点可以去搜索一下看看有什么函数可以解决。


在“搜索函数”文本框输入:位置,单击“转到”按钮,就会出现跟位置有关的函数,查看每个函数的说明,找到我们需要的,如MATCH函数,返回符合特定值特定顺序的项在数组中的相应位置,单击“确定”按钮。


在弹出的“函数参数对话框”中尝试填写相应的参数,每个参数的作用下面都有相关说明,填写后会出现计算结果3,也就是订单数在区域中是第3列。尝试下更改第1参数为C12(俗称),计算结果是2,也就是区域中第2列。经过尝试,知道这个函数是我们要找的那个函数,单击“取消”按钮,返回工作表。


在单元格再做最后一次验证。


到这一步已经十拿九稳了,将公式设置为:

=VLOOKUP($A13,$A$1:$F$10,MATCH(B$12,$A$1:$F$1,0),0)


5、根据番号逆序俗称。


帮助提到VLOOKUP函数只能按首列查找,不能逆向查找,既然如此,那就得想办法将非首列的区域转换成首列。怎么转换区域呢,这时IF函数就派上用场。一步步来了解IF函数的转换。


看看好友传递如何趣聊IF函数,吃货的福音。


IF函数其实只有一个条件来判断是否符合条件,返回FALSE和TRUE两种结果。


当菜只有分甜的或咸的2种口味时,甜味是红烧肉,咸味是酱油肉。


盲人吃饭时,看不到是什么菜。当别人问盲人:“你现在吃的什么菜?是咸的吗?如果是咸的,就是酱油肉,如果不是咸的就是红烧肉。”(给定判断条件:咸味)盲人刚好在吃红烧肉,于是就咂吧着嘴说:“恩,好吃,不是咸的!是红烧肉”(根据提问的要求,不符合咸的)假如要是盲人当时是在吃酱油肉呢,一定回答;“是的,咸的,是酱油肉”(条件为真,是!TRUE)。盲人根据口感,结合提问者说的条件,就知道自己吃的是红烧肉还是酱油肉了。


把这段话用公式来写:

=IF(A1="咸的",A2,B2)


翻译:是咸的吗?要是(TRUE),就是酱油肉,要是不是咸的(FALSE),就是甜的红烧肉。


A1="咸的"这个条件也可以直接换成TRUE或者FALSE。

=IF(TRUE,A2,B2)


因为满足条件,所以返回A2的对应值酱油肉。

=IF(FALSE,A2,B2)


因为不满足条件,所以返回B2的对应值红烧肉。


其实TRUE=1,FALSE=0,所以可以直接用1跟0表示。

=IF(1,A2,B2)

=IF(0,A2,B2)


IF函数不止可以返回1个单元格的值,也可以返回多个单元格的值。

=IF({1,0},A2,B2)

=IF({0,1},A2,B2)


选择两个单元格输入,按Ctrl+Shift+Enter三键结束。条件为{1,0},返回A2:B2的对应值顺序不变;条件为{0,1},返回A2:B2的对应值,顺序对换。也就是说通过改变1跟0的位置,可以调换两单元格的前后位置。


看到这里,知道IF函数通过改变1,0可以调换单元格的顺序,如果要改变区域的顺序也是可以实现的。


用IF函数重新构造的新区域,是多单元格数组公式,记得按Ctrl+Shift+Enter三键结束,否则出错。


新区域:

=IF({1,0},B2:B10,A2:A10)


所以公式可以变成:

=VLOOKUP(A13,新区域,2,0)


两个公式合并,大功告成。

=VLOOKUP(A13,IF({1,0},$B$2:$B$10,$A$2:$A$10),2,0)


6、根据俗称跟订单号两个条件查询完成情况。


正常情况下VLOOKUP函数是不能多条件查询,通过IF函数的学习,我们知道IF函数可以重新构造区域,这里就再次用IF构成一个区域。



新区域:

=IF({1,0},A2:A9&C2:C9,E2:E9)


所以公式可以变成:

=VLOOKUP(A12&B12,新区域,2,0)


两个公式合并,大功告成,记得按Ctrl+Shift+Enter三键结束。

=VLOOKUP(A12&B12,IF({1,0},$A$2:$A$9&$C$2:$C$9,$E$2:$E$9),2,0)


7、根据俗称的第一个字符查找番号。


=VLOOKUP(D2&"*",A:B,2,0)


星号(*)是通配符,代表所有字符,问号(?)代表一个字符。D2&"*"就是开头包含D2的意思。


8、根据区域判断成绩的等级。


借助辅助列的话,很容易查询等级,只需将VLOOKUP函数的第四参数设置为1或者省略即可。

=VLOOKUP(E2,A:C,3)


如果不用辅助列,估计很多人看到这条公式就得哭了,得结合前面所有函数知识才能完成,有兴趣的朋友可以自己去研究。

=VLOOKUP(E2,IF({1,0},--LEFT(B$2:B$5,FIND("-",B$2:B$5)-1),C$2:C$5),2)


前阵子无意间发现了IMREAL函数,所以不用辅助列的数组公式可以稍微简单一点。


=VLOOKUP(E2,IF({1,0},IMREAL(B$2:B$5&"i"),C$2:C$5),2)

IMREAL函数是计算复数的实部系数的函数,作用就是提取区间的下限。


通过这8个疑难,基本上的查询问题都能够解决。


开心吗?一下搞定8大疑难!


相关文章:

VLOOKUP函数跨表、逆向、双条件查询对应值

厉害了,跨国查询的 VLOOKUP

VLOOKUP函数,为何你总是出错?

用身份证VLOOKUP出现问题的人必看,你要的答案都在这里了……

突破限制,让VLOOKUP查找无所不能!

同样是VLOOKUP函数查找单价,为何你还是出错?

学会这几条公式,你比80%的人都会用VLOOKUP函数家族!

VLOOKUP函数这两个让80%的人头痛的问题,终于有解了!

VLOOKUP函数最经典的12个表查找用法(推荐收藏)


VLOOKUP函数的相关文章实在太多了,没想到我过去写了几十篇文章,就不一一罗列。


VIP888元,一次报名,所有视频课程,终生免费学,提供一年在线答疑服务。



报名后加卢子微信chenxilu2019,发送报名截图邀请进群。


你最喜欢哪个函数?



作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

请把「Excel不加班」推荐给你的朋友

您可能还会对下面的文章感兴趣: