VLOOKUPでエラーが出る原因は？#N/A・#REF!の直し方を初心者向けに解説

• コピーしました

 

 

ExcelでVLOOKUP関数を使っていると、「#N/A」や「#REF!」などのエラーが表示され、困ってしまうことはありませんか？

 

VLOOKUPでエラーが出る場合は、検索値が見つからない、数式の参照範囲が間違っている、列番号の指定に問題があるなど、いくつかの原因が考えられます。

 

エラーの種類を確認すれば、修正すべき箇所を絞り込めます。

 

この記事では、VLOOKUPでエラーが出る原因と対処法を初心者向けに解説します。

 

具体的な数式やエラー別の比較表も紹介するので、Excelの操作に慣れていない方も参考にしてください。

VLOOKUPでエラーが出た場合は、まずエラーの種類を確認することが大切です。

 

エラーによって原因が異なるため、やみくもに数式を書き直すよりも、表示されたエラーに合わせて対処すると効率よく解決できます。

VLOOKUPでエラーが出る原因一覧

主な原因	確認するポイント	対処法
検索値が見つからない	検索対象のデータがあるか	検索値とデータを照合する
数値と文字列の不一致	セルのデータ形式	数値・文字列の形式を確認する
余分な空白や文字の違い	全角・半角やスペース	不要な空白や文字を修正する
検索範囲の指定ミス	範囲の左端と参照先	正しいセル範囲を指定する
列番号の指定ミス	列番号と範囲内の列数	正しい列番号に修正する
検索方法の指定ミス	TRUE・FALSEの指定	完全一致ならFALSEを指定する

 

VLOOKUPは、指定した範囲の左端の列から検索値を探し、指定した列にある値を返す関数です。

 

検索値の位置や範囲、列番号などの指定が正しくないと、期待した結果が得られません。

検索値がデータに存在しない

VLOOKUPで「#N/A」が表示される原因の一つは、指定した検索値が検索範囲に見つからないことです。

 

例えば、社員番号「1005」の社員名を検索しているのに、参照先の表に「1005」が登録されていなければ、完全一致の検索では該当するデータが見つかりません。

 

検索値そのものが存在しない場合は、参照先のデータを追加するか、正しい検索値を指定する必要があります。

数値と文字列の形式が一致していない

見た目が同じ数字でも、Excel内部で数値として扱われている場合と、文字列として扱われている場合があります。

 

例えば、社員番号の「00123」を文字列として保存している表と、数値の「123」として保存している表では、データの扱いが異なります。

 

このような形式の違いは、VLOOKUPで一致する値が見つからない原因になることがあります。

 

対処するときは、検索値と参照先のデータがどのような形式で保存されているかを確認しましょう。

 

社員番号や商品コードなど、先頭のゼロが必要なデータは、単純に数値へ変換すると情報が失われることがあるため注意が必要です。

検索範囲や列番号が間違っている

VLOOKUPでは、検索する範囲と、結果を取り出す列番号を正しく指定する必要があります。

 

例えば、次の数式を見てみましょう。

 

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

 

この数式では、A2の値をD2から探し、一致した行のE列にある値を返します。

 

検索範囲を誤ってE列から指定した場合、D列にある検索値を見つけられません。

 

また、範囲内に存在しない列番号を指定すると、#REF!が表示される原因になります。

 

数式を修正するときは、検索値・検索範囲・列番号の3点を順番に確認しましょう。

セルに余分なスペースが含まれている

検索値や参照先のデータに不要な空白が含まれていると、完全一致の検索で一致しない場合があります。

 

例えば、次の2つの文字列は見た目が似ていても、末尾の空白の有無が異なります。

 

• 東京支店
• 東京支店
  

CSVファイルや外部システムからコピーしたデータでは、不要な空白が混入していることがあります。

 

半角スペースを取り除く場合は、TRIM関数が役立ちます。

 

=TRIM(A2)

 

ただし、TRIM関数は全角スペースを同じようには処理しません。

 

全角スペースも除去したい場合は、SUBSTITUTE関数などを組み合わせて処理する方法があります。

VLOOKUPのエラー別の原因と直し方

VLOOKUPのエラーは、表示されたエラーコードによって原因を絞り込めます。

 

代表的な4種類のエラーについて、原因と具体的な対処法を確認しましょう。

エラー①：#N/Aが表示される場合

#N/Aは、検索値が見つからないときなどに表示されるエラーです。

 

VLOOKUPで完全一致を指定している場合、検索値に一致するデータが検索範囲の左端の列に存在しなければ、#N/Aが返されます。

 

主な原因は次のとおりです。

 

• 検索値が参照先の表に存在しない
• 検索値に余分な空白がある
• 数値と文字列の形式が一致していない
• 全角と半角など、文字の違いがある
• 検索範囲の指定が間違っている

 

対処法として、まず参照先の表に検索値が存在するかを確認します。

 

値が存在するのにエラーが出る場合は、データ形式や空白、数式の参照範囲を見直しましょう。

 

Microsoft公式サポートでも、#N/Aは検索対象が見つからない場合などに発生すると説明されています。

 

参考：Microsoft公式｜エラー値 #N/A を修正する方法

エラー②：#REF!が表示される場合

#REF!は、数式の参照先が無効になっている場合などに表示されます。

 

VLOOKUPでは、指定した検索範囲の列数を超える列番号を設定した場合にも発生します。

 

例えば、次の数式を確認してください。

 

=VLOOKUP(A2,D2:E100,3,FALSE)

 

検索範囲はD列とE列の2列しかありません。

 

それにもかかわらず、列番号に「3」を指定しているため、範囲内に存在しない3列目を取得しようとしています。

 

この場合は、数式の目的に合わせて列番号を修正します。E列の値を取得したいのであれば、列番号を「2」に変更します。

 

修正例：

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

 

参照先のセルや列が削除されている場合も、数式に無効な参照が残っていないか確認しましょう。

エラー③：#VALUE!が表示される場合

#VALUE!は、VLOOKUPの引数に問題がある場合などに発生します。

 

代表的な原因には、列番号に文字列や0などの1未満の値を指定しているケースや、検索値が255文字を超えているケースがあります。

 

例えば、次の数式は列番号が0になっています。

=VLOOKUP(A2,D2:E100,0,FALSE)

 

VLOOKUPの列番号は1以上で指定する必要があるため、この数式は適切ではありません。

 

E列の値を取得したい場合は、次のように修正します。

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

 

なお、#VALUE!の原因は一つではありません。

 

列番号だけでなく、数式の引数や検索値の長さなども確認しましょう。

 

参考：Microsoft公式｜VLOOKUP関数の #VALUE! エラーを修正する方法

エラー④：#NAME?が表示される場合

#NAME?は、Excelが数式内の関数名や名前などを認識できない場合に表示されます。

 

例えば、関数名を間違えて入力すると、エラーの原因になります。

 

誤った例：

=VLOKUP(A2,D2:E100,2,FALSE)

 

正しい例：

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

 

また、文字列を数式に直接入力するときは、必要に応じてダブルクォーテーションで囲む必要があります。

 

例えば、社員名を直接指定する場合は、次のように記述します。

 

=VLOOKUP("田中",D2:E100,2,FALSE)

 

関数名のスペルや、文字列の引用符、定義名などを確認して修正しましょう。

VLOOKUPで値が出ないときに確認したいポイント

エラーが表示されていなくても、VLOOKUPが期待どおりの値を返さない場合があります。

 

そのようなときは、次の3つのポイントを確認しましょう。

検索値が範囲の一番左の列にあるか確認する

VLOOKUPは、指定した範囲の左端の列で検索値を探す関数です。

 

例えば、商品コードがC列、商品名がB列にある表で、C列の商品コードからB列の商品名を取得しようとしても、VLOOKUPではそのまま検索できません。

 

VLOOKUPを使用する場合は、検索値を含む列が範囲の左端になるように指定する必要があります。

 

この制約を避けたい場合は、XLOOKUP関数など、検索列と戻り値の列を別々に指定できる関数を検討する方法もあります。

検索範囲を絶対参照で固定する

数式を下の行へコピーしたときに、検索範囲がずれてしまう場合があります。

 

例えば、次の数式を下へコピーすると、通常の相対参照では検索範囲も移動します。

 

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

 

検索範囲を固定するには、絶対参照を使います。

 

=VLOOKUP(A2,$D$2:$E$100,2,FALSE)

 

このように「$」を付けると、数式をコピーしても検索範囲が固定されます。

 

Excelでは、セル参照を選択してF4キーを押すと、絶対参照などに切り替えられます。

 

キーボードによってはFnキーとの組み合わせが必要です。

完全一致の指定がFALSEになっているか確認する

VLOOKUPの最後の引数は、検索方法を指定する部分です。

 

完全一致で検索したい場合は、FALSEまたは0を指定します。

 

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

 

一方、TRUEまたは1を指定すると近似一致で検索します。

 

また、最後の引数を省略した場合もTRUEが指定されたものとして扱われます。

 

近似一致は、検索範囲の左端の列が昇順に並んでいることを前提とするため、意図しない値が返されることがあります。

 

社員番号や商品コードなど、特定の値を正確に検索したい場合は、FALSEを指定する方法がおすすめです。

VLOOKUPのエラーをIFERROR関数で処理する方法

VLOOKUPでエラーが出た場合は、原因を修正することが基本です。

 

ただし、検索値がまだ登録されていない場合など、エラーになることが想定される場面では、IFERROR関数を組み合わせて表示を整えられます。

エラー時に空白を表示する数式

エラーが発生したときに空白を表示したい場合は、次の数式を使用します。

 

=IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"")

 

この数式では、VLOOKUPが正常に値を返した場合はその値を表示し、エラーになった場合は空白を表示します。

 

ただし、空白にすると、検索値が存在しないのか、数式に問題があるのかが分かりにくくなることがあります。

 

原因の調査中は、エラーをそのまま表示しておく方が適切な場合もあります。

エラー時に「該当なし」と表示する数式

エラー時に理由が分かるようにしたい場合は、独自のメッセージを指定できます。

 

=IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"該当なし")

 

この数式では、VLOOKUPがエラーを返すと「該当なし」と表示します。

 

ただし、IFERRORは#N/Aだけでなく、#REF!や#VALUE!などのエラーも置き換えます。

 

そのため、数式の参照範囲や列番号が間違っている場合でも、エラーが見えなくなることに注意してください。

 

IFERRORはエラーを修正する関数ではなく、エラーが発生したときの表示を制御する関数です。

VLOOKUPのエラーに関するよくある質問

VLOOKUPで急にエラーが出るのはなぜ？

参照先のデータが変更された、検索値が削除された、数式の参照範囲が変わったなどの原因が考えられます。

 

まずはエラーの種類を確認し、検索値・検索範囲・列番号・検索方法の順番で見直してみましょう。

VLOOKUPで一致する値があるのに#N/Aになるのはなぜ？

見た目が同じでも、余分な空白や文字の違い、数値と文字列の形式の違いなどが原因で一致しない場合があります。

 

検索値と参照先のデータを比較し、不要な空白や形式の違いがないかを確認してください。

VLOOKUPのエラーを一括で直す方法はある？

原因が同じであれば、検索値やデータ形式をまとめて修正することで、多数のエラーを解消できる場合があります。

 

ただし、エラーの原因が行ごとに異なる場合は、一括置換だけでは解決できません。

 

まず原因を特定してから、置換機能やTRIM関数などを使用しましょう。

VLOOKUPでエラーが出る場合はXLOOKUPに変えるべき？

Excelの対応バージョンでXLOOKUPを使用できる場合は、切り替えを検討できます。

 

XLOOKUPは、VLOOKUPと異なり、検索列の左側にある値も取得でき、列番号を指定する必要もありません。

 

また、既定で完全一致検索を行います。

 

ただし、検索値が見つからない場合などにはエラーが発生することがあります。

 

XLOOKUPに変えれば、すべてのエラーがなくなるわけではありません。

まとめ｜VLOOKUPのエラーは原因を確認して正しく修正しよう

VLOOKUPでエラーが出る場合は、エラーの種類を確認してから、検索値・データ形式・参照範囲・列番号・検索方法を順番にチェックすることが大切です。

 

最後に、主なエラーの対処法を整理します。

 

エラー	まず確認すること
#N/A	検索値が存在するか、データ形式が一致しているか
#REF!	参照先が有効か、列番号が範囲内か
#VALUE!	引数の指定や列番号が正しいか
#NAME?	関数名や文字列の指定が正しいか

 

エラー表示を消すだけでは、根本的な問題が残ることがあります。

 

まずは数式や参照先を正しく修正し、必要に応じてIFERROR関数を活用しましょう。

 

原因を一つずつ確認すれば、VLOOKUPのエラーにも落ち着いて対処できるようになります。

参考資料

• Microsoft公式｜VLOOKUP関数
• Microsoft公式｜エラー値 #N/A を修正する方法
• Microsoft公式｜VLOOKUP関数の #VALUE! エラーを修正する方法

• コピーしました