夜雨聆风学习资料网

ARTICLE · 1382668

VLOOKUP 完整用法,一篇讲透【习题可下载】

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|办公技能|职场干货|表格

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

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

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

相关学习资料