excel的vlookup函数怎么使用?
116 个回答
全文 5000+字,前方高能,干货预警!花了一个周末的时间,整理了 VLOOKUP 函数最全的 16 种用法,借着这个话题分享给大家,希望对大家有帮助呀,收藏的同时不要忘记点赞呀~
16 个技巧划分成了 3 个部分:初级篇、中级篇和高级篇,并且每个用法都有案例+详细解释,整体大纲如下:
持续更新中,还有漏的技巧,大家可以评论区 @指北针 ,我会更新上来~
当然只看图片不练习怎么也学不会 VLOOKUP 的,关于这 16 种用法,我也整理了一份练习文件,分享给大家,希望对大家有帮助呀,→这里:VLOOKUP的这16种用法,强到没朋友!(练习下载)
另外不少小伙伴私信咨询有没有零基础学习 Excel 函数的书籍推荐,对于零基础的小伙伴,可以看下这 3 册书,刚好京东暑假读书优惠(适合零基础):
另外我也分享了近 500+ 篇 Excel/Word/PPT 类的技巧,也是超级有用的学习资料:
01. VLOOKUP基础语法
要了解一个 Excel 函数,第一个学习的肯定是函数的语法结构,VLOOKUP 共有 4 个参数,参数语法如下:
= VLOOKUP(查找的值, 查找区域, 返回值所在列数, [匹配模式])
这 4 个参数的解释如下:
- 查找的值:要查找的词或单元格引用;
- 查找区域:包含查找字段和返回字段的单元格区域,查找字段必须在查找区域的第 1 列;
- 返回值所在列数:返回值在查找区域中的列数;
- 匹配模式:0 为精确匹配,1 为模糊匹配;
以上就是 VLOOKUP 函数的基础语法,作为 Excel 中使用频率最高、资质最老的一批函数,兼容性也非常强,基本上市面的 Excel 和 WPS 版本都能完美兼容。
02. VLOOKUP 单条件查询
首先是 Excel 中最常用,也最基础的用法「单条件查询」,只需要根据语法规则将对应单元格区域录入到公式中即可。
案例:“根据工号将对应工资进行查询匹配”,操作也很简单,输入公式:
=VLOOKUP(H2,A2:F11,6,0)
现在就可以轻松将工号为 6 的员工工资匹配出来了,如下:
简单解释下这个公式的含义:
- H2:要查找的单元格引用;
- A2:F11:查找区域单元格区域;
- 6:工资字段位于查找区域的第 6 列;
- 0:代表精准匹配;
所以并不是所有的 VLOOKUP 函数都是返回第 2 列的,还要根据实际的工作场景来返回。
03. 屏蔽检索不到错误
VLOOKUP 在找不到数据的情况下会返回 #N/A 错误,如果想将错误屏蔽掉,或者输出其他提示信息,用 IFERROR+VLOOKUP 组合即可实现。
案例:“匹配不到数据的时候返回 / 提示”,使用公式:
=IFERROR(VLOOKUP(H2,A2:F11,6,0),"/")
如下,查找区域并没有工号 11 的员工,所以返回了“/”:
另外 VLOOKUP 对数据的匹配是极为严格的,哪怕差了一个空格、一个不可见字符,都会认为是不一样的,例如:“芒种学院”和“芒种学院 ”并不相等,因为差了一个空格。
04. 反向匹配技巧
讲解语法的时候提到过“查找词必须在查找区域的第 1 列”,但是在实际工作中,数据往往并不能完美符合需求,这个时候可以利用 IF 数组公式来调换列。
案例:“根据姓名查找对应的工号”,使用如下公式:
=VLOOKUP(H2,IF({1,0},B2:B11,A2:A11),2,0)
这里用到了「IF数组公式」,将 B 列和 A 列的位置进行互互换,然后重新组成一个新的区域,这样 B 列就跑到 A 列前面了,如下:
另外这个公式是“数组公式”,如果使用的版本并非 365,还需要按三键 Ctrl+Shift+Enter 结束,并不能直接 Enter 结束,大概率结果会出错。
05. 模糊关键词检索
除了检索确定的关键词,VLOOKUP 也可以配合通配符实现「模糊关键词匹配」,在 Excel 中共有 2 个通配符:
- 星号*:代表任意个字符,可以是 0、1、N 个;
- 问号?:代表任意 0 个或者 1 个字符;
案例:“匹配出包含阳字姓名的员工工资”,可以使用如下公式:
=VLOOKUP("*"&H2&"*",B2:F11,5,0)
这样即可匹配出数据中包含“阳”字的相关数据,如下:
06. 代替 IF 数值区间匹配
针对查找词为“数字”的场景,VLOOKUP 还可以实现区间匹配,完美代替 IF 嵌套地狱。
案例:“根据不同的销售业绩层级计算提成”,使用如下公式:
=VLOOKUP(D2,A2:B6,2,1)
不过使用这个公式是有条件限制的,必须符合这 2 个条件方可使用:
- 查找词必须为数字;
- 查找区域的数字必须从小到大进行排序;
公式实现效果如下所示,轻松将提成计算出来:
07. 去除空格/不可见字符匹配
如果碰上不规范的数据,明明肉眼看上去一模一样,但是怎么都匹配不出来,大概率是碰到了数据中存在空格、数据中存在不可见字符。
这个时候可以使用 SUBSTITUTE 或者 CLEAN 函数进行处理后匹配,例如:
=VLOOKUP(SUBSTITUTE(H2," ",""),B2:F11,2,0)
这个公式的技巧在于使用 SUBSTITUTE 将空格替换成空,然后再进行匹配,确保数据的一致性。
另外如果碰到的是不可见字符,则可以使用 CLEAN 函数清除。
再分享多一个判断是否存在“空格”或者“不可见字符”的技巧,可以使用 LEN 函数判断数据的长度,如果长度不一致,则表明存在。
08. 横向匹配返回多列
前面分享的 7 个技巧都是返回单个结果的,如果返回的数据是 N 列呢?难道还要写 N 个公式,如果数据是连续的,可以利用 COLUMN+VLOOKUP 配合实现返回多列。
案例:“根据工号返回姓名/部门/性别/年龄/工资”等字段信息,使用如下公式:
=VLOOKUP($H$2,$A$2:$F$11,COLUMN(B1),0)
分别向右向下拖动完成填充即可,这里主要用到了 COLUMN 函数动态生成 2/3/4/.../N ,避免将返回列数固定写死,从而达到返回多列的效果。
如果数据并非连续的呢?这个时候就可以考虑使用 MATCH 函数来定位数据所在的列数了。
09. 匹配多表查询
如果匹配的表有多张,并且可以通过条件来判断数据是位于哪张表中,那么可以使用 VLOOKUP+IF 公式来实现需求。
案例:“不同的城市位于不同的表中,查找广州工号 8 员工的工资”,利用如下公式:
=VLOOKUP(B2,IF(A2="深圳",A6:F15,H6:M15),6,0)
这里用到了 IF 函数判断 A2 单元格的数值是否为深圳,是则返回 A6:F15,否则返回返回 H6:M15,实现了动态变更查找区域。
当然这个技巧只适用于表的数量较少的情况下,如果表数量比较多,使用 IF 会让公式看起来很长,而且还容易出错,后面会有另外的技巧来实现。
10. 多条件匹配查找
前面分享了 9 个技巧,不过都是单条件查找,多个字段才能确定唯一一条数据的情况下,利用 VLOOKUP 也可以轻松实现,同样用到了 IF 数组公式。
案例:“根据姓名+部门查找员工对应工资”,利用如下公式:
=VLOOKUP(H2&I2,IF({1,0},B2:B11&C2:C11,F2:F11),2,0)
这里用到了 2 个技巧:
- 将 H2&I2 拼接起来组成一个新字段,并且该字段是唯一的;
- IF 数组公式将 B 和 C 列也拼接到一起,然后和 F 列组成成新的查找区域;
这样就可以巧妙地将「多条件」转换成「单条件」,实现效果如下:
11. 查找区域存在合并单元格
如果查找区域中存在合并单元格,直接使用 VLOOKUP 匹配非常容易出错,因为合并单元格只有最左上角的单元格存在数据,其他单元格并不存在。
这个时候就可以考虑使用 VLOOKUP+OFFSET+MATCH 来实现。
案例:“根据部门+姓名查找出员工对应的工资”,使用如下公式:
=VLOOKUP(F2,OFFSET($B$1,MATCH(E2,A:A,)-1,):C99,2,0)
MATCH 函数用于定位部门所在的行号,然后使用 OFFSET 函数向下偏移获得“动态查找区域”,这样就可以轻松实现我们的需求了:
当然这个案例会复杂很多,在实际工作中也尽量不要使用合并单元格来实现数据分组,无论是函数、透视表、图表、排序、筛选等绝大部分功能对合并单元格都不太友好。
12. 查找词中存在合并单元格
如果在查找词中也存在合并单元格,可以嵌套使用两个 VLOOKUP 函数完成需求。
案例:“根据部门返回月度奖金”,使用如下公式:
=VLOOKUP(VLOOKUP("座",$D$2:D2,1),$A$2:$B$4,2,0)
嵌套的 VLOOKUP 函数主要是用于找到 D 列截止至本行的最后一个非空值,这样就可以将查找词一一匹配上,如下所示:
13. 查找返回多个结果
关键词和返回结果是 1:N 的关系,也可以使用 VLOOKUP 将所有结果全部返回,用到了 VLOOKUP+ROW+IF+INDIRECT 数组公式配合。
案例:“找出市场部所有员工的姓名”,使用如下公式:
=VLOOKUP($H$2&ROW(A1),IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT("c2:c"&ROW($2:$11)),$H$2),$B$2:$B$11),2,0)
公式非常复杂,整体可以分为 4 个思路:
- 用 ROW 函数生成序列;
- INDIRECT+ROW 生成行数递增的查找区域;
- COUNTIF 计算部门的个数,生成新编号;
- IF 数组公式将构建的数据组成一个新的查找区域;
同样是数组公式,按 Ctrl+Shift+Enter 结束后向下拖动填充,得到如下结果:
这个公式的兼容性虽然很好,不过维护起来实在费劲,如果是 365 版本,可以直接使用 FILTER 函数即可实现需求,而且非常便捷。
14. 查找结果返回同一单元格
上面的技巧是将查找结果填充到不同的单元格中,如果想将结果合并到一个单元格内呢?
纯粹利用 VLOOKUP 实现不了,但是可以配合辅助列,共有 2 个公式:
G2 = C2&","&IFERROR(VLOOKUP(B2,B3:$G$12,6,),"")
I2 = VLOOKUP(H2,$B$2:$G$11,6,)
其中 G2 单元格的公式用到了“调用自身列”的技巧,将找到的数据依次拼接,最后使用二分法进行匹配,实现效果如下:
当然难度也非常大,如果版本比较新,可以考虑使用 TEXTJOIN+IF/FILTER 函数来实现,会非常简单,而且更流畅一些。
15. 返回最后一个结果
如果返回的结果有多个,只想要最后一个,利用 VLOOKUP+IF 数组公式也可以实现。
案例:“找到市场部的最后一位职员”,使用如下公式:
=VLOOKUP(1,IF({1,0},0/(C2:C11=H2),B2:B11),2)
具体思路为当忽略 VLOOKUP 的最后一个参数时,函数会使用二分法进行查找,同时用 0/条件 可以将不符合条件的数值变成错误值,符合的变成 0。
最后用 1 查找最后一个 0 即可实现需求,是不是非常巧妙,实现效果如下:
16. 跨多Sheet表匹配
数据被分布在 N 张 Sheet 表中,而且并不确定查找词位于其中的哪张表,这个时候可以利用 INDIRECT+VLOOKUP 实现跨多 Sheet 表匹配。
案例:“存在深圳/广州/上海共计 3 张表,匹配出不同员工的工资”,使用如下公式:
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"深圳";"广州";"上海"}&"!b:b"),A2),{"深圳";"广州";"上海"})&"!b:f"),5,0)
公式非常长,不过逻辑非常简单,利用 INDIRECT 函数将所有表的数据全部导入进行匹配,找到非错误的值即可,如下。
当然针对这类表格,已经不建议使用 VLOOKUP 进行匹配了,不仅卡不说,还非常容易出错,不过公式还是非常值得研究的。
可以的话,利用 PQ 将数据合并起来然后进行匹配,效果会更高。
好了,暂时先分享 16 个,后续还有用到其他的 VLOOKUP 技巧,我也会更新上来,希望对大家有帮助呀,收藏的同时不要忘记点赞哦~
我的其他高赞回答,也可以看下哦,都是一些非常有用的资料:
- VLOOKUP 到底有多重要?
- 如何制作图表非常精美的 Excel 文档?
- 工作中 Excel 哪个功能最实用?
- 有哪些值得推荐的数据可视化工具?
- Excel 有多强大?你用 Excel 做的最酷的事情是什么?
- 如何快速学习数据透视表?
- Excel 有哪些和 vlookup 一样重要的函数或功能?
- 怎样才算精通 Excel?
未经允许,禁止转载,练习文件有想要的么,晚点也更新上来。
如果觉得有用,点个赞再走呗~持续更新哦!!
Vlookup函数的7种典型应用技巧,能解决查询引用的全部问题
查询引用,用到最多的函数为Vlookup,但你真的会用吗?其实,Vlookup函数除了常规的查询引用外,还有多种使用技巧
一、Vlookup函数:功能及语法结构。
功能:在指定的数据范围内返回符合查询要求的值。
语法:=Vlookup(查询值,数据范围,返回值列数,匹配模式)。
其中匹配模式有两种,分别为“0”或“1”。其中“0”为精准匹配,“1”为模糊匹配。
目的:查询“商品”的“销量”。
方法:
在目标单元格中输入公式:=VLOOKUP(H3,B3:C9,2,0)。
解读:
第三个参数(返回值列数)是根据第二个参数(数据范围)来确定的,“数据范围”中的第一列为1,第二列为2……以此类推。
二、Vlookup函数:反向查询。
目的:根据“编码”查询“商品”名称。
方法:
1、在目标单元格中输入公式:=VLOOKUP(I3,IF({1,0},C3:C9,B3:B9),2,0)。
2、Ctrl+Shift+Enter填充。
解读:
公式中的IF({1,0},C3:C9,B3:B9)的作用为形成一个以C3:C9为第一列、B3:B9为第二列的临时数组。
三、Vlookup函数:多条件查询。
目的:根据“商品”名称和“型号”查询“销量”。
方法:
1、在目标单元格中输入公式:=VLOOKUP(I3&J3,IF({1,0},B3:B9&C3:C9,D3:D9),2,0)。
2、快捷键Ctrl+Shift+Enter填充。
解读:
1、当有多个查询的条件时,用连接符“&”连接在一起,对应的数据区域也用“&”连接在一起。
2、公式中IF({1,0},B3:B9&C3:C9,D3:D9)的作用为形成一个以B3:B9和C3:C9为第一列,D3:D9为第二列的临时数组。
四、Vlookup函数:多条件反向查询。
目的:根据“商品”的销售“地区”查询对应的“销量”。
方法:
1、在目标单元格中输入公式:=VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0)。
2、快捷键Ctrl+Shift+Enter填充。
解读:
当有多个条件和数据范围时,对应的值用符号“&”连接。
五、Vlookup函数:屏蔽错误值。
目的:无查询匹配结果时,不显示错误代码#N/A, 将单元格的置空。
方法:
在目标单元格中输入公式:=IFERROR(VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0),"")。
解读:
Iferror函数的作用为:判断一个表达式是否有误,如果有误,则返回本身,否则返回指定的值;语法结构为:=Iferror(表达式,表达式有误时的返回值)。
六、Vlookup函数:批量查询。
目的:根据“商品”名称批量返回相关信息。
方法:
在目标单元格中输入公式:=VLOOKUP($I3,$B$3:$F$9,MATCH(J$2,$B$2:$F$2,0),0)。
解读:
1、巧妙利用Match函数获取返回值对应的列数。
2、注意参数的引用方式,不变为“绝对”、变为“相对”,也可以是“混合引用”。
七、Vlookup函数:一对多查询。
目的:根据对应的值返回多个查询结果。
步骤1:插入辅助列。
方法:
1、在“商品”列的前面插入“辅助列”。
2、输入公式:=COUNTIF(C$3:C3,C3)。
解读:
利用Countif函数统计“商品”在对应的区域出现的次数。
步骤2:根据“商品”名称查询对应的“型号”。
方法:
1、在目标单元格中输入公式:=IFERROR(VLOOKUP($J$3&ROW(A1),IF({1,0},C3:C9&B3:B9,D3:D9),2,0),"")。
2、快捷键Ctrl+Shift+Enter填充。
解读:
公式主要运用了“逆向查询”和“屏蔽错误值”两种主要方法。
步骤3:根据“商品”名称和“型号”查询对应的销量。
方法:
1、在目标单元格中输入公式:=IFERROR(VLOOKUP($J$3&$K3,IF({1,0},C3:C9&D3:D9,E3:E9),2,0),"")。
2、快捷键Ctrl+Shift+Enter填充。
解读:
公式主要应用了多条件的方法。
结束语:
文中从实际应用出发,针对不同的应用场景,对Vlookup函数的7种典型用法做了详细的解读,对于使用技巧,你Get到了吗?如果亲有更多的关于Vlookup的用法,欢迎在留言区留言讨论哦!