ARTICLE · 1211507
Excel查找函数全解
上周帮同事看表。她要按姓名查工号，VLOOKUP 写完，回车，#N/A。
她排查了半小时：引号是英文的，区域没锁错，格式都是文本。最后转过头问我："是不是 VLOOKUP 坏了？"
没坏。方向反了。VLOOKUP 只认查找列左边的东西，只往右看。姓名在 B 列，工号在 A 列，它压根看不见。
这种事我见得太多了。不是公式背不下来，是不知道自己手里这个函数能干到哪一步。这篇把六个查找函数放一起讲，用同一张表。谁适合干什么，一次说清楚。
新建个工作簿，第一张 Sheet 命名「业绩表」，从 A1 开始照这个填：
数据区域是 A1:G7。工号存成文本（A001 这种带字母的，Excel 自动就按文本处理），后面的月份是纯数字。
下面所有例子都基于这张表。建议你真建一份，光看记不住——尤其是第三参数从第几列开始数这件事，不动手永远会数错。
PART 01
VLOOKUP：最熟的那个，也最容易用错
一句话：拿着一个值，去某张表的第一列找，找到了，返回同一行第 N 列的内容。
=VLOOKUP(找谁, 在哪找, 返回第几列, 精确还是模糊)
四个参数拆开看：
第一个参数是找谁。要查的值。可以写 "A003"，也可以引用单元格。第二个是在哪找。数据区域。硬规矩：查找值必须在区域第一列，否则永远查不到。 第三个是返回第几列。从区域的第一列数。注意查找列自己算第 1 列，不是第 0 列。 第四个是精确还是模糊。写 0或FALSE是精确匹配，写1或TRUE是近似匹配。省略不写等于 1。
第四参数省略就默认近似匹配，它会找个"差不多"的值塞给你，还不报错。所以养成习惯：第四位永远手写 0，别偷懒。
按工号查部门：
=VLOOKUP(”A003”,$A$1:$G$7,3,0) → 销售一部
第三参数为什么是 3？A 列工号算 1，B 列姓名算 2，C 列部门算 3。就这么简单。每年栽在这上面的人，我数不清。
再查刘敏三月的业绩：
=VLOOKUP(”刘敏”,$A$1:$G$7,7,0) → 96000
那反过来，按姓名查工号呢？
=VLOOKUP(”刘敏”,$A$1:$G$7,-1,0) → #VALUE!
列号不能是负数。这就是 VLOOKUP 的边界：它只会从查找列往右数，一步都不能往左。
三个高频报错，记下来能省一晚上：
#N/A没找到。九成是两边格式不一样——一边是文本"A001"，一边是数字；或者查找值后面拖了个空格。用=LEN(A2) 和 =LEN(查找值)
PART 02
HLOOKUP：横过来的 VLOOKUP
VLOOKUP 是竖着找，HLOOKUP 是横着找。语法只动一个字：
=HLOOKUP(找谁, 在哪找, 返回第几行, 精确还是模糊)
查找值必须在区域的第一行，返回第几行也从区域首行开始数。
什么时候用？表是横着排的时候。比如这张月度目标表：
=HLOOKUP(”2月”,$A$1:$D$4,2,0) → 85000
说实话，我一年用不到它几次。真实业务里的数据基本都是"一行一条记录"的竖表，横着排的多数是给人看的汇总表。真碰上了，我更愿意先选择性粘贴-转置一下，再用 VLOOKUP 或 XLOOKUP，思路还统一。
知道有这么个函数，面试题里不被问住就行。别为它多花时间。
PART 03
LOOKUP：能反向查，但我不敢用它
核心一句话：原生 LOOKUP 二分查找依赖查找区域升序；想要不受排序限制、稳定反向查找，就用
LOOKUP(1,0/(条件区域),结果区域)这个经典套路，原理完全不一样。
① 原始二分查找写法（依赖升序，不推荐乱序表）
=LOOKUP("刘敏",$B$2:$B$7,$A$2:$A$7)✅ 原理：二分查找，要求查找列 B（姓名）必须升序排序
B 列升序 → 结果稳定正确 B 列乱序 → 二分算法瞎匹配，不报错，随机出错误值，大坑！
适用场景：数据已经预先升序，追求公式简短。乱序表绝对不要用这个写法。
② 万能套路：LOOKUP(1,0/(查找区域=查找值),结果区域)
改造你的例子，稳定反向查找，不需要 B 列升序！
=LOOKUP(1,0/($B$2:$B$7="刘敏"),$A$2:$A$7)拆解原理（重点）
$B$2:$B$7="刘敏"：逐行判断，得到 TRUE/FALSE数组0/(布尔数组)：TRUE → 0/1=0；FALSE →0/0=#DIV/0!数组结果示例：{#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!}LOOKUP(1, 上面这个数组, $A$2:$A$7)LOOKUP 规则：查找小于等于查找值（1）的最后一个有效数据数组里只有 1 个 0，其余都是错误值，LOOKUP 自动忽略错误值，找到这个 0 对应的 A 列工号。
👉 关键点：这个写法不再走二分查找逻辑！因为数组被构造成：一堆错误值 + 0。LOOKUP 会跳过错误值匹配，不受原始数据排序影响，实现稳定查找。 ⚠️ 限制：只能返回最后一个匹配到的值；如果 B 列有多个 “刘敏”，会取最后一行，不是第一个。
PART 04
INDEX + MATCH：老版本里的万能钥匙
这是两个独立函数，配起来用。
MATCH 只干一件事：告诉你某个值在一行或一列里排第几个。
=MATCH(”刘敏”,$B$2:$B$7,0) → 4
意思是：在 B2:B7 里，刘敏排第 4 个。第三个参数同样写 0，表示精确匹配。
INDEX 也只干一件事：按"第几行"从区域里把值取出来。
=INDEX($C$2:$C$7,4) → 销售三部
合起来：
=INDEX($C$2:$C$7,MATCH(”刘敏”,$B$2:$B$7,0)) → 销售三部
读法要倒过来：先让 MATCH 量出"刘敏在第 4 位"，再让 INDEX 去 C2:C7 里拿第 4 个。
好处有三样。一是不挑方向，查找列在哪都行，结果列在左在右都行。二是不怕插列删列：VLOOKUP 第三参数是死数字，中间插一列，3 就变成 4，整表错位；INDEX+MATCH 引用的是区域，插列自动跟着走。三是什么版本都能用，2010 也行。
坏处就一个：比 VLOOKUP 长，第一次写容易把两个区域写反。
记个口诀：MATCH 找位置，INDEX 拿内容，MATCH 永远放里面。
PART 05
XLOOKUP：版本允许就直接用它
=XLOOKUP(找谁, 在哪一列找, 返回哪一列, [找不到显示什么], [匹配方式], [搜索方向])
前三个必填，后面三个可以不管。
同样是按姓名查工号：
=XLOOKUP(”刘敏”,$B$2:$B$7,$A$2:$A$7) → A004
三个参数，正向反向都无所谓，不用数第几列，也不用记那个该死的 0。XLOOKUP 默认就是精确匹配。
找不到的时候默认返回 #N/A，加第四个参数可以自定义：
=XLOOKUP(”孙悦”,$B$2:$B$7,$A$2:$A$7,”查无此人”) → 查无此人
同样是这件事，VLOOKUP 想做得先调列顺序，或者套一层 IFERROR。
还有个用法知道的人不多：返回区域可以框好几列。
=XLOOKUP(”刘敏”,$B$2:$B$7,$C$2:$G$7)
一次拿回部门、城市、1月、2月、3月 五个值，自动往右铺开。前提是右边五个格子得是空的，不然报 #SPILL!（溢出区被占）。
版本这事得说清楚：XLOOKUP 只在 Office 365、Excel 2021 及以后，和较新版本的 WPS 里有。Office 2019、2016 里没这个函数，你写完发到同事电脑上就是一个 #NAME?。
给别人的表做模板之前，先问一句"你用的什么版本"。这不是客套，是省事。
PART 06
FILTER：它不查一个，它捞一批
前面五个都是"给我一个值"。FILTER 不一样，它给一片。
=FILTER(要返回的数据, 筛选条件, [没结果时显示什么])
比如把销售一部 1 月超过 8 万的人捞出来，只要姓名：
=FILTER($B$2:$B$7,($C$2:$C$7=”销售一部”)*($E$2:$E$7>80000),”没有符合条件的”)→ 张伟
条件写法有个规律：多个条件同时满足，用 * 连；满足任意一个，用 + 连。
乘号是"并且"，加号是"或者"。看着别扭，记住就不会错。（原理是 TRUE 当 1、FALSE 当 0，1×1=1 才算成立，不用深究。）
FILTER 和 XLOOKUP 最容易搞混。按人名查他的业绩，是 XLOOKUP，返回一条。把所有销售一部的记录拉出来，是 FILTER，返回一堆。
一个找一条，一个捞一批。想清楚这个，就不会选错。
FILTER 同样挑版本，Excel 2021/365 和较新 WPS 才有。老版本用「数据 - 筛选」或数据透视表也能干同样的事，只是不能自动更新。
到底该用哪个
还有，所有查找函数出问题，先查格式再查公式。数字和文本长得一模一样，Excel 分得清清楚楚。拿 =ISTEXT(A2) 和 =ISNUMBER(A2) 各测一下，比盯着公式看半小时有用。
