ARTICLE · 1071492
3,000行Excel里找10个人 XLOOKUP 还是 FILTER
3,000行Excel里找10个人，你还在一个一个搜？
老板甩给你一串名单，别再 Ctrl+F 一个个找了。
正当，春和景明，波澜不惊，老板突然给你发来一串名字：
张伟陈静刘洋王磊李敏……
然后说：
“帮我把这10个人的资料找出来。”
你打开员工表。3,000多行。里面有：
（贵公司好big）
怎么办？
很多人的第一反应可能是：
Ctrl + F
输入：
张伟
找到、复制、再搜：
陈静
再复制 x 10；10个人好像也不是不能干。
但是如果老板给你的不是10个人，而是：
50个人呢？
我们先思考0.5秒，用 XOOKUP 还是 FILTER 。。。
01｜先别一个一个搜
假设完整的员工资料放在：
A2:E3001
我们把老板给你的10个人名单放在：
G2:G11 （当然也可以放在一个新的表里A2:A11)
如下：
如果你会XLOOKUP，可能会在旁边输入：
=XLOOKUP(G2,B2:B3001,A2:E3001)
Excel的运算逻辑是什么？
Excel拿：
G2里的“张伟”
去：
B2:B3001的姓名列
寻找。
找到以后，把这个人的：
工号 + 姓名 + 部门 + 城市 + 工资
一次全部返回。
这里其实就是我们之前讲过的：
XLOOKUP多列溢出。
所以输入一个公式以后：
5列一起出来。但是，这还只是：
查一个人。
02｜10个人，还需要把公式拖10次吗？
其实不用。
这里有一个很多人可能没用过的XLOOKUP玩法。
我们把：G2
改成：G2:G11
整个公式变成：
=XLOOKUP(G2:G11,B2:B3001,A2:E3001)
注意发生了什么。
现在我们告诉Excel：
帮我找G2:G11这10个人。
按下 Enter。结果：
10个人 × 5列资料，一次全部出来。
甚至老板明天给你：
100个人
逻辑也是一样。
03｜为什么一个公式能出来这么多结果？
这里稍微讲一点原理。
很多人理解Excel公式，还是：
一个公式 → 一个答案
但新版Excel已经不完全是这样了。
比如：
=XLOOKUP(G2:G11,B2:B3001,A2:E3001)
这里一次给XLOOKUP：
10个查找值
而每找到一个人，又要求它返回：
5列资料
于是最终结果自然就是：
10行 × 5列
Excel会自动把结果铺到周围单元格。
这就是：动态数组
你不用记这个名字也没关系。只需要记住一个变化：
一个公式，不一定只能返回一个结果。
这也是Microsoft 365版Excel和很多人印象里的老Excel很不一样的地方。
04｜等等，老板又改需求了
刚做完。老板走过来说：
“算了，不要这10个人了。”
然后补了一句：
“把销售部所有人的资料给我。”
这时候怎么办？我们还能用 XLOOKUP 吗？
当然可以想办法。
因为刚才的问题是：
我知道要找谁。
张伟、陈静、刘洋……
现在的问题变成：
我不知道具体是谁。
我只知道一个条件：
销售部
这时候就应该换工具。
05｜FILTER该上场了
输入：
=FILTER(A2:E3001,C2:C3001="销售部")
告诉Excel：
从A2:E3001这张员工表里，
把：
C列等于“销售部”
的所有记录筛出来。
Enter。
如果销售部有183个人：
183个人全部自动出来。
不是第一条，不是最后一条。
而是：
所有符合条件的记录。
06｜这就是XLOOKUP和FILTER一个很重要的区别
很多人学习Excel时容易陷入一个习惯：
学会一个函数，就什么问题都想用它解决。
比如学会XLOOKUP以后：
什么都XLOOKUP。
其实没有必要。你可以这样理解。
情况一
老板说：
“帮我查一下张伟。”
你知道具体找谁。用：
=XLOOKUP(G2,B2:B3001,A2:E3001)
情况二
老板给你：
10个人的名单。
你仍然知道具体找谁，只不过一次找很多人。 用：
=XLOOKUP(G2:G11,B2:B3001,A2:E3001)
情况三
老板说：
“把销售部的人全部找出来。”
你不知道具体姓名。只知道条件。用：
=FILTER(A2:E3001,C2:C3001="销售部")
所以关键不是：
XLOOKUP厉害还是FILTER厉害？
而是：
你现在到底在解决什么问题？
07｜再加一个条件呢？
老板继续说：
“销售部太多人了，只要上海的。”
（既要又要也要型老板登场）
现在有两个条件：
部门 = 销售部城市 = 上海
FILTER同样可以做。
=FILTER(A2:E3001,
(C2:C3001="销售部")*
(D2:D3001="上海")
)
这里的：*
你可以简单理解成：
而且
所以：销售部 * 上海
就是：
既属于销售部，而且在上海。
结果：Excel返回同时满足两个条件的人。
08｜甚至可以把条件放进单元格
如果每次都在公式里写："销售部"
其实还不够方便。我们可以在单元格输入：
G2 = 销售部H2 = 上海
然后公式改成：
=FILTER(
A2:E3001,
(C2:C3001=G2)*
(D2:D3001=H2)
)
以后想看：
财务部 + 苏州
只需要修改：
G2 → 财务部H2 → 苏州
结果马上变化。
公式：
一个字都不用改。
这时候它已经有一点像：
一个迷你查询工具。
09｜今天真正想讲的不是两个函数
如果只记公式，这篇文章很快就忘了。
我更希望你记住下面这个判断方法：
知道“找谁” → XLOOKUP
知道“一批人是谁” → XLOOKUP + 动态数组
不知道具体是谁，只知道“条件” → FILTER
下次老板再丢给你3000行Excel时，先问自己一句：
我到底是在“找一个东西”，还是“筛一批东西”？
问题判断对了，函数往往就已经选对一半了。
10｜最后再留一个真正工作中很容易踩的坑
假设名单里有：
张伟
但是员工表里有两个张伟。
那么：
=XLOOKUP(...)
会怎么样？它通常只会返回：
第一个找到的张伟。
这时候如果你真正想要的是：
把两个张伟都找出来
XLOOKUP就不再是最合适的选择。
FILTER反而更合适：
=FILTER(A2:E3001,B2:B3001=G2)
两个张伟：
两条全部返回。
这正好也呼应我们之前讨论过的XLOOKUP局限：
不是函数越高级越好，而是工具要选对。
大家可以免费下载这个文件作练习哦
通过网盘分享的文件：3000行找10个人_XLOOKUP_FILTER练习模板.xlsx
链接: https://pan.baidu.com/s/1PKBhdSSeNehy5NcHXpR1tg?pwd=ux19 提取码: ux19