VLOOKUPでよくあるエラーと対処法｜原因別に解説

VLOOKUP関数を使っていて、#N/Aや#REF!などのエラーに遭遇した経験はないでしょうか。VLOOKUPは便利な関数ですが、引数の指定方法やデータの状態によってさまざまなエラーが発生します。この記事では、VLOOKUPで発生する主なエラーの原因と対処法を具体例とともに解説します。エラーの予防策やIFERROR関数を使った処理方法もあわせて紹介します。

エラーの種類と概要

VLOOKUPで発生する代表的なエラーは以下の3種類です。

エラー	意味	主な発生原因
#N/A	値が見つからない	検索値が存在しない、データの不一致
#REF!	参照が無効	列番号が範囲を超えている
#VALUE!	値が不正	引数の型が間違っている

それぞれのエラーについて、原因と対処法を詳しく見ていきましょう。

#N/Aエラーの原因と対処法

#N/Aエラーは、VLOOKUPで最も多く遭遇するエラーです。「検索値が見つからない」ことを意味します。

原因1：検索値がデータに存在しない

最も単純な原因です。検索値に対応するデータがマスタに登録されていない場合に発生します。

=VLOOKUP("Z999", A2:C10, 2, FALSE)

A列に「Z999」が存在しなければ、#N/Aエラーになります。

対処法として、データの登録漏れがないか確認してください。意図的に存在しない場合は、後述するIFERROR関数でエラーを処理します。

原因2：余分なスペースが含まれている

見た目は同じに見えても、セルの前後に余分なスペース（半角・全角）が含まれていると、完全一致検索で不一致と判定されます。

たとえば、マスタのA2に「A001 」（末尾にスペース）が入力されていて、検索値が「A001」の場合、一致しません。

対処法は、TRIM関数で余分なスペースを除去してから検索することです。

=VLOOKUP(TRIM(E2), A2:C10, 2, FALSE)

マスタ側のデータも同様に整理する場合は、マスタのA列に対してTRIM関数を適用してスペースを除去しておくのが理想です。

原因3：データ型の不一致（数値と文字列）

商品コードが数値として入力されているセルと、文字列として入力されているセルでは、見た目が同じでも一致しません。

セル	値	データ型	セルの表示位置
A2	1001	数値	右寄せ
E2	1001	文字列	左寄せ

セルの値が左寄せか右寄せかで、データ型の違いを確認できます。

対処法は、データ型を揃えることです。数値を文字列に変換して検索する場合は以下のように記述します。

=VLOOKUP(VALUE(E2), A2:C10, 2, FALSE)

または、マスタ側の値を文字列に変換する場合はTEXT関数を使います。

原因4：検索値が範囲の左端列にない

VLOOKUPは、検索範囲の左端列（1列目）から検索値を探します。検索したい値が左端列にないと、正しく検索できません。

A列（商品名）	B列（商品コード）	C列（単価）
ノートPC	A001	89000
マウス	A002	2500

商品コードで検索したい場合、範囲をB:Cに変更するか、INDEX+MATCHを使用してください。

=INDEX(A2:A5, MATCH("A002", B2:B5, 0))

原因5：近似一致モードでのデータ未整列

検索の型をTRUE（近似一致）にした場合、検索範囲の左端列が昇順で並んでいないと、正しい結果を返せません。

対処法は、完全一致が必要な場合はFALSEを指定することです。近似一致が必要な場合は、データを昇順に並べ替えてください。

#REF!エラーの原因と対処法

#REF!エラーは、参照が無効であることを示します。

原因1：列番号が範囲の列数を超えている

=VLOOKUP(E2, A2:C10, 5, FALSE)

範囲A2:C10は3列しかないのに、列番号に5を指定しているため、#REF!エラーが発生します。

対処法は、列番号を範囲の列数以内に修正することです。上記の例では、列番号を1〜3のいずれかに変更します。

原因2：参照先のシートや列が削除された

数式が参照しているシートや列が削除されると、#REF!エラーになります。

対処法として、シートや列を削除する前に、他の数式から参照されていないか確認してください。「数式」タブの「参照元のトレース」機能を使うと、参照関係を視覚的に確認できます。

原因3：列の挿入で列番号がずれた

VLOOKUPの弱点の一つが、列番号をハードコーディング（数値で直接指定）する点です。範囲内に列を挿入すると、意図した列とずれてしまいます。

操作前	操作後
A:商品コード, B:商品名, C:単価	A:商品コード, B:カテゴリ（挿入）, C:商品名, D:単価
列番号2で商品名を取得	列番号2でカテゴリを取得してしまう

対処法として、MATCH関数で列番号を動的に取得する方法があります。

=VLOOKUP(E2, A:D, MATCH("商品名", A1:D1, 0), FALSE)

見出し行から「商品名」の列位置を動的に取得するため、列の挿入・削除の影響を受けません。

#VALUE!エラーの原因と対処法

#VALUE!エラーは、引数の型や値が不正な場合に発生します。

原因1：列番号に0以下の値を指定

=VLOOKUP(E2, A2:C10, 0, FALSE)

列番号は1以上の整数でなければなりません。0や負の値を指定すると#VALUE!エラーになります。

原因2：列番号に文字列を指定

列番号を数式で動的に求めている場合、結果が文字列になっていると#VALUE!エラーが発生します。

=VLOOKUP(E2, A2:C10, "2", FALSE)

数値の2ではなく文字列の”2”が指定されています。VALUE関数で数値に変換するか、数式を見直してください。

原因3：検索値が255文字を超えている

VLOOKUPの検索値は255文字が上限です。これを超える長い文字列を検索しようとすると、#VALUE!エラーになります。対処法として、データの設計を見直すか、LEFT関数で先頭255文字を切り出して検索する方法があります。

IFERROR関数によるエラー処理

エラーが表示されたままのシートは見栄えが悪く、エラーセルを参照する他の数式にも影響します。IFERROR関数でエラーを処理しましょう。

基本的な使い方

=IFERROR(VLOOKUP(E2, A2:C10, 2, FALSE), "該当なし")

VLOOKUPがエラーを返した場合に「該当なし」と表示します。

空白を返す場合

エラー時に何も表示したくない場合は、空文字列を指定します。

=IFERROR(VLOOKUP(E2, A2:C10, 2, FALSE), "")

数値のエラー処理

計算に使う値の場合、エラー時に0を返すことで後続の計算がエラーにならないようにします。

=IFERROR(VLOOKUP(E2, A2:C10, 3, FALSE), 0) * F2

具体的なエラーを特定したい場合

IFERROR関数はすべてのエラーを一括で処理するため、エラーの種類を特定できません。エラーの種類に応じて異なる処理をしたい場合は、IFNA関数を使います。

=IFNA(VLOOKUP(E2, A2:C10, 2, FALSE), "データ未登録")

IFNA関数は#N/Aエラーのみを処理し、#REF!や#VALUE!は数式の問題として表示します。データ未登録と数式のミスを区別できるため、デバッグに役立ちます。

エラーを未然に防ぐテクニック

テーブル機能の活用

データをテーブルとして書式設定すると、構造化参照が使えます。列の挿入や削除に強く、#REF!エラーのリスクを軽減できます。

=VLOOKUP(E2, 商品マスタ, 2, FALSE)

データの入力規則を設定する

検索値の入力セルにドロップダウンリスト（入力規則）を設定すると、マスタに存在する値のみを入力できるようになります。存在しない値の入力を防ぐことで、#N/Aエラーを根本から防止できます。

数式の検証手順

VLOOKUPでエラーが発生した場合の確認手順をまとめます。

検索値がマスタに存在するか確認する
データ型（数値・文字列）が一致しているか確認する
余分なスペースがないかTRIM関数で検証する
列番号が範囲の列数以内か確認する
検索の型（TRUE/FALSE）が正しいか確認する

XLOOKUPへの移行も検討する

VLOOKUPの多くの制限は、XLOOKUP関数で解消されています。

VLOOKUPの課題	XLOOKUPでの解決
列番号のハードコーディング	戻り範囲で指定するため不要
エラー処理にIFERRORが必要	第4引数で指定可能
左方向の検索が不可	方向の制限なし
既定が近似一致	既定が完全一致

Microsoft 365やExcel 2021以降を使用している場合は、新しい数式ではXLOOKUPを使うことをおすすめします。既存のVLOOKUP数式を無理に書き換える必要はありませんが、エラーが頻発する数式はXLOOKUPへの置き換えを検討してみてください。

まとめ

VLOOKUPのエラーは、原因を理解すれば対処できるものがほとんどです。以下のポイントを押さえておきましょう。

#N/Aエラーはスペースやデータ型の不一致が原因であることが多い
#REF!エラーは列番号が範囲を超えていないか確認する
#VALUE!エラーは引数の型や値を見直す
IFERROR関数またはIFNA関数でエラーを適切に処理する
テーブル機能や入力規則を活用してエラーを未然に防ぐ

エラーの原因を一つずつ確認していけば、必ず解決策が見つかります。この記事で紹介した対処法を参考に、VLOOKUPを安心して使いこなしてください。