Excel #N/Aエラーの原因と直し方|VLOOKUP・XLOOKUPで検索値があるのに出るとき

スポンサーリンク

VLOOKUPで単価を引こうとしたら、セルに #N/A と出てしまった。表を見ると、探している商品はちゃんと載っている。Excelでこんな場面に出くわすと、どこを直せばいいのか迷いますよね。

数式を書き直したり、範囲を広げたりしても、原因に当たらなければ #N/A は消えません。締め切りが迫っていると、IFERRORで隠したくなる気持ちもわかります。

でも、Excelの #N/A エラーの原因はかなり絞り込めます。空白・型違い・範囲のずれ・一致の指定・そもそも存在しない、の5つです。この順に切り分ければ、数式を当てずっぽうに書き換えずに済むはず。

Excelの#N/Aエラーとは|VLOOKUP・XLOOKUP・MATCH別の早見表

#N/A は、数式が探すように指示された値を見つけられなかったときのエラーです。Microsoftの公式ヘルプ「エラー値 #N/A を修正する方法」でも、最も多い原因は検索関数とされています。VLOOKUP・XLOOKUP・MATCHなどが、検索値を表の中に見つけられないケースのこと。

厄介なのは、目で見て「ある」値でも、Excelが「ない」と判定する場合があることです。たとえば、検索値のE2に「みかん」と入れ、表のA3にも「みかん」があるのに、F2のVLOOKUPが #N/A を返すことがあります。

原因は、E2の末尾に入った見えない半角スペース。見た目が同じでも、中身が1文字違えば検索は一致しません。

対応バージョン(XLOOKUPは365/2021以降)

対応バージョン: VLOOKUP・MATCHは、すべてのバージョンのExcelで使える関数。XLOOKUPはMicrosoft 365・Excel 2021以降・Excel for the webで使えます。Excel 2019以前ではXLOOKUP自体が使えず、#N/A ではなく #NAME? になります。

#NAME? が出ているなら、原因は別の場所にあります。ExcelのXLOOKUP・FILTERが使えない・#NAME?の原因と対処を見てください。この記事では、#N/A が出ているケースに絞って直し方を解説します。

関数別・どの指定で#N/Aになるか早見表

同じ「見つからない」でも、関数ごとに一致方法の既定値が違います。とくに注意したいのは、引数を省略したときの動き。

関数一致方法を省略したとき#N/Aになる主なケース#N/A以外になる取り違え
VLOOKUP第4引数の省略はTRUE(近似一致)完全一致(FALSE)で見つからない/検索値が範囲の1列目にない/近似一致で検索値が最小値より小さい列番号が範囲の列数を超えると #REF!、1未満だと #VALUE!。近似一致で並べ替えていないと誤った値が返ることも
XLOOKUP一致モードの省略は0(完全一致)見つからず、第4引数(見つからない場合)も指定していない検索範囲と戻り範囲のサイズが違うと #VALUE!。非対応の版では #NAME?
MATCH第3引数の省略は1(近似一致・昇順が前提)完全一致(0)で見つからない/昇順でない範囲を近似一致で探した昇順でない範囲では、#N/A ではなく誤った位置が返ることも

右端の列も見落とせません。#REF! や #VALUE! が出ているなら、この記事の切り分けとは別の場所を直すことになります。ほかのエラーはExcelエラー値12種類の原因と対処法一覧から探してください。

30秒でできる切り分けチェック(ISNUMBER / LEN / EXACT)

「表にあるはずなのに」と感じたら、数式を触る前に検索値のセルを調べましょう。空いているセルに、次の数式を入れてみてください。E2が検索値、A3が表の中で一致してほしいセルです。

=ISNUMBER(E2)
=ISNUMBER(A3)
=LEN(E2)
=LEN(A3)
=EXACT(E2,A3)

結果の読み方は次のとおり。

チェック結果の読み方疑う原因
ISNUMBER(E2) と ISNUMBER(A3)TRUEとFALSEに分かれたら、片方が数値で片方が文字列型違い
LEN(E2) と LEN(A3)文字数が違えば、見えない空白などが混じっている余分な空白
EXACT(E2,A3)FALSEなら、2つのセルの中身がどこか違う空白や表記の違い

冒頭の「みかん」の例で試すと、ISNUMBERはどちらもFALSEでそろい、LENは4と3に分かれ、EXACTはFALSEになります。

ISNUMBERがそろい、LENが同じで、EXACTもTRUEなら、値そのものは一致しています。それでも #N/A が出るなら、疑う先は数式の範囲と一致の指定です。

原因の候補は、確かめやすい順に次の5つ。上から順に見ていくと、無駄な書き換えが減ります。

  1. 余分な空白(LENが違う)
  2. 文字列と数値の型違い(ISNUMBERが分かれる)
  3. 範囲のずれ(検索列が左端にない・$ の固定忘れ・追加した行が範囲外)
  4. 一致の指定(VLOOKUPの第4引数・MATCHの第3引数)
  5. そもそも表に存在しない

検索値があるのに#N/Aになる原因

チェックでLENかISNUMBERがずれていたら、原因はデータ側にあります。数式を書き換える前に、検索値か表の値をそろえましょう。

余分な空白(前後・全角スペース)

冒頭の「みかん」の例が、まさにこのケース。E2の「みかん」の後ろに半角スペースが1つあり、LENが4を返しています。表のA3は3文字なので、Excelは別の文字列として扱うわけです。

空白は手入力の打ち間違いのほか、他のシステムから貼り付けたデータに紛れ込むこともあります。検索値の側なら、TRIMで余分な空白を取り除いてから検索しましょう。

=VLOOKUP(TRIM(E2),$A$2:$C$6,3,FALSE)

TRIMは前後の余分な半角スペースを取り除く関数です。E2が「みかん」として検索され、単価の80が返ります。

気をつけたいのは、全角スペースとノーブレークスペース。公式ヘルプでは、TRIMの対象は半角スペース(文字コード32)とされています。

Webページからコピーした文字には、ノーブレークスペース(CHAR(160))が混じることがあります。SUBSTITUTEで半角スペースに置き換えてからTRIMすると確実です。

=TRIM(SUBSTITUTE(E2,CHAR(160)," "))
=TRIM(SUBSTITUTE(E2," "," "))

1行目はノーブレークスペースを、2行目は全角スペースを半角に置き換えてから余分な空白を除く式です。余分な半角スペースがあるかどうかは、=LEN(E2)-LEN(TRIM(E2)) が1以上かで確かめられます。

文字列と数値の型違い

もう1つの「あるのに見つからない」原因が、型の違い。画面上は同じ1003でも、片方が数値で片方が文字列だと一致しません。公式ヘルプも、#N/A の原因として値の型の違いを挙げています。

商品コードで単価を引こうとして #N/A になった例で考えます。E2の1003は、CSVから貼り付けた文字列になっています。

B列の商品コードは数値なので、文字列の「1003」とは一致しません。表示形式が「標準」なら文字列の数字は左揃えになるので、見分けるヒントになります。ISNUMBER(E2)がFALSEを返せば確定です。

検索値の側が文字列なら、VALUEで数値に変えてから検索します。

=VLOOKUP(VALUE(E2),$B$2:$C$6,2,FALSE)

これで1003が数値として検索され、ぶどうの単価400が返ります。反対に、表の側が文字列で検索値が数値なら、&"" で検索値を文字列にそろえましょう。

=VLOOKUP(E2&"",$B$2:$C$6,2,FALSE)

ただ、数式でそろえるのはあくまで応急処置。元データの列ごと直しておかないと、別の数式で同じ #N/A がまた出てきます。元データを整える手順(TRIM・VALUE・ASC)は、Excel集計・検索が合わない原因5つと直し方で解説しています。

参照範囲・一致の指定が原因の#N/A

チェックの3つがそろっていても #N/A が消えないなら、問題は数式の側にあります。範囲の取り方か、一致の指定がずれているはず。

検索列が範囲の左端にない(VLOOKUP)

商品コード(B列)から単価を引こうとして、範囲をA列から指定した例です。E2には数値の1003が入っています。

VLOOKUPは、範囲の1列目から検索値を探す関数。次の式では、商品名が並ぶA列で1003を探すことになります。

=VLOOKUP(E2,$A$2:$C$6,3,FALSE)

A列に1003は無いため、見つからずに #N/A が返ります。範囲は検索したい列から始め、列番号も数え直しましょう。

=VLOOKUP(E2,$B$2:$C$6,2,FALSE)

B列が1列目になるので、単価のC列は2列目。これでぶどうの単価400が返ります。

逆に、商品コードから左側の商品名を取りたい場合、VLOOKUPでは届きません。XLOOKUPなら左方向も検索でき、XLOOKUPが使えない版ではINDEXとMATCHの組み合わせが使えます。

=XLOOKUP(E2,$B$2:$B$6,$A$2:$A$6)
=INDEX($A$2:$A$6,MATCH(E2,$B$2:$B$6,0))

どちらもE2が1003なら「ぶどう」を返します。XLOOKUPの引数の詳細は、6症状で直すXLOOKUP関数の使い方へどうぞ。

$固定忘れでコピー時に範囲がずれる

最初の行は正しく出るのに、下にコピーした行だけ #N/A になる。そんなときは、範囲に $ が付いているかを見てください。

F2に $ なしの式を入れて下にコピーすると、範囲も1行ずつ下にずれていきます。

F2: =VLOOKUP(E2,A2:C6,3,FALSE)
F3: =VLOOKUP(E3,A3:C7,3,FALSE)
F4: =VLOOKUP(E4,A4:C8,3,FALSE)

F4の範囲は4〜8行目なので、2行目のりんごが外れています。E4の「りんご」が見つからず、#N/A になるわけです。範囲を $A$2:$C$6 と絶対参照にすれば、コピーしても範囲は動きません。

=VLOOKUP(E2,$A$2:$C$6,3,FALSE)

デスクトップ版では、数式の中で範囲を選んだ直後にF4キーを押すと $ が付きます。キーが効かない環境なら、$ を直接入力しても同じです。引数の基本から確かめたいときは、ExcelのVLOOKUP関数の使い方も参考にしてください。

範囲が対象の行を含んでいない

表に行を追加したあと、新しい行の商品だけ #N/A になるケースです。たとえば7行目に「かき(1006・250円)」を足しても、範囲が $A$2:$C$6 のままなら7行目は検索されません。

範囲を $A$2:$C$7 に広げるか、列全体を指定すると直ります。

=VLOOKUP(E2,$A:$C,3,FALSE)

列全体なら、行を足しても範囲から漏れません。表をテーブルに変換し、数式でテーブルを参照する方法も便利です。行を追加すると、参照範囲も自動で広がります。

VLOOKUPの第4引数の省略(近似一致)

VLOOKUPの第4引数を省略すると、TRUE(近似一致)として動きます。近似一致は、範囲の1列目が昇順に並んでいる前提の検索方法。並んでいない表では、あるはずの値で #N/A が出たり、別の行の値が返ったりすることがあります。

さらに怖いのは、#N/A にならないケースです。商品コード順に並んだ表で、存在しない1006を近似一致で探してみます。

=VLOOKUP(1006,$B$2:$C$6,2)

結果は200。1006以下で最大の1005(なし)の単価が、エラーにならずに返ってきます。存在しないコードなのに、それらしい数字が出てしまうわけです。

第4引数にFALSEを付けると完全一致になり、正しく #N/A が返ります。

=VLOOKUP(1006,$B$2:$C$6,2,FALSE)

表に無いものは無いと知らせてくれるので、コードや名前で探すならFALSEを付けるのが基本。FALSEの代わりに0と書いても同じ意味になります。

MATCHの第3引数の省略

MATCHの第3引数も、省略すると1(近似一致)として扱われます。検査範囲が昇順に並んでいる前提なので、商品名のように並んでいない列では注意が必要です。#N/A になるか、誤った位置が返ることがあります。

=MATCH("ぶどう",$A$2:$A$6)
=MATCH("ぶどう",$A$2:$A$6,0)

1行目は近似一致なので、並んでいない商品名の列では結果があてになりません。第3引数に0を指定した2行目は完全一致になり、並び順に関係なく3(3番目)を返します。

INDEXと組み合わせるときも、MATCHには0を付けておきましょう。第3引数の1と-1の違いは、ExcelのMATCH関数の使い方で解説しています。

そもそも検索値が存在しない場合と、直した後のIFNA

表に本当に無いときの考え方

ここまでのチェックをすべて通っても #N/A なら、検索値が表に無い可能性が高いでしょう。この場合、#N/A は正しい結果。マスターへの登録漏れや、検索値の打ち間違いを知らせてくれています。

対処は2つに分かれます。登録すべきデータなら、マスターに行を追加して範囲に含まれているかも確認しましょう。未登録がありうる表なら、次のIFNAで表示を整えます。

検索値のセル自体が #N/A になっていないかも見ておきたいところ。公式ヘルプによると、手入力された #N/A やNA関数も原因になります。別の数式から #N/A を受け取っていれば、その数式までさかのぼって直してください。

#N/A だけを判定するなら、ISNA関数が便利です。使い方はExcelのISNA関数の使い方を参考にしてください。

原因を直してからIFNAで処理する

見つからないときに「未登録」と表示したいなら、IFNAで #N/A だけを置き換えます。

=IFNA(VLOOKUP(E2,$A$2:$C$6,3,FALSE),"未登録")

XLOOKUPなら、第4引数に見つからない場合の表示を指定できます。

=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"未登録")

IFERRORではなくIFNAを勧めるのは、隠すエラーの範囲が違うから。IFERRORはすべてのエラーを置き換えるので、列番号ミスの #REF! も「未登録」に見えてしまいます。IFNAなら #N/A 以外のエラーはそのまま残るため、数式の誤りに気づけるわけです。

どちらの関数も、原因を直さずに使うと「あるはずのデータ」まで「未登録」と表示されます。空白・型違い・範囲を確かめてから、最後に付けるのが正しい順番。書き方の詳細はIFNA関数の解説とIFERROR関数の解説にまとめています。

よくある質問(FAQ)

Q. VLOOKUPをXLOOKUPに替えれば、#N/Aは出なくなりますか?

第4引数の付け忘れや、検索列が左端にないことによるトラブルは防げます。XLOOKUPは一致モードを省略しても完全一致で、検索範囲と戻り範囲を別々に指定するからです。

ただし、空白や型違いがあればXLOOKUPでも同じく #N/A になります。データ側の原因は、関数を替えても残る点に注意してください。

Q. #N/Aではなく#REF!や#VALUE!が出ます

エラー名が違えば、直す場所も違います。早見表の右端の列で、自分の関数に当てはまる行を確かめてください。#VALUE! の切り分けは、Excel #VALUE!エラーの直し方が症状別に整理しています。

Q. SUMの合計が#N/Aになってしまいます

合計する範囲に #N/A のセルが1つでもあると、合計の結果も #N/A になります。まずは範囲内の #N/A を探し、元の検索式の原因を直しましょう。

未登録が正しい結果なら、元の式をIFNAで0に置き換える方法もあります。=IFNA(VLOOKUP(E2,$A$2:$C$6,3,FALSE),0) のようにしておけば、合計の計算が止まりません。

まとめ

Excelの #N/A は、検索関数が値を見つけられなかったというサイン。「あるはずなのに」と感じたら、数式より先に検索値のセルを疑いましょう。確かめる順番と直し方は次のとおりです。

  • 余分な空白: LENで文字数を比べ、TRIM・SUBSTITUTEで取り除く
  • 型違い: ISNUMBERで比べ、VALUEや &"" でそろえる
  • 範囲のずれ: 検索列を範囲の1列目にし、$ で固定し、追加した行まで含める
  • 一致の指定: VLOOKUPはFALSE、MATCHは0を付ける
  • 存在しない: 登録漏れを直すか、最後にIFNAで表示を整える

IFERRORで隠すのは簡単ですが、原因が残ったままだと集計の数字そのものが信用できなくなります。次に #N/A が出たら、まずは30秒チェックから試してみてください。

#N/A 以外のエラー値が出たときは、Excelエラー値12種類の原因と対処法一覧から原因を探せます。

タイトルとURLをコピーしました