作者：小北童鞋
来源：芒种学院(ID：lazy_info)
这两天看到不少关于 Excel 更新的新功能介绍，并且 Vlookup 又双要被退休了！
虽然我承认 365 版本的确有不少函数比 Vlookup 都要好用！但是身边一统计，使用 365 的小伙伴甚至不到 1%！
而身边各式各样的文件，全部都被 Vlookup 包围了！
是的，没错，哪怕 1985 年就已经诞生的 Vlookup 函数，至今仍然被广泛使用！而且还有各种新技巧被挖掘出来！
花了一个周末的时候，小北整理了目前 Vlookup 的 16 种技巧，应该说是全网最全的 Vlookup 技巧合集了，当然在实际应用中，其实还能挖掘出更多技巧。
由于推文内容比较多，先来看下整体的大纲：
PS：练习文件在公众号「芒种学院」回复关键词「16」即可领取~VLOOKUP基础用法
首先来看下 VLOOKUP 函数的基础用法，函数共有 4 个参数，如下：
= VLOOKUP(查找的值, 查找区域, 返回值所在列数, 匹配模式)▲左右滑动查看完整公式
4 个参数的解释如下：
查找的值：要查找的词或单元格引用；
查找区域：包含查找字段和返回字段的单元格区域，查找字段必须在查找区域的第 1 列；
返回值所在列数：返回值在查找区域中的列数；
匹配模式：0 为精确匹配，1 为模糊匹配；
是不是非常简单？当然我们会循序渐进将这 18 种技巧全部分享完毕。
VLOOKUP单条件查询
首先是 VLOOKUP 最简单的单条件查询，案例：“根据工号将对应工资进行匹配”，操作也很简单，输入公式：
=VLOOKUP(H2,A2:F11,6,0)▲左右滑动查看完整公式
轻松即可将工号为 6 的员工工资匹配出来。
含义也很简单：
H2：要查找的单元格引用；
A2:F11：查找区域；
6：工资字段位于查找区域的第 6 列；
0：精准匹配；
现在终于知道为什么有的 VLOOKUP 的第 3 个参数有的写 2 ，有的写 6 了吧~
屏蔽检索不到错误
如果数据不存在，那么 VLOOKUP 则会返回 #N/A 错误，在某些场合下，想将这些错误全部屏蔽掉，可以使用 IFERROR 函数处理。
例如使用公式，当找不到数据的时候返回 “/”：
=IFERROR(VLOOKUP(H2,A2:F11,6,0),"/")▲左右滑动查看完整公式
当然，VLOOKUP 对数据的匹配是非常严格的，差一个空格都会认为并不是同个字符，例如 “芒种” 和 “芒种 ” 并不匹配，因为差了一个空格。
反向匹配技巧
前面提到过查找词必须在查找区域的第 1 列，如果不在第 1 列需要如何处理呢？
例如：“根据姓名查找对应的工号”，其实可以用 IF 数组公式来对调下位置即可，公式如下：
=VLOOKUP(H2,IF({1,0},B2:B11,A2:A11),2,0)▲左右滑动查看完整公式
由于是数组公式，必须使用三键 Ctrl+Shift+Enter 结束。
这里“IF({1,0}, B2:B11, A2:A11)”的用法其实也非常好理解，将 B 列和 A 列互换下位置，然后组合在一起，这样“姓名”又跑到第 1 列了。
模糊关键词检索
如果想匹配出包含某个词的数据，使用 VLOOKUP 也可以轻松实现。
例如：“查找包含阳字姓名的员工工资”，输入如下公式：
=VLOOKUP("*"&H2&"*",B2:F11,5,0)▲左右滑动查看完整公式
这里的技巧主要用到了通配符「*」，而「*」在公式中可以代表任意字符。
代替IF实现区间查找
在实际工作中，并不是所有的匹配都是精准匹配的，也常常会出现「区间匹配」。
例如：“根据不同的销量计算提成数”，使用如下公式：
=VLOOKUP(D2,A2:B6,2,1)▲左右滑动查看完整公式
将函数的第 4 个参数改成 1 ，同时查找区域的数据必须从小往大排列，如下：
如果使用 IF 来完成需求，就非常复杂了，嵌套一堆，还容易出错，VLOOKUP 轻松就能搞定。
去除空格/不可见字符匹配
在部分不规范的数据中，命名肉眼看着一模一样，却怎么也匹配不上，大概率是因为查找关键词或者查找区域中存在空格导致的。
这个时候可以使用 SUBSTITUTE 或者 CLEAN 函数进行处理，公式如下：
=VLOOKUP(SUBSTITUTE(H2," ",""),B2:F11,2,0)▲左右滑动查看完整公式
技巧也非常简单，利用 SUBSTITUTE 将空格替换掉即可，如果是不可见的字符，可以将 SUBSTITUTE 换成 CLEAN 即可。
横向匹配返回多列
现在查找单个数值的技巧我们已经掌握了，如果要查找返回 N 列数据呢？难道要写 N 个公式？其实只需要配合 COLUMN 函数即可实现。
例如：“根据工号返回姓名、部门、性别、年龄、工龄等字段”，只需要输入公式：
=VLOOKUP($H$2,$A$2:$F$11,COLUMN(B1),0)▲左右滑动查看完整公式
这里用到的技巧为：COLUMN 函数动态生成 2、3、4、...的序列，向右拖动即可自动将多列返回，如下。
使用 COLUMN 返回多列的场景适合于字段是连续的，如果数据并非连续，可以使用 MATCH 来实现，后面会讲解到。
匹配多表查找
在实际工作中，可能存在不同条件检索不同表的情况，例如：深圳员工检索深圳表，广州员工检索广州表，使用 VLOOKUP+IF 即可实现。
例如：“返回不同城市员工的工资”，使用如下公式：
=VLOOKUP(B2,IF(A2="深圳",A6:F15,H6:M15),6,0)▲左右滑动查看完整公式
这里用 IF 判断 A2 单元格是否为“深圳”，如果是则查找区域为 A6:F15，否则为 H6:M15，完美实现多表查找。
除了利用 IF ，还可以利用 OFFSET 来实现，相对来说会更复杂，不过支持的场景会更多，更详细的技巧会在 VLOOKUP 微课中讲解。
多条件匹配查找
前面分享了 9 个技巧，不过都是单条件的，利用 VLOOKUP 实现多条件查找其实也很简单，同样用到 IF 数组公式。
例如：“根据姓名+部门查找对应工资”，输入如下公式：
=VLOOKUP(H2&I2,IF({1,0},B2:B11&C2:C11,F2:F11),2,0)▲左右滑动查看完整公式
由于姓名、部门可能存在重复，两个字段才能确定唯一一条数据。
这里使用 & 将两个关键词拼接起来，再使用 IF 数组公式将对应两列也拼接起来，同时组装上返回区域作为 VLOOKUP 的第 2 个参数。
VLOOKUP 多条件查询是数组公式，需要按 Ctrl+Shift+Enter 结束，否则会返回错误信息。
查找区域合并单元格检索
如果查找区域中存在合并单元格呢？也可以解决。
例如：“根据部门+姓名查找出对应的工资”，使用如下公式：
=VLOOKUP(F2,OFFSET($B$1,MATCH(E2,A:A,)-1,):C99,2,0)▲左右滑动查看完整公式
这里使用 MATCH 找到部门所在的行号，然后用 OFFSET 向下进行偏移，通过这 2 个函数可以构建出动态匹配区域，即可实现需求。
这个案例会稍微难一点，如果不理解，建议将单元格拆分后才查找，会快很多。
查找词合并单元格查找
如果查找词所在列存在合并单元格，这种情况 VLOOKUP 也可以轻松搞定。
例如：“根据部门返回月度奖金”，使用如下公式：
=VLOOKUP(VLOOKUP("座",$D$2:D2,1),$A$2:$B$4,2,0)▲左右滑动查看完整公式
这里嵌套了 VLOOKUP 函数，其中内部的函数用于查找 D 列截止至本行的最后一个非空值，这样就可以将查找词找到了。
查找返回多个结果
关键词和返回结果是 1 对 N 的关系，如何将所有结果都返回呢？
例如：“找出市场部所有员工姓名”，输入如下公式：
=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)▲左右滑动查看完整公式
公式非常复杂，整体思路如下：
用ROW函数生成序列；
INDIRECT+ROW生成行数递增的区域；
COUNTIF计算部门的个数，进行编号；
IF数组公式将数据构建成一个新数组；
同样也是数组公式，三键结束后，如下：
公式兼容性虽然好，但是难度实在不小，如果想成为函数高手，这个公式也非常值得研究，另外 365 版本还有 FILTER 函数，会更便捷一些。
查找结果返回于同个单元格
依旧是返回多个结果，不过将结果一次性返回到一个单元格中，单纯利用 VLOOKUP 实现起来还是比较困难的，但是可以借助辅助列。
共有 2 个公式，如下：
G2 = C2&","&IFERROR(VLOOKUP(B2,B3:$G$12,6,),"")I2 = VLOOKUP(H2,$B$2:$G$11,6,)
▲左右滑动查看完整公式
这里用到了函数调用自身引用单元格的技巧将找到的数据依次拼接，最后使用二分法进行匹配，如下：
当然利用 VLOOKUP 实现这个需求难度的确过大，如果版本比较新，可以使用 TEXTJOIN+IF/FILTER 函数实现，会更简洁一些。
查找最后一个结果
如果查找的数据为多条，只想要最后一条，用 VLOOKUP 也可以实现。
例如：“找到市场部的最后一位职员”，输入如下公式：
=VLOOKUP(1,IF({1,0},0/(C2:C11=H2),B2:B11),2)▲左右滑动查看完整公式
当忽略 VLOOKUP 的最后一个参数时，函数会使用二分法进行查找，同时用 0/条件 可以将不符合条件的数值变成错误值，符合的变成 0。
最后用 1 查找最后一个 0 即可实现需求，看起来很难，其实马马虎虎。
VLOOKUP跨多表匹配
如果数据被分散在 N 张 Sheet 表中，并不确定是其中的哪个，其实利用 INDIRECT+VLOOKUP 也可以轻松实现跨表匹配。
例如：“有深圳、广州、上海共计 3 张表，匹配员工工资”，输入如下公式：
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"深圳";"广州";"上海"}&"!b:b"),A2),{"深圳";"广州";"上海"})&"!b:f"),5,0)▲左右滑动查看完整公式
公式非常长，但是逻辑结构不难。
利用 INDIRECT 函数将所有表的数据全部导入进行匹配，找到非错误的值即可，如下。
不过对于这类表，建议使用 PQ 将表合并后在进行匹配，这样效率会高很多。
好了，那么关于 VLOOKUP 函数的 16 种用法技巧就分享到这里了，本次推文的练习文件也可以在公众号下载。
关注公众号，回复「16」即可获得这份练习文件
你学会了么？学不会的 VLOOKUP ，终于可以学会了~
关于「VLOOKUP」的技巧分享就到这里了，希望对你有帮助~
每一次安利都花了很多精力来写，如果认为对你有帮助，不妨帮小北扩散和点赞，谢谢大家！
想了解数据处理和信息图表的更多思路与技巧？「Excel实战课，让你的图表会说话」超值 Excel 课程了解一下——
