excel的vlookup函数怎么使用?

全文 5000+字，前方高能，干货预警 ！花了一个周末的时间，整理了 VLOOKUP 函数最全的 16 种用法，借着这个话题分享给大家，希望对大家有帮助呀，收藏的同时不要忘记点赞呀~

16 个技巧划分成了 3 个部分： 初级篇、中级篇和高级篇 ，并且每个用法都有案例+详细解释，整体大纲如下：

持续更新中，还有漏的技巧，大家可以评论区 @指北针 ，我会更新上来~

当然只看图片不练习怎么也学不会 VLOOKUP 的，关于这 16 种用法，我也整理了一份练习文件，分享给大家，希望对大家有帮助呀，→这里： VLOOKUP的这16种用法，强到没朋友！(练习下载)

另外不少小伙伴私信咨询有没有零基础学习 Excel 函数的书籍推荐，对于零基础的小伙伴，可以看下这 3 册书，刚好京东暑假读书优惠（适合零基础）：

另外我也分享了近 500+ 篇 Excel/Word/PPT 类的技巧，也是超级有用的学习资料：

01. VLOOKUP基础语法

要了解一个 Excel 函数，第一个学习的肯定是函数的语法结构，VLOOKUP 共有 4 个参数，参数语法如下：

= VLOOKUP(查找的值, 查找区域, 返回值所在列数, [匹配模式])

这 4 个参数的解释如下：

1. 查找的值 ：要查找的词或单元格引用；
2. 查找区域 ：包含查找字段和返回字段的单元格区域，查找字段必须在查找区域的第 1 列；
3. 返回值所在列数 ：返回值在查找区域中的列数；
4. 匹配模式 ：0 为精确匹配，1 为模糊匹配；

以上就是 VLOOKUP 函数的基础语法， 作为 Excel 中使用频率最高、资质最老的一批函数，兼容性也非常强，基本上市面的 Excel 和 WPS 版本都能完美兼容 。

02. VLOOKUP 单条件查询

首先是 Excel 中最常用，也最基础的用法「单条件查询」，只需要根据语法规则将对应单元格区域录入到公式中即可。

案例：「 根据工号将对应工资进行查询匹配 」，操作也很简单，输入公式：

现在就可以轻松将工号为 6 的员工工资匹配出来了，如下：

简单解释下这个公式的含义：

1. p：要查找的单元格引用；
2. A2:F11：查找区域单元格区域；
3. 6：工资字段位于查找区域的第 6 列；
4. 0：代表精准匹配；

所以并不是所有的 VLOOKUP 函数都是返回第 2 列的，还要根据实际的工作场景来返回。

03. 屏蔽检索不到错误

VLOOKUP 在找不到数据的情况下会返回 #N/A 错误，如果想将错误屏蔽掉，或者输出其他提示信息，用 IFERROR+VLOOKUP 组合即可实现。

案例：「 匹配不到数据的时候返回 / 提示 」，使用公式：

如下，查找区域并没有工号 11 的员工，所以返回了「/」：

另外 VLOOKUP 对数据的匹配是极为严格的，哪怕差了一个空格、一个不可见字符，都会认为是不一样的，例如： 「芒种学院」和「芒种学院 」并不相等，因为差了一个空格 。

04. 反向匹配技巧

讲解语法的时候提到过「查找词必须在查找区域的第 1 列」，但是在实际工作中，数据往往并不能完美符合需求，这个时候可以利用 IF 数组公式来调换列。

案例：「 根据姓名查找对应的工号 」，使用如下公式：

这里用到了「IF数组公式」，将 B 列和 A 列的位置进行互互换，然后重新组成一个新的区域，这样 B 列就跑到 A 列前面了，如下：

另外这个公式是「数组公式」，如果使用的版本并非 365，还需要按三键 Ctrl+Shift+Enter 结束，并不能直接 Enter 结束，大概率结果会出错。

05. 模糊关键词检索

除了检索确定的关键词，VLOOKUP 也可以配合通配符实现「 模糊关键词匹配 」，在 Excel 中共有 2 个通配符：

1. 星号* ：代表任意个字符，可以是 0、1、N 个；
2. 问号? ：代表任意 0 个或者 1 个字符；

案例：「 匹配出包含阳字姓名的员工工资 」，可以使用如下公式：

这样即可匹配出数据中包含「阳」字的相关数据，如下：

06. 代替 IF 数值区间匹配

针对查找词为「数字」的场景，VLOOKUP 还可以实现区间匹配，完美代替 IF 嵌套地狱。

案例：「 根据不同的销售业绩层级计算提成 」，使用如下公式：

不过使用这个公式是有条件限制的，必须符合这 2 个条件方可使用：

1. 查找词必须为数字；
2. 查找区域的数字必须从小到大进行排序；

公式实现效果如下所示，轻松将提成计算出来：

07. 去除空格/不可见字符匹配

如果碰上不规范的数据，明明肉眼看上去一模一样，但是怎么都匹配不出来，大概率是碰到了数据中存在空格、数据中存在不可见字符。

这个时候可以使用 SUBSTITUTE 或者 CLEAN 函数进行处理后匹配，例如：

这个公式的技巧在于使用 SUBSTITUTE 将空格替换成空，然后再进行匹配，确保数据的一致性。

另外如果碰到的是不可见字符，则可以使用 CLEAN 函数清除。

再分享多一个 判断是否存在「空格」或者「不可见字符」的技巧，可以使用 LEN 函数判断数据的长度，如果长度不一致，则表明存在 。

08. 横向匹配返回多列

前面分享的 7 个技巧都是返回单个结果的，如果返回的数据是 N 列呢？难道还要写 N 个公式，如果数据是连续的，可以利用 COLUMN+VLOOKUP 配合实现返回多列。

案例：「 根据工号返回姓名/部门/性别/年龄/工资 」等字段信息，使用如下公式：

分别向右向下拖动完成填充即可，这里主要用到了 COLUMN 函数动态生成 2/3/4/.../N ，避免将返回列数固定写死，从而达到返回多列的效果。

如果数据并非连续的呢？这个时候就可以考虑使用 MATCH 函数来定位数据所在的列数了。

09. 匹配多表查询

如果匹配的表有多张，并且可以通过条件来判断数据是位于哪张表中，那么可以使用 VLOOKUP+IF 公式来实现需求。

案例：「 不同的城市位于不同的表中，查找广州工号 8 员工的工资 」，利用如下公式：

这里用到了 IF 函数判断 A2 单元格的数值是否为深圳，是则返回 A6:F15，否则返回返回 H6:M15，实现了动态变更查找区域。

当然这个技巧只适用于表的数量较少的情况下，如果表数量比较多，使用 IF 会让公式看起来很长，而且还容易出错，后面会有另外的技巧来实现。

10. 多条件匹配查找

前面分享了 9 个技巧，不过都是单条件查找，多个字段才能确定唯一一条数据的情况下，利用 VLOOKUP 也可以轻松实现，同样用到了 IF 数组公式。

案例：「 根据姓名+部门查找员工对应工资 」，利用如下公式：

这里用到了 2 个技巧：

1. 将 p&I2 拼接起来组成一个新字段，并且该字段是唯一的；
2. IF 数组公式将 B 和 C 列也拼接到一起，然后和 F 列组成成新的查找区域；

这样就可以 巧妙地将「多条件」转换成「单条件」 ，实现效果如下：

11. 查找区域存在合并单元格

如果查找区域中存在合并单元格，直接使用 VLOOKUP 匹配非常容易出错，因为合并单元格只有最左上角的单元格存在数据，其他单元格并不存在。

这个时候就可以考虑使用 VLOOKUP+OFFSET+MATCH 来实现。

案例：「 根据部门+姓名查找出员工对应的工资 」，使用如下公式：

MATCH 函数用于定位部门所在的行号，然后使用 OFFSET 函数向下偏移获得「动态查找区域」，这样就可以轻松实现我们的需求了：

当然这个案例会复杂很多，在实际工作中也尽量不要使用合并单元格来实现数据分组，无论是函数、透视表、图表、排序、筛选等绝大部分功能对合并单元格都不太友好。

12. 查找词中存在合并单元格

如果在查找词中也存在合并单元格，可以嵌套使用两个 VLOOKUP 函数完成需求。

案例：「 根据部门返回月度奖金 」，使用如下公式：

嵌套的 VLOOKUP 函数主要是用于找到 D 列截止至本行的最后一个非空值，这样就可以将查找词一一匹配上，如下所示：

13. 查找返回多个结果

关键词和返回结果是 1:N 的关系，也可以使用 VLOOKUP 将所有结果全部返回，用到了 VLOOKUP+ROW+IF+INDIRECT 数组公式配合。

案例：「 找出市场部所有员工的姓名 」，使用如下公式：

公式非常复杂，整体可以分为 4 个思路：

1. 用 ROW 函数生成序列；
2. INDIRECT+ROW 生成行数递增的查找区域；
3. COUNTIF 计算部门的个数，生成新编号；
4. IF 数组公式将构建的数据组成一个新的查找区域；

同样是数组公式，按 Ctrl+Shift+Enter 结束后向下拖动填充，得到如下结果：

这个公式的兼容性虽然很好，不过维护起来实在费劲，如果是 365 版本，可以直接使用 FILTER 函数即可实现需求，而且非常便捷。

14. 查找结果返回同一单元格

上面的技巧是将查找结果填充到不同的单元格中，如果想将结果合并到一个单元格内呢？

纯粹利用 VLOOKUP 实现不了，但是可以配合辅助列，共有 2 个公式：

其中 G2 单元格的公式用到了「调用自身列」的技巧，将找到的数据依次拼接，最后使用二分法进行匹配，实现效果如下：

当然难度也非常大，如果版本比较新，可以考虑使用 TEXTJOIN+IF/FILTER 函数来实现，会非常简单，而且更流畅一些。

15. 返回最后一个结果

如果返回的结果有多个，只想要最后一个，利用 VLOOKUP+IF 数组公式也可以实现。

案例：「 找到市场部的最后一位职员 」，使用如下公式：

具体思路为当忽略 VLOOKUP 的最后一个参数时，函数会使用二分法进行查找，同时用 0/条件 可以将不符合条件的数值变成错误值，符合的变成 0。

最后用 1 查找最后一个 0 即可实现需求，是不是非常巧妙，实现效果如下：

16. 跨多Sheet表匹配

数据被分布在 N 张 Sheet 表中，而且并不确定查找词位于其中的哪张表，这个时候可以利用 INDIRECT+VLOOKUP 实现跨多 Sheet 表匹配。

案例：「 存在深圳/广州/上海共计 3 张表，匹配出不同员工的工资 」，使用如下公式：

公式非常长，不过逻辑非常简单，利用 INDIRECT 函数将所有表的数据全部导入进行匹配，找到非错误的值即可，如下。

当然针对这类表格，已经不建议使用 VLOOKUP 进行匹配了，不仅卡不说，还非常容易出错，不过公式还是非常值得研究的。

可以的话，利用 PQ 将数据合并起来然后进行匹配，效果会更高。

好了，暂时先分享 16 个，后续还有用到其他的 VLOOKUP 技巧，我也会更新上来，希望对大家有帮助呀，收藏的同时不要忘记点赞哦~

我的其他高赞回答，也可以看下哦，都是一些非常有用的资料：

1. VLOOKUP 到底有多重要？
2. 如何制作图表非常精美的 Excel 文档？
3. 工作中 Excel 哪个功能最实用？
4. 有哪些值得推荐的数据可视化工具？
5. Excel 有多强大？你用 Excel 做的最酷的事情是什么？
6. 如何快速学习数据透视表？
7. Excel 有哪些和 vlookup 一样重要的函数或功能？
8. 怎样才算精通 Excel？

如果觉得有用，点个赞再走呗~持续更新哦！！