提到Excel中使用频率最高的函数,其实不是Vlookup就是Lookup,这两个函数的搭配使用,几乎能解决Excel所有的匹配问题。
花了一周时间,我给大家 整理了Vlookup+Lookup共计30种超详细的用法。
应该可以说是全网最全的Lookup系列函数技巧了。
当然在实际应用中,其实还能挖掘出更多技巧。
由于推文内容比较多,建议收藏后在阅读,下次就能轻松找到啦~
首先来看下 VLOOKUP 函数的基础用法,函数共有 4 个参数,如下:
= VLOOKUP(查找的值, 查找区域, 返回值所在列数, 匹配模式)▲左右滑动查看完整公式
4 个参数的解释如下:
- 查找的值:要查找的词或单元格引用;
- 查找区域:包含查找字段和返回字段的单元格区域,查找字段必须在查找区域的第 1 列;
- 返回值所在列数:返回值在查找区域中的列数;
- 匹配模式:0 为精确匹配,1 为模糊匹配;
是不是非常简单?完事开头难,基础语法看着简单,实则容易踩坑。
接下来看下 LOOKUP 函数的基础用法,函数共有 3 个参数,如下:
▲左右滑动查看完整公式
3 个参数的解释如下:
- 查找的值:要查找的词或单元格引用;
- 查找区域:要查找的区域;
- 返回区域:要返回的区域,必须是一行或者一列,查找和返回区域必须大小一致;
看着比Vlookup简单一些,当然在实际使用中,还会区分已排序和未排序的场景。
首先是 VLOOKUP 最简单的单条件查询,案例:“根据工号将对应工资进行匹配”,操作也很简单,输入公式:
▲左右滑动查看完整公式
轻松即可将工号为 6 的员工工资匹配出来。
- H2:要查找的单元格引用;
- 6:工资字段位于查找区域的第 6 列;
现在终于知道为什么有的 VLOOKUP 的第 3 个参数有的写 2 ,有的写 6 了吧~
如果要查找的字段所在列已经排序,那么可以直接使用 Lookup 查找。
例如查找“西瓜”的数量,使用公式:
▲左右滑动查看完整公式
在排序后,即可轻松将对应的数据匹配出来。
不过这种使用并不多,毕竟数据能排序后再匹配,场景很少。
绝大部分匹配场合都是未排序的,这时候就需要使用0/的方法来匹配。
依旧是查找“西瓜”的数据,公式如下:
▲左右滑动查看完整公式
函数的套路也很简单,用0除以判断条件,只有相符的才不会发生除0错误,使用1进行匹配,即可将对应数值匹配出来。
当然如果不理解公式也无所谓,下次碰到直接套用即可:
▲左右滑动查看完整公式
如果数据不存在,那么 V/Lookup 则会返回 #N/A 错误,在某些场合下,想将这些错误全部屏蔽掉,可以使用 IFERROR 函数处理。
例如使用公式,当找不到数据的时候返回 “/”:
▲左右滑动查看完整公式
当然,VLOOKUP 对数据的匹配是非常严格的,差一个空格都会认为并不是同个字符,例如 “芒种” 和 “芒种 ” 并不匹配,因为差了一个空格。
Lookup函数屏蔽错误的技巧也一模一样,最外层套多一个iferror即可。
前面提到过查找词必须在查找区域的第 1 列,如果不在第 1 列需要如何处理呢?
例如:“ 根据姓名查找对应的工号”,其实可以 用 IF 数组公式来对调下位置即可,公式如下:
▲左右滑动查看完整公式
这里“IF({1,0}, B2:B11, A2:A11)”的用法其实也非常好理解,将 B 列和 A 列互换下位置,然后组合在一起,这样“姓名”又跑到第 1 列了。
而在Lookup函数中,没有反向与正向的概念,因为返回值区域均是单独列出来的,所以使用技巧和普通查询一模一样。
如果想匹配出包含某个词的数据,使用 VLOOKUP 也可以轻松实现。
例如:“查找包含阳字姓名的员工工资”,输入如下公式:
▲左右滑动查看完整公式
这里的技巧主要用到了通配符「*」,而「*」在公式中可以代表任意字符。
除了精准匹配,和 Vlookup 一样,要实现模糊匹配也非常简单,不过原理不太一致,Lookup 函数需要配合 Find 函数使用。
例如查找存在“西”字符串的水果数量,使用公式:
▲左右滑动查看完整公式
由于 Find 函数会返回字符串所在的位置数值,当找不到时则返回错误,这里的原理和查找最后一个非数值内容很接近,给一个很大的数让 Lookup 匹配即可。
在实际工作中,并不是所有的匹配都是精准匹配的,也常常会出现「 区间匹配」。
例如:“根据不同的销量计算提成数”,使用如下公式:
▲左右滑动查看完整公式
将函数的第 4 个参数改成 1 ,同时 查找区域的数据必须从小往大排列,如下:
如果使用 IF 来完成需求,就非常复杂了,嵌套一堆,还容易出错,VLOOKUP 轻松就能搞定。
在实际应用场景中,Lookup 常常会代替 Ifs 函数,用于简化区间匹配。
在 数字已经排序的场景下,利用 Lookup 的特性,可以轻松实现区间匹配。
例如想查找“ 销量为80”的提成,使用公式:
▲左右滑动查看完整公式
原理是 Lookup 在找不到匹配数值的时候,会找一个小于匹配数里的最大值进行匹配,这样就可以巧妙实现区间匹配了,不过数据需要进行排序。
这个公式如果要使用 Ifs 或者 If 来写,就非常麻烦了,Lookup 一句话搞定。
去除空格/不可见字符匹配
在部分不规范的数据中,命名肉眼看着一模一样,却怎么也匹配不上,大概率是因为查找关键词或者查找区域中存在空格导致的。
这个时候可以使用 SUBSTITUTE 或者 CLEAN 函数进行处理,公式如下:
▲左右滑动查看完整公式
技巧也非常简单,利用 SUBSTITUTE 将空格替换掉即可,如果是不可见的字符,可以将 SUBSTITUTE 换成 CLEAN 即可。
现在查找单个数值的技巧我们已经掌握了,如果要查找返回 N 列数据呢?难道要写 N 个公式?其实只需要配合 COLUMN 函数即可实现。
例如:“根据工号返回姓名、部门、性别、年龄、工龄等字段”,只需要输入公式:
▲左右滑动查看完整公式
这里用到的技巧为:COLUMN 函数动态生成 2、3、4、...的序列,向右拖动即可自动将多列返回,如下。
使用 COLUMN 返回多列的场景适合于字段是连续的,如果数据并非连续,可以使用 MATCH 来实现,后面会讲解到。
Lookup函数由于返回值列单独拎出来,所以只需要处理好引用,即可一键向右填充返回多列的匹配数值。
在实际工作中,可能存在不同条件检索不同表的情况,例如:深圳员工检索深圳表,广州员工检索广州表,使用 VLOOKUP+IF 即可实现。
例如:“返回不同城市员工的工资”,使用如下公式:
▲左右滑动查看完整公式
这里用 IF 判断 A2 单元格是否为“深圳”,如果是则查找区域为 A6:F15,否则为 H6:M15,完美实现多表查找。
除了利用 IF ,还可以利用 OFFSET 来实现,相对来说会更复杂,不过支持的场景会更多,更详细的技巧会在 VLOOKUP 微课中讲解。
前面分享了 14 个技巧,不过都是但条件的,利用 VLOOKUP 实现多条件查找其实也很简单,同样用到 IF 数组公式。
例如:“ 根据姓名+部门查找对应工资”,输入如下公式:
▲左右滑动查看完整公式
由于姓名、部门可能存在重复,两个字段才能确定唯一一条数据。
这里使用 & 将两个关键词拼接起来,再使用 IF 数组公式将对应两列也拼接起来,同时组装上返回区域作为 VLOOKUP 的第 2 个参数。
VLOOKUP 多条件查询是数组公式,需要按 Ctrl+Shift+Enter 结束,否则会返回错误信息。
换成Lookup函数进行多条件匹配会更简单,只需将多个条件相乘即可。
例如:查找“广东西瓜”的数量,使用公式如下:
▲左右滑动查看完整公式
有 N 个条件,就将 N 个条件相乘即可,和单条件其实没啥区别。
Vlookup查找区域合并单元格检索
如果查找区域中存在合并单元格呢?也可以解决。
例如:“根据部门+姓名查找出对应的工资”,使用如下公式:
▲左右滑动查看完整公式
这里使用 MATCH 找到部门所在的行号,然后用 OFFSET 向下进行偏移,通过这 2 个函数可以构建出动态匹配区域,即可实现需求。
这个案例会稍微难一点,如果不理解,建议将单元格拆分后才查找,会快很多。
Vlookup查找词合并单元格查找
如果查找词所在列存在合并单元格,这种情况 VLOOKUP 也可以轻松搞定。
例如:“根据部门返回月度奖金”,使用如下公式:
▲左右滑动查看完整公式
这里嵌套了 VLOOKUP 函数,其中内部的函数用于查找 D 列截止至本行的最后一个非空值,这样就可以将查找词找到了。
Vlookup查找返回多个结果
关键词和返回结果是 1 对 N 的关系,如何将所有结果都返回呢?
例如:“找出市场部所有员工姓名”,输入如下公式:
▲左右滑动查看完整公式
公式非常复杂,整体思路如下:
- 用ROW函数生成序列;
- COUNTIF计算部门的个数,进行编号;
- IF数组公式将数据构建成一个新数组;
同样也是数组公式,三键结束后,如下:
公式兼容性虽然好,但是难度实在不小,如果想成为函数高手,这个公式也非常值得研究,另外 365 版本还有 FILTER 函数,会更便捷一些。
Lookup查找最后一个非数值
“座”在Excel中是一个很特殊的字,放到Lookup 函数中常用于查找最后一个非数值内容。
例如查找A列最后一个非数值内容,使用公式:
▲左右滑动查看完整公式
原理也很简单,”座“是汉字中编码相对靠后的数值,Lookup 函数如果找不到,则会返回查找区域中小于/等于”座“的最大值进行匹配。
这样就可以找到最后一个非数值了,效果如下:
Lookup查找最后一个数值
同样的原理,想要查找最后一个数值,也只需要提供一个足够大的数给 Lookup 进行匹配即可,例如最常见的大数:9E307。
查找 B 列的最后一个数字,使用公式:
▲左右滑动查看完整公式
Lookup查找最后一个非空内容
除了数值/非数值,Lookup还可以查找最后一个非空内容,思路也非常简单,将查找值替换成空,条件使用不等进行匹配即可。
例如查找A列的最后一个非空内容,使用公式:
▲左右滑动查看完整公式
原理也类似 Lookup 会匹配最小非错误数值,如果理解不了,记住套路即可:
▲左右滑动查看完整公式
Lookup多条数据匹配最后一条
和 Vlookup 会返回匹配多条数据中的第一条恰好相反,如果匹配区域中存在多条匹配的数据,则 Lookup 函数默认会匹配最后一条。
例如查找“西瓜”的最后一条数据,使用公式:
▲左右滑动查看完整公式
Vlookup查找最后一个结果
如果查找的数据为多条,只想要最后一条,用 VLOOKUP 也可以实现。
例如:“找到市场部的最后一位职员”,输入如下公式:
▲左右滑动查看完整公式
当忽略 VLOOKUP 的最后一个参数时,函数会使用二分法进行查找,同时用 0/条件 可以将不符合条件的数值变成错误值,符合的变成 0。
最后用 1 查找最后一个 0 即可实现需求,看起来很难,其实马马虎虎。
Lookup查找结果返回于同个单元格
依旧是返回多个结果,不过将结果一次性返回到一个单元格中,单纯利用 VLOOKUP 实现起来还是比较困难的,但是可以借助辅助列。
共有 2 个公式,如下:
▲左右滑动查看完整公式
这里用到了函数调用自身引用单元格的技巧将找到的数据依次拼接,最后使用二分法进行匹配,如下:
当然利用 VLOOKUP 实现这个需求难度的确过大,如果版本比较新,可以使用 TEXTJOIN+IF/FILTER 函数实现,会更简洁一些。
如果数据被分散在 N 张 Sheet 表中,并不确定是其中的哪个,其实利用 INDIRECT+VLOOKUP 也可以轻松实现跨表匹配。
例如:“ 有深圳、广州、上海共计 3 张表,匹配员工工资”,输入如下公式:
▲左右滑动查看完整公式
公式非常长,但是逻辑结构不难。
利用 INDIRECT 函数将所有表的数据全部导入进行匹配,找到非错误的值即可,如下。
不过对于这类表,建议使用 PQ 将表合并后在进行匹配,这样效率会高很多。
Lookup全称查找简称进行匹配
另外一种更加复杂的场景是根据全称去查找简称,使用 Vlookup 就很难实现了,而 Lookup 函数轻松实现。
例如根据地址去匹配所在省份的行政中心,使用公式:
▲左右滑动查看完整公式
原理和模糊匹配非常接近,只是将 Find 函数的两个参数颠倒下位置即可。
Lookup提取数据开头的数值
作为匹配中最难的一道题,在数字+中文+英文的混合中,分别将开头/结尾/任意位置的数值提取出来,其他函数几乎办不到。
而 Lookup 可以,套路也很简单,利用 LEFT + 数组公式逐个提取数值匹配。
例如找到最开头的数值,使用如下公式:
▲左右滑动查看完整公式
Lookup提取数据结尾的数值
同样,如果要提取数据结尾的数值,将 Left 函数换成 Right 即可。
▲左右滑动查看完整公式
公式实现效果如下,轻松匹配出来:
相比匹配头和尾,匹配任意位置的数值就非常复杂了,这里需要用到 MID+MATCH+ROW 函数的配合。
▲左右滑动查看完整公式
好啦,其实 哪怕看到这里也很难一次性掌握这30个函数技巧的,记得收藏起来,下次要用的时候,就可以直接查找啦~
最后大家经常用Vlookup还是Lookup呢?欢迎在评论区中分享哦~
好了,那么今天的 「 Vlookup+Lookup」的30种技巧就分享到这里了,如果你还有想学的 Excel 技巧,不妨在评论区留言哦~
当然,也可以进我们的读者群,和老师同学们一起交流讨论~~