VLOOKUP 与查找匹配

[TOC] [TOC]

两张表之间搬数据，是 Excel 里最高频也最容易翻车的操作。成绩表里只有学号，姓名在另一张花名册里；销售表里只有工号，提成比例在另一张参数表里。VLOOKUP 就是专门解决这类问题的函数：按一个共同字段，把另一张表的内容搬过来。

四个参数一个一个填

VLOOKUP 的句式是 =VLOOKUP(找谁,在哪找,取第几列,怎么找)。举个例子，A 列是学号，花名册在 Sheet2，想按学号取姓名：

=VLOOKUP(A2,Sheet2!$A$1:$D$100,2,0)

四个参数一个一个翻译：

参数位置 填什么 上例中的写法 作用
第一参数 要匹配的值 A2 按这一格的值去找
第二参数 查找区域 Sheet2!$A$1:$D$100 只能在这片区域里找
第三参数 取第几列 2 区域第一列算 1，姓名在区域第 2 列
第四参数 匹配方式 0 0 表示精确匹配，1 表示近似匹配

前三个参数决定找什么、上哪找、取哪个值，第四个参数只有两个选项：0（或 FALSE）要求完全相等，1（或 TRUE）允许找最接近的。日常搬数据百分之九十九用 0，这也是新人最容易写错的地方：第四参数漏写或写成 1，返回的结果驴唇不对马嘴，还以为自己表串了。

两条铁律

第一条铁律：查找值必须在区域的第一列。第二参数圈出来的那片区域，最左边那一列必须是装着 A2 那种值的一列。如果姓名在学号左边，VLOOKUP 就废了，得先把那两列的顺序调一下。

第二条铁律：往下拖公式时，查找区域要锁死。上例里区域写的是 $A$1:$D$100，美元符号把行列都固定住。不锁会怎样？拖到第三行时区域自动滑成 A2:D101，花名册第一行的数据就漏出去了，查到最后一个人永远查不到。按 F4 键可以一键加上美元符号，省得手敲。

精确匹配与近似匹配

精确匹配好理解：找王建国的成绩，表里没有王建国就返回 #N/A。近似匹配则像翻字典找最接近的词：查找值不必精确存在，Excel 给你一个不超过查找值的最大匹配。

近似匹配的典型用法是查档：分数对应等级、金额对应提成比例、工龄对应年假天数。前提是查找区域第一列必须按从小到大排好序，否则结果天差地别。这种用法后面篇章还会细讲，先记住第四参数写 1 的时候必须先排序。

查不到怎么办：#N/A 处理

VLOOKUP 找不到就会显示 #N/A，难看且会污染下游计算。成熟的写法是外包一层 IFERROR：

=IFERROR(VLOOKUP(A2,Sheet2!$A$1:$D$100,2,0),“查无此人”)

这样找不到的记录显示查无此人四个字，找到的正常显示姓名。交报表之前务必把 #N/A 都消化掉，哪怕显示成空白（两个中文引号里什么都不写即可）也比错误码专业。

真实案例：做工资条

每月做工资条最折磨：基本工资、绩效、社保三项分别在三张表里，共同字段是工号。你的操作：在总表第一列放工号，然后 B 列写 =VLOOKUP($A2,工资表!$A:$F,3,0) 取绩效、C 列改第三参数取社保。三个 VLOOKUP 各拖一下，全公司工资条半小时合成。

注意 $A2 这种写法：列锁行不锁，公式往右拖时工号引用不变，往下拖时行号随行变化。混合引用是 VLOOKUP 应用的基本功。

报错 原因 排查方向
#N/A 查找值不存在 两张表的工号是否一致，是否有空格或不可见字符
#REF! 列序号超界 第三参数大于区域列数
#VALUE! 参数类型错 第四参数写成了文字
结果错位 第四参数写成 1 改回 0，或区域第一列不是查找列

最隐蔽的坑是数据看着一样其实不一样：工号前多了个空格、一张表是文本格式一张是数字格式、全角半角符号混用。用 LEN 函数对比两格字符长度，或 =A2=B2 直接判断，差异立刻现形。

INDEX 加 MATCH：更自由的组合

VLOOKUP 只能从左往右找，想从右往左就得 INDEX 配 MATCH。写法是 =INDEX(要取的那一列,MATCH(找谁,在哪一列,0))。MATCH 负责在第几行，INDEX 负责把那行的值取出来。看起来复杂，胜在不受列顺序限制。

较新的 Excel 还有 XLOOKUP，一个函数搞定左右查找、默认精确匹配、找不到还能自定义提示，=XLOOKUP(A2,Sheet2!$A$1:$A$100,Sheet2!$C$1:$C$100,“无”)。新版本用户优先学它，老旧文件兼容场景再回退 VLOOKUP。

VLOOKUP 的四条军规

  • 第四参数永远想清楚再写：精确匹配写 0，只有查档分级才用 1，且查找列必须先升序排好。
  • 查找区域用绝对引用锁死：按 F4 加美元符号，否则拖一下公式漏一片数据。
  • 查找值与被查值格式一致：文本数字混用时用分列或乘 1 的方式统一，否则永远查不到。
  • 匹配的字段先清洗：批量去除前后空格用查找替换或 TRIM 函数，眼睛看不见的空格是 #N/A 的头号元凶。

做匹配前的准备工作

拿到两张要匹配的表，别急着写公式，先做三件事：一是两张表都按共同字段排个序，肉眼扫一遍有没有明显对不上的；二是把两列工号或学号的格式统一（选中列，点数据选项卡的分列直接完成可把文本转数字）；三是确认查找值没有重复，重复的话 VLOOKUP 只返回第一个匹配到的结果。匹配字段的唯一性是数据质量的地基，磨刀不误砍柴工。

一次匹配多列的小技巧

要取不只一列数据时，第三参数可以做成自动的：=VLOOKUP($A2,Sheet2!$A:$F,COLUMN(B1),0)。COLUMN(B1) 返回 2，公式往右拖到下一列自动变成 3、4，一个公式横着拖竖着拉，就能覆盖整片结果区。这个小改动把重复劳动再砍掉一半，做过工资条的人都知道有多省事。

写熟练之后，工资条、成本归集、库存核对这类活，思路都一样：找共同字段、锁区域、拖公式、兜错误。VLOOKUP 会被你用成办公桌上的螺丝刀。

延伸阅读