ARTICLE · 1382668
VLOOKUP 完整用法,一篇讲透【习题可下载】
VLOOKUP 完整用法,一篇讲透
从「查得到」到「不出错」
📌 函数编号 41 | 查找匹配类 | 适用 Excel 2007 及以上
(XLOOKUP / FILTER 需 365 或 2021)
月底对账,左手一张回款明细,右手一张客户档案,几千行里要把客户名称一个个对上。手动找,眼睛花;用 VLOOKUP,一秒出结果——但也是这个函数,报错率最高。
多数人只会最基础那一种:0 精确匹配。第 4 参数写 1 做区间评级、配合其他函数一次返回多列,几乎没人讲透。这篇从语法讲到 3 个真实场景、3 个报错,再给一套财务场景用法,照着做就行。建议先收藏 ⭐
────────────────────
🟢 一、先搞懂它到底在干什么
一句话:拿一个值,去一张表里按行找,把找到那一行的第 N 列带回来。
▸ 公式长这样
=VLOOKUP(找什么, 去哪找, 第几列, 怎么找)
▸ 四个参数,逐个拆开看
① 查找值 · 要找的值,如工号、客户编号
⚠️ 文本和数值格式不一致,明明看着一样就是查不到
② 查找区域 · 包含「查找列 + 结果列」的区域
⚠️ 查找列必须在区域最左列,否则必然 #N/A
③ 返回列序号 · 结果在区域里第几列,从左往右数
⚠️ 超过区域总列数 → #REF!
④ 匹配方式 · 0 精确 / 1 近似
⚠️ 漏写或误写成 1,结果悄悄错、还不报错
✅ 口诀:0 找一模一样,1 找差不多
日常 95% 的场景写 0;只有区间评级、税率档位、提成阶梯这类「落在一个区间里」的需求才用 1。
📌 硬规矩:用 1 时,查找列必须升序排列,否则结果不可预期。
【配图 1|四参数一张图看懂】

────────────────────
🟢 二、3 个单函数场景,直接抄
▸ 场景 1|按工号查姓名(最常用)
=VLOOKUP(F2, $A$2:$B$20, 2, 0)
F2 是工号,$A$2:$B$20 是员工表,2 表示返回第 2 列姓名,0 精确匹配。区域加 $ 绝对引用,往下拖时不会跑偏——这一步偷懒,第二行开始就全错。
核工资、对台账、查客户信息,本质都是这一句。
▸ 场景 2|按姓名查多列,不用手动改列号
要一次带出部门、岗位、基本工资三列,右拖公式时列序号得自动 2、3、4 递增。用 COLUMN 顶替写死的序号:
=VLOOKUP($G2, $A$2:$D$20, COLUMN(B1), 0)
COLUMN(B1) 返回 2;公式往右拖一格变成 COLUMN(C1) 返回 3,再拖返回 4。查找值 $G2 锁列不锁行,保证右拖时始终按同一工号找。工资条、批量带出档案,这一招省掉大量重复劳动。
▸ 场景 3|工资条自动评级 / 税率档位(第 4 参数写 1)
先建一张升序的评级表:0→待提升、60→及格、70→中等、80→良好、90→优秀。然后:
=VLOOKUP(B2, $J$2:$K$6, 2, 1)
B2=85,函数会取「≤85 的最大下限」80,返回「良好」。不是四舍五入,是往下找档位。绩效评级、阶梯提成、个税速算、账龄分档,全是这个逻辑。
────────────────────
🟢 三、和其他函数配合,才算真会用
▸ IFERROR 兜底,别让 #N/A 满屏
=IFERROR(VLOOKUP(F2, $A$2:$D$20, 2, 0), "未找到")
查不到就显示「未找到」,报表干净,也方便后面筛选异常。只想屏蔽 #N/A、保留其他报错,用 IFNA 更精准。
▸ MATCH 动态列号,表头变了也不怕
COLUMN 靠位置,插入一列就可能错。更稳的是按表头名字找列号:
=VLOOKUP($F2, $A$2:$E$20, MATCH(G$1, $A$1:$E$1, 0), 0)
G1 写「基本工资」,MATCH 返回它在表头里的位置,VLOOKUP 用这个位置取数。表头顺序调整、中间插列,公式都不用改。
▸ 查找列不在最左?三条路
▪ 把查找列挪到最左(最省事)
▪ 用 INDEX + MATCH 组合,任意方向都能查
▪ 365 / 2021 直接换 XLOOKUP,天生支持向左查、返回多列
=XLOOKUP(F2, $B$2:$B$20, $A$2:$A$20, "未找到")
老版本要反向查找,也可用 IF({1,0}, ...) 重排区域,但属于数组公式,输入后需按 Ctrl + Shift + Enter 三键,日常不推荐。
▸ 一个客户多笔回款?VLOOKUP 不行
⚠️ 这是最常被误用的地方:VLOOKUP 只返回第一个匹配,剩下的行会被漏掉。多行求和要换 SUMPRODUCT + SUMIF 或新版 SUM(XLOOKUP(...)),别硬套。
────────────────────
🟢 四、3 个报错,对照着修
⚠️ #N/A 找不到 —— 按这个顺序查:
1. 格式不一致:一边是文本型数字、一边是数值。用 TRIM() 去空格、VALUE() / 分列统一格式,或用 &"" 把两边都转成文本
2. 查找列不在区域最左列,挪到最左或换 XLOOKUP
3. 确实没有这个值,用 IFERROR 包一层兜底
📌 顺带一提,精确匹配也支持通配符:* 任意多个字符、? 一个字符,做模糊查找好用;但要找的字面里本身含 * 或 ?,前面加 ~ 转义。
⚠️ #REF! 列数越界 —— 列序号大于区域总列数。比如只选了 A:C 三列,却要返回第 4 列。拉宽区域,或把序号改小。
⚠️ 结果不对、但一个错都不报 —— 九成是第 4 参数漏写或误写成 1。拿不准就写 0;只有区间评级才用 1,且确认查找列已升序。
【配图 2|3 个报错速查】

────────────────────
🟢 五、财务人最常用的一组场景
▸ 两表对账|按单号 / 客户编号 VLOOKUP 取金额,再相减看差异;差异非 0 的用条件格式标出
▸ 工资条制作|COLUMN 右拖带出多列,一行一人,批量生成
▸ 绩效 / 提成评级|第 4 参数 1 + 升序档位表,自动落档
▸ 费用科目匹配|按部门 / 项目编码带出科目名称,配合 IFERROR 标记未匹配项
▸ 账龄分档|用天数区间表 + 近似匹配,自动归到 0-30 / 31-60 / 61-90 天
✅ 共同点:查找值那一列的格式必须先统一。财务数据从系统导出常带不可见空格和文本型数字,先清洗再查找,能省掉八成的 #N/A。
────────────────────
🟢 六、一张表速查
① #N/A → 格式不一致 / 查找列非首列 / 确实没有
→ TRIM+VALUE 统一格式、挪列、IFERROR 兜底
② #REF! → 列序号超区域列数
→ 拉宽区域或改小序号
③ 结果错但不报错 → 第 4 参数漏写或写 1
→ 一律写 0,除非做区间评级
④ 往下拖全错 → 区域没锁 $
→ $A$2:$D$20 绝对引用
⑤ 只返回第一条 → VLOOKUP 天生只取首个匹配
→ 多行求和改用 SUMPRODUCT+SUMIF
✅ 避坑清单:查找列放最左 → 区域加 $ → 第 4 参数写 0 → 数据格式统一 → 近似匹配确认升序 → 插列后检查列序号
【配图 3|全篇速查表】

────────────────────
🟢 七、练一练
看懂不等于会用。配套做了一张练习模板,3 道题从精确查找、报错兜底到区间评级,填完公式自动判分,做完再看答案:
▪ 第 1 题 按工号查姓名(入门)
▪ 第 2 题 IFERROR + VLOOKUP 处理查无此人(进阶)
▪ 第 3 题 绩效区间评级,第 4 参数写 1(实战)
你踩过哪个坑?文本匹配不上、多列数错列号,还是被 #N/A 支配?评论区说说,票数最高的下期单独讲 👇
关注我,每天掌握一个 Excel 函数。
🏷️ Excel|Excel技巧|VLOOKUP|办公技能|职场干货|表格















































































