Excelの#N/Aエラー: VLOOKUPやXLOOKUPで見つからない

#N/Aは「利用できない」という意味で、VLOOKUP、XLOOKUP、MATCHなどの検索が探していた値を見つけられなかったことを表します。下の=VLOOKUP(E2,A2:B6,2,FALSE)は、Kiwiがリストにないので#N/Aを返します。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

実際のシートで最もよくある原因です。数式は1行目では動くのに、商品がリストにあるにもかかわらず、下のいくつかの行で#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の範囲より2行下です。数式を下にコピーしたときに範囲も一緒に動いたので、Pear(3行目)とApple(2行目)が範囲から外れました。F3とF5が動くのは、PlumとMilkがまだそれぞれの範囲の中にあるからにすぎません。F2をクリックして範囲を$A$2:$B$6に変えると、列全体がそれに合わせて変わり、すべての価格が表示されます。$記号は範囲を固定します。絶対参照を参照してください。

余分なスペースによる#N/A

末尾にスペースのある"Pear "と"Pear"は、Excelにとって別の値です。スペースは、手で入力したデータ、Webページからコピーしたデータ、ほかのシステムから書き出したデータに紛れ込み、セルの中では見えません。

数式リセット

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のスペースを削除すると、検索が動きます。恒久的に直す方法は3つあります：

• 列をきれいにする：作業列に=TRIM(A2)を入れて下にコピーし、それをコピーして元の列にホーム > 貼り付け > 値で貼り付けます。
• 入力する側にスペースがあるなら、検索値にTRIMをかけます：=VLOOKUP(TRIM(D2),A2:B4,2,FALSE)。
• 数式の中で検索列全体にTRIMをかけます(Excel 2021かMicrosoft 365)：=XLOOKUP(D2,TRIM(A2:A4),B2:B4)。

Webページから貼り付けた文字列には、TRIMでは取り除けないノーブレークスペースが含まれていることがあります。SUBSTITUTE(A2,CHAR(160)," ")で置き換える方法はTRIMのページで紹介しています。

値が最初の列にないときの#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より役に立つものを表示します。IFERRORではなくIFNAを使います：

数式リセット

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! 存在しないセルを参照しています。

どちらの数式も、2列の範囲の3列目を求めるという間違いをしています。IFNAは#REF!をそのまま通すので、バグが見えます。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)も動きますが、それはどの商品にも末尾のスペースがちょうど1つある間だけです。長く使える直し方は、TRIMで列をきれいにすることです。