Excel #N/A 错误：VLOOKUP、XLOOKUP 找不到值

#N/A 的意思是“不可用”：VLOOKUP、XLOOKUP 或 MATCH 等查找函数没有找到要找的值。下面的 =VLOOKUP(E2,A2:B6,2,FALSE) 返回 #N/A，因为 Kiwi 不在列表中。把 E2 改成 Pear，它就返回 1.5。

公式重置

F2fx

=VLOOKUP(E2,A2:B6,2,FALSE)

	A	B	C	D	E	F
1	Product	Price			Look for	Price
2	Apple	1.2			Kiwi	#N/A
3	Pear	1.5
4	Plum	0.8
5	Bread	2.4
6	Milk	1.1

#N/A 要查找的值不在查找范围内。

当这个值确实不存在时，#N/A 就是正确的答案，下文介绍的 IFNA 可以把它变成一条提示。真正需要修复的，是值明明存在、查找却仍然失败的情况。某些语言版本的 Excel 会用自己的名称显示这个错误，例如德语的 #NV、葡萄牙语和意大利语的 #N/D、俄语的 #Н/Д 和土耳其语的 #YOK；它们都是同一个错误。

向下填充查找公式后出现 #N/A

这是实际表格中最常见的原因：公式在第一行正常，下面的一些行却显示 #N/A，尽管这些商品都在列表中。

公式重置

F4fx

=VLOOKUP(E4,A4:B8,2,FALSE)

	A	B	C	D	E	F
1	Product	Price			Order	Price
2	Apple	1.2			Apple	1.2
3	Pear	1.5			Plum	0.8
4	Plum	0.8			Pear	#N/A
5	Bread	2.4			Milk	1.1
6	Milk	1.1			Apple	#N/A

#N/A 要查找的值不在查找范围内。

点击 F4：它的表格区域是 A4:B8，比 F2 的低了两行。向下填充公式时区域也跟着移动了，所以 Pear（第 3 行）和 Apple（第 2 行）落到了区域之外。F3 和 F5 之所以正常，只是因为 Plum 和 Milk 仍在它们的区域内。点击 F2，把区域改成 $A$2:$B$6：整列都会跟着更新，每个价格都出现了。$ 符号锁定了区域，见 绝对引用。

多余的空格导致 #N/A

对 Excel 来说，末尾带空格的 "Pear " 和 "Pear" 是两个不同的值。空格来自手工输入的数据、从网页复制的内容或从其他系统导出的数据，而且在单元格中是看不见的。

公式重置

E2fx

=VLOOKUP(D2,A2:B4,2,FALSE)

	A	B	C	D	E	F
1	Product	Price		Look for	Price	Length of A2
2	Pear	1.5		Pear	#N/A	5
3	Apple	1.2
4	Plum	0.8

#N/A 要查找的值不在查找范围内。

E2 返回 #N/A。F2 显示了原因：Pear 有 4 个字母，但 A2 有 5 个字符。删掉 A2 中的空格，查找就正常了。彻底解决的三种方法：

• 清理这一列：在辅助列中写 =TRIM(A2) 并向下填充，然后复制它，用 开始 > 粘贴 > 值 覆盖原数据。
• 如果空格在你输入的内容中，就对查找值使用 TRIM：=VLOOKUP(TRIM(D2),A2:B4,2,FALSE)。
• 在公式中对整个查找列使用 TRIM（Excel 2021 或 Microsoft 365）：=XLOOKUP(D2,TRIM(A2:A4),B2:B4)。

从网页粘贴的文本可能含有不间断空格，TRIM 删不掉它。TRIM 页面介绍了怎样用 SUBSTITUTE(A2,CHAR(160)," ") 替换它。

值不在第一列时出现 #N/A

VLOOKUP 只在区域的第一列中查找，并返回它右边的某一列。查找其他列中的值会返回 #N/A，即使这个值就在表格里。

公式重置

F2fx

=VLOOKUP(E2,A2:C6,1,FALSE)

	A	B	C	D	E	F	G
1	Product	Code	Price		Code	VLOOKUP	XLOOKUP
2	Apple	A-17	1.2		P-22	#N/A	Pear
3	Pear	P-22	1.5
4	Plum	P-31	0.8
5	Bread	B-05	2.4
6	Milk	M-40	1.1

#N/A 要查找的值不在查找范围内。

编码在 B 列，所以在 A2:C6 上使用 VLOOKUP 时，它是在商品名称中查找 P-22，结果当然找不到。它也无法返回位于编码左边的商品名称。XLOOKUP 分别接收查找列和返回列，因此能找到 Pear。在 Excel 2019 及更早版本中，=INDEX(A2:A6,MATCH(E2,B2:B6,0)) 可以实现同样的效果。

文本格式的数字导致 #N/A

以文本形式输入的订单号（'1001，或者从 CSV 导入的）永远不会与数字 1001 匹配，反过来也一样。两个单元格都显示 1001，所以这种情况很难发现。在 Excel 中：

A2:B6 holds order numbers stored as text, E2 holds the number 1001
=VLOOKUP(E2,A2:B6,2,FALSE)          #N/A
=VLOOKUP(E2&"",A2:B6,2,FALSE)       found: E2&"" turns the number into text

A2:B6 holds real numbers, E2 holds "1001" as text
=VLOOKUP(E2,A2:B6,2,FALSE)          #N/A
=VLOOKUP(--E2,A2:B6,2,FALSE)        found: -- turns the text into a number

=ISTEXT(A2) 可以告诉你哪一边是文本，单元格角上的绿色小三角也标示着以文本形式存储的数字。要转换一整列，见 文本转数字。

IFNA 还是 IFERROR：找不到时显示提示

当一个值确实可能不存在时，显示比 #N/A 更有用的内容。请用 IFNA，而不是 IFERROR：

公式重置

F2fx

=IFNA(VLOOKUP(E2,A2:B6,3,FALSE),"Not found")

	A	B	C	D	E	F	G
1	Product	Price			Look for	IFNA	IFERROR
2	Apple	1.2			Pear	#REF!	Not found
3	Pear	1.5
4	Plum	0.8
5	Bread	2.4
6	Milk	1.1

#REF! 公式引用了一个不存在的单元格。

两个公式都要求返回两列区域中的第 3 列，这是一个错误。IFNA 让 #REF! 显示出来，于是你能看到这个 bug。IFERROR 则把它掩盖了，对列表中明明存在的 Pear 也显示“Not found”。把两个 3 都改成 2：现在两者都显示 1.5；把 E2 设为 Kiwi 时，两者都显示“Not found”。XLOOKUP 内置了提示参数：=XLOOKUP(E2,A2:A6,B2:B6,"Not found")。两者区别的更多内容见 IFERROR。

修复被空格破坏的查找

公式重置

F2fx

	A	B	C	D	E	F
1	Product	Price			Look for	Price
2	Apple	1.2			Plum
3	Pear	1.5
4	Plum	0.8
5	Bread	2.4
6	Milk	1.1

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 列表中的每个商品导入时末尾都带了一个空格，所以 =VLOOKUP(E2,A2:B6,2,FALSE) 返回 #N/A。在 F2 中写一个公式，仍然能返回 E2 中商品的价格。

检查显示答案

=XLOOKUP(E2,TRIM(A2:A6),B2:B6) 在公式内部对列表使用 TRIM。=VLOOKUP(E2&" ",A2:B6,2,FALSE) 在这里也能用，但前提是每个商品末尾都恰好有一个空格；用 TRIM 清理这一列才是一劳永逸的办法。