比 VLOOKUP 更好用，Excel 关键词查找函数

前几日，一位职场 Exceller 提出了一个很接地气的实战问题：

老师，下面这个表，能否不通过辅助列，直接设置公式按门店名称关键字，查询对应销售额？

F2 单元格公式：

=LOOKUP(1,0/(MID(E2,FIND("/"E2)+1,LEN(E2)-FIND("/"E2))=$C$2$C$6)$B$2$B$6)

C2 单元格辅助公式

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

不得不说，这位小伙伴在 Excel 公式使用方面还是很有料的，通过辅助列来简化公式，观察规律并使用正确的函数。

有丰富的函数基础知识储备，还会使用 LOOKUP 两分法，相信这位小伙伴已是妥妥的职场 Excel 达人了。

但当前公式仍有优化空间。比如，F2 单元格公式从 E2 单元格提取店名时，使用 LEN (E2)-FIND ("/",E2) 来获取店名长度是没有必要的。

因为 E2 文本中，店名后并无其他内容，只需使用一个较大的数作为 MID 的第三个参数，比如数字 10，就能够完整提取店名。

F2 单元格公式-简单优化：

=LOOKUP(1,0/(MID(E2,FIND("/"E2)+1,10)=$C$2$C$6)$B$2$B$6)

言归正传。不通过辅助列解决这一关键字查询问题，方法有很多。接下来，小花就分享其中的四个不同公式。

还是遵循提问者的思路，从 E 列中提取关键字 —— 店名，然后使用 LOOKUP 的两分法来查询目标值。只是，我们不仅需要使用 FIND 函数来提取店名，更需要用它完成模糊匹配。

1、正向查询

=LOOKUP(1,0/FIND(MID(E2,FIND("/"E2)+1,10)$A$2$A$6)$B$2$B$6)

公式说明：

① MID(E2,FIND("/",E2)+1,10)

显然，门店简称中，"/" 后即为店名。使用 FIND 查找出 "/" 在 E2 文本中的位置，+1 即为店名文本首个字符的位置，再使用 MID 函数提取店名即可。

② 0/FIND(①,$A$2:$A$6)

再次使用 FIND 函数，分别在 A2:A6 单元格查找店名文本出现的位置。如果单元格文本存在该店名文本，则返回代表初始位置的数值，否则返回错误值#VALUE!，即得到数组 {11;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。

0 除以该数组，得到 {0;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。

特别需要强调的是，LOOKUP 的两分法在构建第二个参数时，所有非零的数都表示满足查询条件，所有的零值和错误值都表示不满足条件，并不要求除数总是一组逻辑值或 0/1 的组合。

③ LOOKUP(1,②,$B$2:$B$6)

经典的 LOOKUP 两分法，忽略错误值，查询小于且最接近于目标值 1 的最后一个有效值，从结果区域返回其相应位置的值。

此处除②中 FIND 函数返回有效数值的位置外，其余均为错误，因此，A2:A6 中包含目标店名的单元格 A2 在 B2:B6 的对应位置 B2 的值 46，即为返回值。

当然了，如果目标店名很容易提取，使用 VLOOKUP 和通配符来解决问题也是非常方便的。

2、模糊查询

=VLOOKUP("*"&MID(E2,FIND("/"E2)+1,10)&"*"$A$2$B$6,2,0)

同时，由于查询结果为数值且唯一，使用 SUMIF 进行条件求和也有异曲同工之妙。

3、求和查询

=SUMIF(A:A"*"&MID(E2,FIND("/"E2)+1,10)&"*"B:B)

但有些时候，从 E 列单元格提取目标店名并不容易。比如下图，E 列并不存在明显店名标识特征，不方便提取店名，而在 A 列提取店名显然更容易。

此时，我们需要在优化公式 1 的基础上稍加变通，从 A 列提取店名，对 E 列进行反向查询。

4、反向查询

=LOOKUP(1,0/FIND(MID($A$2$A$6,FIND("("$A$2$A$6)+1,10)E2&")")$B$2$B$6)

公式说明：

① MID ($A$2:$A$6,FIND ("(",$A$2:$A$6)+1,10) 将 A2:A6 单元格中的店名都提取出来。

FIND (①,E2&")") 查找这些店名是否存在于 E2 单元格中，由于片段①提取的店名时没有去除最后的右括号 ")"，于是需使用 E2&")" 作为匹配文本，这点需格外留意。

最后，LOOKUP 两分法照方抓药，解决问题。

以上，就是小花分享的关键字查询实战案例，解决方法包含如下几种：

❶ 使用 FIND 在多个单元格中匹配目标文本的 LOOKUP 正向查询公式；

❷ VLOOKUP 与通配符组成的模糊查询公式；

❸ SUMIF 求和实现对唯一数值的查询作用；

❹ 使用 FIND 将多个文本匹配目标单元格的 LOOKUP 反向查询公式。

上述公式你学会了吗？如果你也在实际工作中遇到难以解决的公式问题，欢迎留言与我们探讨！

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小花

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。