Excel 查找公式总是 #N/A?按这 5 处查

两张表对名单、对单价，是办公室里最常见的活儿：一张是花名册，一张是考勤表；一张是订单明细，一张是价目表。用查找公式 VLOOKUP 一拉，结果满屏 #N/A。

公式检查了三遍，括号、逗号都没错，就是不出数。

下面按我平时排查的顺序，把最常见的 5 处一个个讲清楚，每处都给一个可以照着抄的公式。

示例统一假设：当前表 A 列是要查的姓名或编号，Sheet2 的 A 列是同样的姓名或编号，B 列是要带回来的结果。

Excel 和 WPS 表格里写法基本一样，菜单位置可能略有差异，以你屏幕上显示的为准。数据情况千差万别，这 5 处能解决大部分 #N/A，但不保证百分之百。

先做个 10 秒小测试

排查之前，先挑一个你确定两边都有、却显示 #N/A 的值，比如当前表 A2 的「张三」，和 Sheet2 里 A5 的「张三」，在空白格输入：

=A2=Sheet2!A5

结果是 TRUE，说明内容一样，问题在公式写法上，直接看第四、第五处；结果是 FALSE，说明两个「张三」在 Excel 眼里根本不是同一个东西，重点看第一、第二处。

再用 =LEN(A2) 和 =LEN(Sheet2!A5) 看看字数。「张三」应该是 2，如果显示 3，基本就是多了个空格。

一、空格和看不见的字符

这是 #N/A 的头号元凶。从系统导出的数据、从微信或网页复制过来的名单，经常带着肉眼看不见的空格、换行符或特殊字符。

办法一：公式里顺手清掉

如果是当前表这边有空格，在公式里套一个 TRIM：

=VLOOKUP(TRIM(A2),Sheet2!$A:$B,2,0)

TRIM 会去掉前后多余的空格。如果还夹着换行符这类不可见字符，再加一层 CLEAN：

=VLOOKUP(TRIM(CLEAN(A2)),Sheet2!$A:$B,2,0)

办法二：用辅助列把源数据洗干净

如果是 Sheet2 那边有空格，在公式里改就麻烦了。更省事的做法是在旁边插一列，输入 =TRIM(CLEAN(A2)) 往下拉，再复制这一列，右键「选择性粘贴 → 数值」贴回原列。

办法三：查找替换

选中那一列，按 Ctrl + H，「查找内容」里敲一个空格，「替换为」什么都不填，点「全部替换」。

注意：查找替换会把中间的空格也删掉。中文姓名、编号没关系，英文名（比如 Tom Lee）就别这样用。

还有一种顽固空格叫「不间断空格」，常见于网页复制的内容，TRIM 去不掉。可以用：

=SUBSTITUTE(A2,CHAR(160),"")

二、数字存成了文本

工号、订单号、身份证后几位这类数据最容易踩这个坑。一边是真正的数字，另一边是「长得像数字的文本」，查找公式会认为两者不相等。

怎么认出来： 单元格左上角有个绿色小三角，或者数字默认靠左对齐（真正的数字一般靠右），基本就是文本型数字。

办法一：分列，直接点完成

选中那一列，点 数据 → 分列，弹出向导后什么都不用改，直接点「完成」，文本型数字就会变回真正的数字。WPS 里也是「数据 → 分列」。

也可以点一下绿色小三角旁边的感叹号，选「转换为数字」。

办法二：在公式里转换

如果要查的值是文本、Sheet2 里是数字，在前面加两个减号：

=VLOOKUP(--A2,Sheet2!$A:$B,2,0)

反过来，要查的值是数字、Sheet2 里是文本，就在后面连一个空字符串：

=VLOOKUP(A2&"",Sheet2!$A:$B,2,0)

小提示：以 0 开头的编号（比如 00123）一转成数字，前面的 0 就没了。这种情况建议两边都统一成文本，用 &"" 的写法更稳。

三、要查的列不在区域最左边

这一条很多人不知道：查找公式只会在选中区域的第一列里找，然后往右边拿结果。

比如 Sheet2 里 A 列是部门、B 列是姓名，你想按姓名查部门，写成 =VLOOKUP(A2,Sheet2!$A:$B,1,0) 是不行的。姓名在 B 列，不在第一列，它就找不到。

办法一：挪列

把姓名列剪切到最左边，或者区域从姓名那一列开始选。最简单，但会动原表，适合自己的表。

办法二：改用 XLOOKUP

XLOOKUP 不分左右，要查的列和要返回的列各自单独选：

=XLOOKUP(A2,Sheet2!$B:$B,Sheet2!$A:$A,"没有")

XLOOKUP 需要较新版本的 Excel（Microsoft 365、Excel 2021 及以后）或较新版本的 WPS。输入后显示 #NAME?，说明你的版本不支持，用下面的办法三。

办法三：INDEX + MATCH

老版本也能用，而且同样不分左右：

=INDEX(Sheet2!$A:$A,MATCH(A2,Sheet2!$B:$B,0))

意思是先用 MATCH 找到姓名在 B 列第几行，再用 INDEX 取 A 列同一行的部门。

四、区域没锁住，往下拉就偏了

第一行公式结果正常，往下一拉，前面一部分还对，后面越来越多 #N/A。这多半是查找区域跟着一起往下滑了。

比如第一行写的是 =VLOOKUP(A2,Sheet2!A2:B100,2,0)，拉到第 50 行就变成 Sheet2!A51:B149，前面 49 行数据全被漏掉了。

办法：选中区域后按 F4 加上 $

编辑公式时，用鼠标选中 Sheet2!A2:B100 这一段，按一下 F4，它会变成：

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

有了 $，往下拉区域就不会再动了。笔记本上 F4 不灵的，试试 Fn + F4。

更省心的写法是直接选整列：

=VLOOKUP(A2,Sheet2!$A:$B,2,0)

以后 Sheet2 再加数据也能查到，不用改公式。

顺带一提：第 3 个参数（返回第几列）写得超过了区域列数，显示的是 #REF!，不是 #N/A。区域选了 A:B 两列，这里最大只能写 2。

五、第 4 个参数漏写或写成了 TRUE

查找公式最后一个参数决定「怎么找」：

写 0 或 FALSE：精确匹配，必须一模一样才算找到。对名单、对单价、对编号，用的都是这个。

写 1、TRUE 或干脆不写：近似匹配，要求第一列按升序排好，一般用来查分数段、提成档位这类区间。

很多人图省事写成 =VLOOKUP(A2,Sheet2!$A:$B,2)，最后一个参数没写，默认就是近似匹配。结果要么一片 #N/A，要么更糟：返回一个看起来正常、其实是错的值，还不容易发现。

正确写法，记住最后那个 0：

=VLOOKUP(A2,Sheet2!$A:$B,2,0)

写成 FALSE 效果一样。养成习惯：只要不是查区间，最后一个参数永远写 0。

彩蛋：用 IFERROR 让表格好看一点

5 处都排查完了，剩下的 #N/A 通常是真的没有，比如新员工还没进花名册，或者某个商品价目表里压根没登记。

这时可以用 IFERROR 把 #N/A 显示成「没有」，表格看着更清爽，汇总、求和也不会被错误值卡住：

=IFERROR(VLOOKUP(A2,Sheet2!$A:$B,2,0),"没有")

想留空就把「没有」换成 ""。

建议先排查、再套 IFERROR。一开始就套上，前面那些空格、文本型数字的问题也会一起被藏成「没有」，你会以为真的查不到。

最后附一张排查清单，下次遇到 #N/A，从上往下过一遍：

• ●空格和隐藏字符：用 =LEN() 看字数，用 TRIM、CLEAN 或 Ctrl + H 清掉
• ●文本型数字：看有没有绿色小三角，用「数据 → 分列 → 完成」，或在公式里加 --、&""
• ●查找列在不在最左边：不在就挪列，或改用 XLOOKUP、INDEX+MATCH
• ●区域锁没锁：选中区域按 F4 加 $，或直接选整列 $A:$B
• ●第 4 个参数：精确匹配写 0 或 FALSE

查找公式本身不难，难的是两边数据「看着一样、其实不一样」。

关注我

你的办公好帮手