未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
这样用LOOKUP,比一般的查找好用百倍!
本文作者:赵骄阳&小爽
本文编辑:竺兰


大家好,前面我们介绍过 Lookup 函数的基本用法。(文章见文末!

=LOOKUP(lookup_value,lookup_vector,[result_vector])=LOOKUP(查找值,查找区域,[返回区域)
▲ 左右滑动查看

之前提到:使用 Lookup 函数时,必须先对第二参数「查找区域」进行升序处理。

这时就有小伙伴纳闷了:写函数公式,还要时刻注意给原来的数据排序,这也太麻烦了!

别急!不想排序的话,Lookup 也有办法。

请往下看!

单条件查询


如下图,根据姓名查询年龄:


看起来好复杂啊!

不怕,以 G5 单元格中的公式为例,我们先来看看公式解析~

=LOOKUP(1,0/(F3=B3:B12),D3:D12)

❶ F3=B3:B12 先判断 B3:B12 (姓名列)是否等于 F3('杨山')。

如果是,则返回 True,否则返回 False。这时形成由 True 和 False 组成的数组。

❷ 0/({FALSE;FALSE......}),用 0 除以判断结果形成的数组。

0/TRUE=0,0/FALSE=#DIV/0!(在四则运算中,True 相当于 1,False 相当于 0),形成由 0 和#DIV/0!组成的数组。

如下图所示:


❸ Lookup 函数先在「查找区域」中查找与 1 匹配的值。

如果找不到,则继续找小于且最接近查找值的数值。

在「查找区域」中 0 最接近 1,所以结果返回 0 对应的 D3:D12 中的数据(85)。


PS:Lookup 函数是根据二分法进行跳跃式查找的,它会忽略#DIV/0!错误值,所以查找区域中仅存在数值 0,且 0 也是小于等于 1 的数值。

简言之,这个公式的关键在于第 2 参数:

0/(F3=B3:B12)生成一个由数值 0 和错误值#DIV/0! 组成的数组。

再从这个数组中找到小于或等于 1 的最大值 0,最后返回第 3 参数:D3:D12 对应的值。


 敲黑板:

当我们碰到单条件查找的问题时,可以按照下面的公式直接套用!

=LOOKUP(1,0/(条件=条件区域),返回区域)

看案例!

如下图,我们需要根据姓名查询性别:


以 F3 作为条件,B3:B12 为条件区域,C3:C12 为返回区域,套用:

=LOOKUP(1,0/(条件=条件区域),返回区域)

得到 G3 单元格公式:

=LOOKUP(1,0/(F3=B3:B12),C3:C12)

既然有单条件查询的套路公式,那自然有 Lookup 函数多条件查询的套路公式。

继续往下看!

多条件查询


Lookup 函数多条件查询的套路公式:

=LOOKUP(1,0/((条件 1=条件区域 1)*(条件 2=条件区域 2)*…*(条件 n=条件区域 n)),返回区域)
▲ 左右滑动查看

看案例!

如下图,根据采购日期和货品名称,查找对应的单价。


条件 1:F3 也就是采购日期
条件区域 1:B3:B8
条件 2:G3 也就是货品名称
条件区域 2:C3:C8
返回区域:D3:D8

在 H3 单元格中,直接套用公式:

=LOOKUP(1,0/(F3=B$3:B$8)*(G3=C$3:C$8),C$3:C$12)
▲ 左右滑动查看


前面讲的都是文本的精确匹配,那如果是文本的模糊匹配,我们应该怎么做呢?

这个还有没有套路公式哇?

这时候我们还需要借助一个 FIND 函数~

欲知详情,往下看!

模糊查询


Lookup 函数模糊查询的套路公式:

=LOOKUP(1,0/FIND(查找区域,查找值),返回区域)
▲ 左右滑动查看

看案例!根据类别(简称)查商品名称(全称)。


查找区域:E3:E10,也就是类别列
查找值:B3
返回区域:E3:E10

在 C3 单元格中,直接套用公式:

=LOOKUP(1,0/FIND(E$3:E$10,B3),E$3:$E10)
▲ 左右滑动查看


简单解释一下,这个函数公式运算的原理~

Find 函数基本语法:

Find 是用来返回一个字符串在另一个字符串中出现的起始位置(区分大小写)。

=FIND(find_text,within_text,[start_num])=FIND(查找值,在哪里找,从第几个字符开始查找)
▲ 左右滑动查看

以 C3 单元格公式为例:

=LOOKUP(1,0/FIND(E$3:E$10,B3),E$3:$E10)
▲ 左右滑动查看

如下图所示:


❶ 利用 Find 函数查找「简称在全称的位置情况」,如果找得到,数组对应就是位置的起始位置;如果找不到,则对应返回#VALUE 错误值。

查找值:类别列
在哪找:男童舒适适气棒球便服黑色男童便服

❷ 0/({#VALUE;#VALUE......}),用 0 除以 find 函数查找的结果形成的数组,0/错误值=错误值,0/数值=0,最后形成由 0 和#VALUE 组成的数组。

❸ 由于 Lookup 函数可以忽略错误值,所以,Lookup 函数先在查找区域中查找小于等于 1 的最大值,也就是 0,最后返回 0 的对应区域,也就是「便服」。


看到这里,大家是不是对 Lookup 函数的特殊用法:Lookup(1,0/(条件区域))有了进一步的认识呢?

总结一下


Lookup 函数第 2 参数可以通过构造 0/(条件)的形式,实现在乱序中进行查找。

Lookup 函数三种经典用法:

① 单条件查询经典用法:

=LOOKUP(1,0/(条件=条件区域),返回区域)

② 多条件查询经典用法:

=LOOKUP(1,0/((条件 1=条件区域 1)*(条件 2=条件区域 2)*…*(条件 n=条件区域 n)),返回区域)
▲ 左右滑动查看

③ 模糊查询经典用法:
=LOOKUP(1,0/FIND(查找区域,查找值),返回区域)
▲ 左右滑动查看
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP阅读全文并永久保存 更多类似文章
猜你喜欢
类似文章
Excel解析lookup的经典查找方式
LOOKUP函数的几种经典用法
我才是Excel的查找引用之王,甩Vlookup几条街!
9个LOOKUP函数经典用法,学会秒变EXCEL达人!
Excel LOOKUP函数的几种经典用法非常实用工作中用得着
听说你还不会Lookup?用过的人都说香,错过后悔1个亿!
更多类似文章 >>
生活服务
搜索
热点新闻
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!