今天分享 xlookup 函数的一些特定需求的用法。看过我之前推文的同学已经知道,这个函数比传统的 vlookup 简化了用法,不需要再推算查找区域和返回区域之间的位置。
可能很多同学以为 xlookup 的参数因此简化成了 3 个,非也,简化使用难度却不简化功能,该函数的参数甚至多达 6 个。今天就跟大家好好讲解一下第 5 个参数的用法。
案例 1:模糊查找
下图 1 中左侧的数据表是销售获客数列表,请根据右侧的姓名缩写模糊匹配出对应的获客数,效果如下图 2 所示。
解决方案 1:
先看一下用 vlookup 如何进行模糊查找。
1. 选中 E2:E4 区域 --> 输入以下公式 --> 按 Ctrl+Enter:
=VLOOKUP("*"&D2&"*",A:B,2,0)
公式释义:
"*"&D2&"*":“*”是通配符,“&”是连接符,结果为“*D2*”,表示查找区域内所有包含 D2 内容的单元格;
其余就是 vlookup 函数的常规参数,不特意解释了。
那么,基于同样的模糊查找原理,将函数替换成 xlookup 试试。
2. 重新选中 E2:E4 区域 --> 输入以下公式 --> 按 Ctrl+Enter:
=XLOOKUP("*"&D2&"*",A:A,B:B)
公式释义:
"*"&D2&"*":查找值,完全复制了 vlookup 中的模糊参数;
A:A:查找区域
B:B:返回区域
然而结果却出错了。这是因为 xlookup 函数在默认情况下并不支持通配符匹配,取而代之是增加了第 5 个参数,可以用于设置匹配模式,其中就包括了通配符配。
3. 将区域内的公式修改如下:
=XLOOKUP("*"&D2&"*",A:A,B:B,,2)
公式释义:
与之前的公式相比,增加了第 5 个参数“2”,从下图 1 可以看出,2 表示“通配符匹配”
案例 2:区域查找
下图 1 是所有销售的获客数及评级标准对照表,规则如下:
>=0 且
>=10 且
>=15 且
>=20:A
请对照规则计算出每个销售的等级,如下图 2 所示。
解决方案 2:
1. 在 C2 单元格中输入以下公式 --> 下拉复制公式:
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)
公式释义:
第 5 个参数“-1”表示“精确匹配或下一个较小的项”;
请注意两个区域的绝对引用
领取专属 10元无门槛券
私享最新 技术干货