# VLOOKUP 查得准的四件事：TRUE 还是 FALSE、区域为什么要锁死、列序号从哪一列数、#N/A 怎么查

精确匹配与近似匹配分别解决什么问题、col_index_num 为什么从区域最左列数起、table_array 不锁死会怎样、报 #N/A 时按什么顺序排查

> Excel 函数基础 · Excel 公式与引用 · 约 8 分钟 · 10 月 05 日

## 本篇要点

1. VLOOKUP 的四个参数依次是：要找的值、去哪段区域找、要返回区域里的第几列、精确还是近似匹配。
2. col_index_num 从 table_array 的最左列数起记为 1，与工作表的列字母无关；写小了会悄悄取回隔壁列的内容，写大了才报 #REF!。
3. FALSE 是精确匹配，表不用排序，找一条具体记录用它是标准写法；TRUE 是近似匹配，回答的是“数值落在哪一档”，要求第一列升序，并且第一列写的是各区间的下限。
4. 第四个参数可以省略，省略时 Excel 按 TRUE 处理，这是“公式没错结果却错”的常见来源。
5. 要用 TRUE 的场合，题面通常给出一张第一列递升的分界表；拿不准时用 FALSE。查找值小于第一列最小值时，TRUE 会返回 #N/A。
6. table_array 必须锁成全绝对引用（如 `$A$2:$C$50`），否则公式往下拖一行，参照表整体下移一格，第一行记录被漏掉；查找值则列锁行不锁，写成 `$A2`。
7. col_index_num 是写死的数字，往右拖不会自动加一，所以取多个字段时更适合一列一列往下拖。
8. #N/A 表示没找到，排查顺序是先核对值是否存在于源表（可用 COUNTIF 计数）、再查第四个参数、再查文本与数字类型不匹配和多余空格、最后查区域起止与列序号、以及是否忘了锁区域。
9. #REF! 说明列序号超出区域列数或引用被删，与 #N/A 是两类不同的错误。
10. IFERROR 只在确认“源表里确实没有”之后使用，因为它会一并掩盖自己写错造成的错误。
11. VLOOKUP 只能从左往右查，返回值必须在查找列右侧；第一列有重复值时精确匹配取从上往下第一个。

---

前面几篇 Excel 文章讲的是把很多行压缩成一个数：`SUMIF`、`COUNTIF` 那一类，问的都是“整张表里符合条件的加起来一共是多少”。`VLOOKUP` 处理的是相反的需求：**两张表本来对不上，要按一个共同的关键词，把另一张表里的信息搬到这一行来。**

考卷上常见的样子是这样的。一张成绩表只有学号和分数；另一张学生信息表里学号、姓名、班级三列齐全。现在要求你在成绩表里补出一列姓名、一列班级。两张表之间唯一的桥梁就是学号——`VLOOKUP` 干的就是“拿着这一行的学号，去另一张表里找到那一行，把对应的内容取回来”。

它的写法是：

```
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
```

## 四个参数各管一件事

- **lookup_value**：要找的东西，通常是一个单元格引用，比如这一行的学号 `A2`。
- **table_array**：去哪一段区域里找。这里有两个硬条件：要找的值必须在这段区域的**第一列**；要取回来的值也必须在这段区域**里面**。
- **col_index_num**：要取的那一列，是从区域**最左列**数起的第几列。
- **range_lookup**：精确匹配还是近似匹配，下面单独讲。

一个完整的例子，公式写在成绩表的 C2 里，用来取姓名：

```
=VLOOKUP(A2,学生信息表!$A$2:$C$50,2,FALSE)
```

跨工作表引用要写“工作表名 + 感叹号”再接区域；工作表名字里带空格时，`学生信息 表` 这样的名字要用单引号包起来：`'学生信息 表'!$A$2:$C$50`。

## 列序号数的是区域里的第几列，不是工作表的列字母

这是第一个真正的坎。区域 `$A$2:$C$50` 里，A 列是第 1 列、B 列是第 2 列、C 列是第 3 列，所以要取姓名写 `2`、取班级写 `3`。但如果区域写成 `$B$2:$E$100`，第 1 列就变成了 B，此时 C 列是第 2 列、D 列是第 3 列。

同一个 C 列，在两种区域写法里序号不同，只因为**计数起点跟着区域的最左列走**，跟工作表上那个字母没关系。数不清的时候有个土办法：临时在数据上方插一行，在区域覆盖的每一列上标上 1、2、3，数完再删掉。

列序号写错有两种后果，一好一坏：写大了，超出区域实际列数，返回 `#REF!`，报错反而好办；写小了，公式照常出结果，只是取回来的是隔壁那一列的内容——要班级却返回了姓名，格式都像对的，这种不报错的错误最难发现。核对时养成习惯：把 col_index_num 数一遍，别只看结果有没有报错。

## 第四个参数：TRUE 和 FALSE 是两种完全不同的问题

**FALSE（或 0）= 精确匹配。** 在第一列里找跟 lookup_value 一模一样的那一行，表不需要排序；找不到就返回 `#N/A`。适用于“一个具体对象对应一条记录”的查询：学号找姓名、姓名找部门、编号找单价。

**TRUE（或 1）= 近似匹配。** 它不找相等的值，而是找第一列里**“小于等于查找值的那些数当中最大的一个”**，然后返回那一行 [1]。换句话说，TRUE 回答的其实是“这个数落在哪一档”，而不是“这个数等于谁”。

所以用 TRUE 的表结构跟精确查询的表完全不一样：第一列写的不是每一个具体值，而是一档一档的**区间起点（下限）**：

| 分数下限 | 等级 |
| --- | --- |
| 0 | 差 |
| 60 | 及格 |
| 70 | 中等 |
| 85 | 优 |

用 `=VLOOKUP(B2,$F$2:$G$5,2,TRUE)` 查分数：

- B2 是 78 → 表里小于等于 78 的下限有 0、60、70，最大的是 70 → 返回“中等”。
- B2 是 60 → 正好命中的是 60 那一行 → 返回“及格”。
- B2 是 85 → 返回“优”。
- 如果查找值比第一列的最小值还小，会返回 `#N/A` [1]。上面这张表从 0 起，正常分数不会触发；但如果对照表第一档是从 60 开始的，一个 40 分就会查不出结果。

用 TRUE 还有两个必须同时满足和必须提防的地方：

- **第一列必须是升序。** 没排序时 Excel 不会报错，结果可能落在不对的那一行，看起来却像正常答案。所以只要用 TRUE，先检查表是不是从小到大排好了。
- **只有真的要做区间判断时才用 TRUE。** 查一个具体对象的记录却写了 TRUE，会返回一条“凑合”的记录：微软文档里的例子就是拿 TRUE 去查“Pear”，返回的价格其实是别的一行的值，因为 Pear 的字母序排在 Peach 前面 [4]。公式没错、结果有数，但内容是错的。

最隐蔽的一点是：**第四个参数是可选的，省略不写时，Excel 按 TRUE 处理** [1]。也就是说，忘写第四个参数的后果不是“少写了个东西”，而是悄悄换成了近似匹配。很多人“公式明明写得没问题，结果就是不对”，根子在这里。

判断办法可以简化成一句：问“是哪一个”（这个人、这个编号、这个产品），用 `FALSE`；问“落在哪一档”（多少分算什么等级、多少量按哪个比例），用 `TRUE`；拿不准就用 `FALSE`。区间判断这类题，题面通常会给你一张对照表，看到那张表第一列是一串递升的分界线，就是 TRUE 的场合。

## 查找区域为什么要锁死：不锁，往下拖一格就全错

上一篇讲公式引用时说过：公式往下拖一行，里面所有相对引用都会跟着加一行。`VLOOKUP` 的第二个参数就是最容易在这里出事的。

假如把区域写成 `学生信息表!A2:C50`，C2 里是 `A2:C50`，拖到 C3 就变成 `A3:C51`——整张参照表往下挪了一格：第 2 行那条记录被排除在外查不到了，最下面还多圈进来一行不属于这张表的数据。所以区域要写成 `学生信息表!$A$2:$C$50`，四个美元符号一个不少。

而查找值恰恰相反，它是**列锁、行不锁**：

```
C2：=VLOOKUP($A2,学生信息表!$A$2:$C$50,2,FALSE)    取姓名
D2：=VLOOKUP($A2,学生信息表!$A$2:$C$50,3,FALSE)    取班级
```

`$A2` 的列被锁住，是为了往右拖时查找值仍然来自 A 列；行不锁，是为了往下拖时换成下一个学号。两个公式一起选中往下拖，整列都能算对。

顺带提醒一个容易被忽略的地方：`col_index_num` 是个写死的数字，往右拖不会从 2 自动变成 3。所以取多个字段时，更稳的做法是**一列一列地往下拖**，而不是写一个公式往右拖全程。

## 报 #N/A 时按这个顺序查

`#N/A` 的含义是“没找到”，不是“算错了” [4]。既然它只说明查找失败，就可以按由省事到麻烦的顺序拆：

1. **先确认源表里到底有没有这个值。** 用上一篇的 `COUNTIF`：`=COUNTIF(学生信息表!$A$2:$A$50,A2)`。返回 0，说明源表里根本没有这个学号，那就不是公式的问题（可能是数据抄错，或者该查的是另一个字段）；返回 1 或更多，说明值确实在，是“对不上”，继续往下查。这一步一下就能把两类原因分开，是最省事的入口。
2. **看第四个参数。** 是不是漏写了？漏写等于 TRUE，在没排序的精确查询表上就会失败或返回错值。
3. **数字和文本不是同一种东西。** 源表第一列的学号如果存成了文本（左对齐、单元格左上角有绿色小三角），而查找值是数字（右对齐），Excel 就认为两者不同，找不到。这跟 `IF` 那篇里“数字被存成文本让整列判断都错”是同一个坑，微软也把它列为 `#N/A` 的首要原因之一 [2]。
4. **看不见的空格。** 从网页或其他系统复制过来的姓名经常带着前导或尾随空格，`"张三 "` 不等于 `"张三"`。可以用 `TRIM` 清理源表数据，或者把查找值包一层：`=VLOOKUP(TRIM(D2),A2:B7,2,FALSE)` [2]。这类问题肉眼看不出差别，是比较两个“看起来一样”的格子时最容易中招的一条。
5. **区域本身对不对。** 这段 `table_array` 是不是**从查找值所在的那一列开始**的，有没有宽到包含要返回的列，col_index_num 有没有数错。
6. **拖公式之前锁没锁区域。** 这条对应上一节，常常表现为“第一行对，往下就乱”。

顺便分一下两种最容易混的错误：`#N/A` 是找不到；`#REF!` 是列序号超出了区域的列数，或者引用的列被删掉了。

如果确认是源表里真的没有（题目也允许留空或填 0），再用 `=IFERROR(公式,0)` 或 `=IFERROR(公式,"")` 把 `#N/A` 换掉 [4]。要注意它会把**所有**错误都吞掉，包括你自己写错造成的错误——所以顺序是先查对结果，最后一步才套 `IFERROR`。

## 有一条限制现在就该记住

`VLOOKUP` 只能从左往右找：查找值必须在 `table_array` 的第一列，要取回来的值必须在它右边。题目要求取的内容在查找列的左边时，`VLOOKUP` 做不了，得调整表格结构，或者改用 `INDEX` + `MATCH` [5]——那是后面的事。

还有一条会影响结果对不对：第一列如果有重复的值，精确匹配返回的是**从上往下第一个**找到的那一条 [3]。所以拿学号、编号这类唯一值去查最稳；拿可能会重复的值去查，要先想清楚要的是哪一条。

## 术语表

- table_array：VLOOKUP 的第二个参数，指去哪一段区域里查找；查找值必须位于这段区域的第一列，要返回的内容也必须在这段区域之内。
- col_index_num：第三个参数，表示要返回的那一列在 table_array 中从左数排第几，最左列记为 1。
- range_lookup：第四个参数，决定精确匹配还是近似匹配；写 FALSE（0）表示找完全相同的值，写 TRUE（1）表示找不超过查找值的最大那个档位，省略不写按 TRUE 处理。
- 精确匹配：要求第一列里存在与查找值完全相同的项，找不到就返回 #N/A；表不需要排序。
- 近似匹配：不要求相等，而是找第一列中“小于等于查找值的最大值”所在的那一行；因此第一列必须升序，且写的是各区间下限。
- 区间下限表：一种用于近似匹配的对照表，第一列是每档的起点分数或起点数量，第二列是这一档对应的结果，例如 0 分对应差、60 分对应及格。
- 引用不匹配导致的 #N/A：查找值在源表中其实是存在的，但因为一边是数字、一边是文本，或带有多余空格，Excel 判定两者不同而查不到。
- IFERROR：把公式产生的任何错误替换成指定的值或空文本，常用于让查不到的行显示为空白或 0，但也会掩盖公式自身的错误。

## 来源

1. [Microsoft Support: VLOOKUP 函数 — 四个参数的定义、approximate 与 exact match 的说明，以及 TRUE 下需升序、查找值小于最小值返回 #N/A](https://support.microsoft.com/en-us/excel/functions/vlookup-function)
2. [Microsoft Support: 如何更正 VLOOKUP 中的 #N/A 错误 — 值类型不一致、多余空格与 TRIM 用法、区域与列序号检查](https://support.microsoft.com/en-us/office/how-to-correct-a-n-a-error-in-the-vlookup-function-e037d763-ffc3-4fae-a909-89c482d389b2)
3. [Microsoft Learn: WorksheetFunction.VLookup — col_index_num 计数方式、升序要求、重复匹配值取第一个、列序号越界报错](https://learn.microsoft.com/en-us/office/vba/api/Excel.WorksheetFunction.VLookup)
4. [Microsoft Support: 如何更正 #N/A 错误 — #N/A 的通用含义、用错 TRUE 会返回错误结果的示例、IFERROR 的写法](https://support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error)
5. [Microsoft Support: 使用 VLOOKUP、INDEX 或 MATCH 查找值 — VLOOKUP 只能从左往右查的限制，以及改用 INDEX + MATCH 的情形](https://support.microsoft.com/en-us/excel/look-up-values-with-vlookup-index-or-match)

---

原文：https://pangzhengboyin.com/articles/vlookup-exact-match-locking-and-na-troubleshooting-408f36e7

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具。说出想学什么，它会先了解你的基础，再把主题写成一篇篇 5–10 分钟能读完的文章；边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com