乐于分享
好东西不私藏

Excel万能函数LOOKUP,七个神技一次讲透

Excel万能函数LOOKUP,七个神技一次讲透

小伙伴们,大家好啊。

在Excel的世界里,VLOOKUP的名气很大,就像是少林寺的达摩院首座,功夫扎实,名门正派。但今天,我要给你介绍一位藏经阁里的“扫地僧”——LOOKUP函数。

这位大神,平时不显山露水,规矩还特别多(比如要求数据升序),但真到了处理复杂问题的关键时刻,它的“野路子”招式往往能四两拨千斤,让你在同事面前赚足面子。

今天就带你彻底盘一盘,这位万能查找函数的七大“隐藏神技”。

01 告别IF函数地狱,业绩评级一秒搞定

场景:你是HR或销售主管,月底要根据员工销售额,自动评定“S/A/B/C”等级。用IF函数嵌套到怀疑人生?LOOKUP一个公式秒杀。

数据实例:

A
B
C
D
E
F
员工
销售额
等级(公式列)
标准下限
等级
张三
8500
?
0
C
李四
28000
?
5000
B
王五
45000
?
20000
A
赵六
6200
?
35000
S
孙七
38000
?

公式:在C2单元格输入=LOOKUP(B2, E$2:E$5, F$2:F$5)向下填充到C6。

结果:张三8500→B,李四28000→A,王五45000→S,赵六6200→B,孙七38000→S。

白话文解释:告诉Excel,去E列里找B2单元格的值(销售额),如果找不到一模一样的,就找比它小的、最接近的那个数,然后返回F列对应的等级。唯一前提:E列的数字必须从小到大排好。

02 专治不服!合并单元格的“数据回填”术

场景:你拿到一张烂表,部门列全是合并单元格(比如“销售部”只出现在第一行,下面全是空的),想筛选、排序完全没法做。怎么把空白格都填上对应的部门名?

数据实例:

A
B
C
序号
部门(合并)
回填结果(公式列)
1
销售部
?
2
(空)
?
3
(空)
?
4
市场部
?
5
(空)
?
6
(空)
?

公式:在C2单元格输入=LOOKUP("做", B$2:B2)向下填充到C7。

结果:C2=销售部,C3=销售部,C4=销售部,C5=市场部,C6=市场部,C7=市场部。

白话文解释:"做"这个字在编码里很大。Excel在这个逐渐变大的区域里找它,肯定找不到,于是就“退而求其次”,返回这个区域里最后一个文本——正好就是我们想要的部门名称。向下拖动公式,完美填充!

03 拒绝手动翻找,永远返回最后一条非空记录

场景:在流水账里,你想动态获取客户最后一次下单的记录,或者员工信息表里最后一名有名字的员工。数据每天都在增加,怎么自动获取最新的那条?

数据实例:

A
姓名(名单列)
张三
李四
(空)
王五
(空)
赵六

公式:在任意空白单元格输入=LOOKUP(1, 0/(A:A<>""), A:A)

结果:返回“赵六”(A列最后一个非空单元格的内容)。

核心模式:=LOOKUP(1, 0/(条件区域=条件), 要返回的区域)

白话文解释:这是LOOKUP的“降龙十八掌”。它用0除以一堆“是/否”的条件,产生一组0和错误值。然后找1,找不到,就定位到最后一个0的位置,并返回对应内容。多条件? 简单!直接乘起来:=LOOKUP(1, 0/(条件1)/(条件2), 目标区域)

04 左右逢源,这才是真正的“逆向查询”

场景:你有商品编码和经理名单两列,编码在左,经理在右。现在想根据编码(左列)查找它左边的经理(右列)。VLOOKUP直接傻眼,LOOKUP表示毫无压力。

数据实例:

A
B
C
D
E
F
商品名称
商品编码
销售经理
查询编码
对应经理(公式)
冰箱
A001
王经理
A003
?
电视
A002
李经理
空调
A003
张经理
洗衣机
A004
赵经理

公式:在F2单元格输入=LOOKUP(1, 0/(E2 = B:B), C:C)

结果:F2返回“张经理”(因为A003对应的是张经理)。

白话文解释:在B列(编码列)找E2的值A003,找到了就返回对应C列(经理列)的值。管你在左在右,我都能给你“揪”出来。

05 条件再多也不怕,多条件查询一步到位

场景:公司有多个部门,每个部门都有叫“张伟”的。现在要根据“姓名”和“部门”两个条件,精准锁定唯一的销售经理。

数据实例:

A
B
C
D
E
F
G
H
销售经理
姓名
部门
查询姓名
查询部门
结果经理(公式)
王经理
张伟
销售部
张伟
市场部
?
李经理
刘洋
销售部
张经理
张伟
市场部
赵经理
陈晨
市场部

公式:在G2单元格输入=LOOKUP(1, 0/(E2 = B:B) / (F2 = C:C), A:A)

结果:G2返回“张经理”(因为张伟在市场部,对应的经理是张经理)。

白话文解释:这就是前面“多条件”的实战版。用除号/把两个条件连接起来,只有两个条件都满足(即分母不为0),才会被匹配。清晰又高效。

06 模糊匹配的王牌,关键字归类大法

场景:公司产品名称很规范,比如“华东区-销售-张三”、“华北区-财务-李四”。你需要根据产品名称里的关键字(如“销售”、“财务”),自动为它分好类别。

数据实例:

A
B
C
D
E
产品全称
类别(公式列)
关键字列表
对应类别
华东区-销售-张三
?
销售
业务线
华北区-财务-李四
?
财务
职能线
华南区-技术-王五
?
技术
研发线
华中区-销售-赵六
?
人力
支持线

公式:在B2单元格输入=LOOKUP(1, -FIND(D$2:D$5, A2), E$2:E$5)向下填充到B5。

结果:B2=业务线(因为包含“销售”),B3=职能线(包含“财务”),B4=研发线(包含“技术”),B5=业务线(包含“销售”)。

白话文解释:这个公式有点“心机”。它在A2里挨个查找所有关键字,找到的位置会变成数字(比如“销售”在第4位),找不到的就是错误值。加个负号把数字变负数(-4)。最后LOOKUP会找到最后一个负数(也就是最后一个被找到的关键字),并返回它的类别。注意:关键字列表要按字数从多到少排序,防止“华东”被“华”提前匹配。

07 专治各种不服:合并单元格+乱序数据的绝杀

场景:这是场景02和04的升级版。商品名称在B列是合并单元格,现在要根据商品名称(如“冰箱”),去查找它左侧C列对应的销售经理。怎么破?

数据实例:

A
B(合并)
C
D
E
F
序号
商品名称
销售经理
查询商品
对应经理(公式)
1
冰箱
王经理
冰箱
?
2
(空)
李经理
3
(空)
张经理
4
电视
赵经理
5
(空)
孙经理

公式:在F2单元格输入=LOOKUP("做", INDIRECT("C1:C" & MATCH(E2, B:B, 0)))

结果:因为MATCH找到“冰箱”在B列的起始位置是第1行,INDIRECT框定范围为C1:C1,LOOKUP返回这个区域里最后一个非空的文本,即C1的王经理。

白话文解释:这招是“组合拳”。先用MATCH精确找到“冰箱”在B列的起始位置(第1行),然后用INDIRECT函数框定一个动态范围,最后再用场景02的“找最大文本”套路,返回这个区域里最后一个非空的经理姓名。完美解决!

写在最后

LOOKUP函数就像一个深藏不露的高手,规矩多(默认升序),但一旦你掌握了它的脾气,它就能帮你解决各种VLOOKUP、HLOOKUP束手无策的“疑难杂症”。

今天这七招,不敢说让你立刻成大神,但足够你在日常工作中横着走了。如果觉得有用,别忘了点个【在看】和【分享】,让更多朋友告别加班!

我们下期见!

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料