完蛋！我被Vlookup包围了！

作者：小北童鞋

来源：芒种学院(ID：lazy_info)

这两天看到不少关于 Excel 更新的新功能介绍，并且 Vlookup 又双要被退休了！

虽然我承认 365 版本的确有不少函数比 Vlookup 都要好用！但是身边一统计，使用 365 的小伙伴甚至不到 1%！

而身边各式各样的文件，全部都被 Vlookup 包围了！

是的，没错，哪怕 1985 年就已经诞生的 Vlookup 函数，至今仍然被广泛使用！而且还有各种新技巧被挖掘出来！

花了一个周末的时候，小北整理了目前 Vlookup 的 16 种技巧，应该说是全网最全的 Vlookup 技巧合集了，当然在实际应用中，其实还能挖掘出更多技巧。

由于推文内容比较多，先来看下整体的大纲：

PS：练习文件在公众号「芒种学院」回复关键词「16」即可领取~

VLOOKUP基础用法

首先来看下 VLOOKUP 函数的基础用法，函数共有 4 个参数，如下：

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

▲左右滑动查看完整公式

4 个参数的解释如下：

1. 查找的值：要查找的词或单元格引用；

2. 查找区域：包含查找字段和返回字段的单元格区域，查找字段必须在查找区域的第 1 列；

3. 返回值所在列数：返回值在查找区域中的列数；

4. 匹配模式：0 为精确匹配，1 为模糊匹配；

是不是非常简单？当然我们会循序渐进将这 18 种技巧全部分享完毕。

VLOOKUP单条件查询

首先是 VLOOKUP 最简单的单条件查询，案例：“根据工号将对应工资进行匹配”，操作也很简单，输入公式：

=VLOOKUP(H2,A2:F11,6,0)

▲左右滑动查看完整公式

轻松即可将工号为 6 的员工工资匹配出来。

含义也很简单：

1. H2：要查找的单元格引用；

2. A2:F11：查找区域；

3. 6：工资字段位于查找区域的第 6 列；

4. 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)

▲左右滑动查看完整公式

公式非常复杂，整体思路如下：

1. 用ROW函数生成序列；

2. INDIRECT+ROW生成行数递增的区域；

3. COUNTIF计算部门的个数，进行编号；

4. 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 种用法技巧就分享到这里了，本次推文的练习文件也可以在公众号下载。