【Excel】エクセルのVLOOKUPでエラーが出る原因と対処法（反映されない・一致しない）

いつも記事を読んでいただきありがとうございます！！！ これからもお役に立てる各情報を発信していきますので、今後ともよろしくお願いします(^^)/

ExcelでVLOOKUP関数を入力したのに、検索結果が反映されない、値が一致しない、またはエラーが表示されて困ることがあります。

VLOOKUPは商品コードや社員番号を手がかりに、別表から価格、氏名、在庫数などを取り出せる便利な関数です。

一方で、検索範囲、列番号、データ形式、検索方法のどれかが少しでも合っていないと、#N/Aや#REF!などのエラーになったり、意図しない値を返したりします。

この記事では、VLOOKUPでエラーが出る代表的な原因から、数式を反映させるための確認方法、完全一致で正確に検索するコツまで解説します。

サンプルデータは、1行目に見出しがある表を前提に説明します。

 

エクセルのVLOOKUPでエラーを直す基本設定

商品コード	商品名	単価
A001	ノート	180
A002	ペン	120
A003	ファイル	250

それではまず、VLOOKUPが正しく結果を返すための基本設定について解説していきます。

結論として、検索値を表の左端列に置き、完全一致の指定を使うことで、多くの反映されない問題を防げます。

たとえばF2セルに商品コードを入力し、G2セルに商品名を表示したい場合は、次のように入力します。

この数式では、F2の値をA列で検索し、範囲内の2列目であるB列の商品名を返します。

VLOOKUPは指定した範囲のいちばん左の列だけを検索対象にする関数です。

 

検索値と検索範囲の先頭列

VLOOKUPで最初に確認したいのは、検索値と検索範囲の位置関係です。

VLOOKUPは、検索範囲の左端の列から検索を開始します。

たとえば商品コードがA列、商品名がB列、単価がC列なら、検索範囲はA2:C4のように商品コード列から指定します。

検索値がB列にあるのに、A2:C4を指定して商品名から単価を検索しようとしても、VLOOKUPでは期待どおりに検索できません。

商品名を検索値にしたい場合は、商品名列を範囲の左端にした別表を用意するか、XLOOKUP関数やINDEX関数とMATCH関数の組み合わせを検討しましょう。

参照元の一覧表に重複したコードがある場合、VLOOKUPは上から最初に見つけた行を返します。

そのため、コード列はできるだけ一意になるよう管理することが重要です。

 

完全一致を指定する第四引数

続いては、検索方法を指定する第四引数を確認していきます。

商品コード、社員番号、郵便番号のように、ぴったり同じ値を探す場合は、第四引数にFALSEを入力します。

FALSEを省略すると、ExcelはTRUEと同じ近似一致として扱います。

完全一致が必要な検索では、FALSEを省略しないことが安全です。

近似一致では、検索値以下で最も近い値を返すため、コード検索では誤った行の内容が表示されるおそれがあります。

TRUEを使うのは、点数から評価を返す表、数量帯から割引率を返す表など、基準値が昇順に並んだ区分表を検索するときです。

近似一致を使う場合は、検索範囲の先頭列を小さい順に並べ替える必要があります。

 

数式の固定とコピー範囲

続いては、数式を下方向へコピーするときのセル参照を確認していきます。

検索値のF2は行ごとに変化させる必要がありますが、検索する一覧表のA2:C4は変化させないのが一般的です。

そこで一覧表の参照には、$A$2:$C$4のような絶対参照を使います。

F2セルに数式を入力した後、セル右下のフィルハンドルを下へドラッグすると、F3、F4の検索値に合わせて結果を取り出せます。

検索範囲を固定しないままオートフィルすると、参照範囲もずれてエラーの原因になります。

数式バーで範囲を選択した後にF4キーを押すと、相対参照と絶対参照を切り替えられます。

 

VLOOKUPの一致しないデータ形式

検索値	表示形式	検索結果
00125	文字列	#N/A
125	数値	一致しない場合あり
A001	文字列	一致

続いては、見た目は同じでもVLOOKUPで一致しないデータ形式について確認していきます。

入力値と検索表の値が同じように見えても、片方が文字列、もう片方が数値なら、完全一致検索では別の値として扱われます。

 

数値と文字列の判別

数値と文字列の違いは、VLOOKUPで#N/Aが出る大きな原因です。

セルの左上に緑色の三角形が表示され、「数値が文字列として保存されています」と案内される場合があります。

ただし、見た目だけでは判断しにくいケースもあります。

確認用として、=ISTEXT(A2)を入力するとA2が文字列ならTRUEを返し、=ISNUMBER(A2)を入力すると数値ならTRUEを返します。

検索値と表側のキー列の両方に同じ確認式を入れると、形式の違いを切り分けやすくなります。

セルの表示形式を変えるだけでは、内部のデータ形式が変わらないことがあります。

形式を数値に直すなら、エラー表示の警告メニューから「数値に変換」を選ぶ方法が便利です。

 

先頭のゼロと文字列変換

続いては、先頭のゼロを含むコードの扱いを確認していきます。

社員番号0012や商品番号000345のようなデータは、数値に変換すると先頭のゼロが消えます。

コードとして検索するなら、入力側と検索表側を文字列にそろえる方法が確実です。

たとえばF2が125という数値でも、この式は00125という文字列を返します。

一方、文字列を数値に変えるなら、=VALUE(F2)または=–F2を使えます。

コードの桁数に意味がある場合は、安易に数値化せず文字列として統一しましょう。

元データがCSVから取り込まれた場合、列ごとに形式が混在しやすいため、インポート後の確認も必要です。

 

空白文字と見えない文字

続いては、目に見えない空白文字による不一致を確認していきます。

コピーしたデータには、セルの前後に半角スペース、全角スペース、改行コードが含まれている場合があります。

これらは画面上で見分けにくいものの、VLOOKUPの完全一致では別の文字列です。

TRIM関数は余分な半角スペースを整え、CLEAN関数は印刷できない文字を除去します。

全角スペースが含まれる場合は、=SUBSTITUTE(A2,”　”,””)を組み合わせて除去します。

データを貼り付けた直後に不一致が起きるときは、空白文字を疑うのが近道です。

 

VLOOKUPのエラー種類と確認手順

エラー表示	主な原因	確認箇所
#N/A	検索値がない	コードと形式
#REF!	列番号が範囲外	第三引数
#VALUE!	引数の指定不正	数式全体

続いては、VLOOKUPで表示されるエラーの種類と確認手順について解説していきます。

エラー名ごとに原因の候補を分けて考えると、修正すべき箇所を素早く絞り込めます。

 

#N/Aが表示される原因

それではまず、もっともよく表示される#N/Aについて解説していきます。

#N/Aは、指定した検索値が検索範囲の先頭列に見つからないことを示すエラーです。

まず検索値をコピーし、検索表の先頭列でCtrlキーとFキーを押して検索してみましょう。

見つからない場合は、コードの入力ミス、廃番コード、検索範囲の不足が考えられます。

見つかるのに#N/Aが出る場合は、数値と文字列、先頭ゼロ、空白文字を確認します。

#N/Aは関数が壊れた表示ではなく、該当データが見つからないという通知です。

該当なしを空欄にしたい場合は、IFERROR関数でVLOOKUPを包みます。

ただし、エラーを空欄にすると入力ミスにも気づきにくくなります。

業務表では「該当なし」と表示する設定も実用的です。

 

#REF!と列番号の範囲

続いては、#REF!が出るときの第三引数について確認していきます。

VLOOKUPの第三引数は、検索範囲の左から何列目の値を返すかを指定する数値です。

検索範囲がA2:C4なら、指定できる列番号は1、2、3です。

ここで4を指定すると、範囲内に4列目が存在しないため#REF!が表示されます。

たとえば単価を返す数式は、=VLOOKUP(F2,$A$2:$C$4,3,FALSE)です。

第三引数はワークシート全体の列番号ではなく、選択した検索範囲内で数えます。

検索範囲を途中から指定したときは、列番号の数え方が変わるため注意しましょう。

 

#VALUE!と数式の入力ミス

続いては、#VALUE!が出るときの数式入力を確認していきます。

#VALUE!は、引数に必要な値が入っていない、区切り記号や参照の入力が適切でない場合に表示されることがあります。

VLOOKUPの基本構文は、=VLOOKUP(検索値,検索範囲,列番号,検索方法)です。

列番号には1以上の整数を指定し、検索方法にはFALSEまたはTRUEを指定します。

数式を編集するときは、数式バーで括弧の対応やカンマの位置を確認しましょう。

Excelの地域設定によっては、引数の区切りにセミコロンが使われる環境もあります。

数式をコピーして使う場合も、参照先のシート名や範囲が変わっていないか確認が必要です。

 

別シート参照とVLOOKUPの反映条件

シート名	A列	B列	C列
商品マスタ	商品コード	商品名	単価
入力表	検索コード	商品名	単価

続いては、別シートの表を参照するときにVLOOKUPが反映されない条件について確認していきます。

別シートを参照する場合も基本の考え方は同じですが、シート名、参照範囲、再計算設定を正しく扱う必要があります。

 

シート名を含めた検索範囲

別シートに商品マスタがある場合は、検索範囲の前にシート名を付けます。

商品マスタのように日本語や空白を含むシート名では、シート名をシングルクォーテーションで囲むと安全です。

数式を手入力するより、数式の入力途中で参照先シートをクリックし、範囲をドラッグして指定すると入力ミスを減らせます。

別シート参照でも、検索キーが参照範囲の左端列にある必要があります。

シート名を変更した場合、通常はExcelが参照式を自動更新しますが、外部ファイル参照ではリンク切れに注意が必要です。

 

計算方法と数式の再計算

続いては、数式が入力されているのに結果が更新されない場合を確認していきます。

Excelの計算方法が手動になっていると、検索値やマスタ表を変更してもVLOOKUPの結果がすぐに反映されないことがあります。

数式タブから計算方法の設定を確認し、通常は自動を選択します。

一時的に更新したい場合は、F9キーでブックを再計算できます。

数式が文字列として表示される場合は、セルの表示形式が文字列になっている可能性があります。

表示形式を標準に戻し、数式を入力し直すと計算されます。

先頭にアポストロフィが付いた数式も文字列扱いになり、計算されません。

 

検索範囲の拡張漏れ

続いては、マスタ表に追加したデータが検索できない問題を確認していきます。

VLOOKUPの参照範囲がA2:C100のままで、101行目以降に新しい商品を追加すると、その商品は検索対象になりません。

この場合、検索範囲を必要な行まで広げる必要があります。

あらかじめ十分な行数まで指定する方法もありますが、不要な空白を広く含めると管理しにくくなる場合があります。

おすすめは、元データをテーブルとして設定し、列名で参照する方法です。

テーブルはデータ追加時に範囲が自動拡張されるため、新しい行だけVLOOKUPで検索できないというトラブルを防ぎやすくなります。

 

VLOOKUPを安全に使う数式の工夫

検索コード	商品名の結果	単価の結果
A001	ノート	180
A999	該当なし	該当なし

続いては、VLOOKUPのエラーを見やすくし、日常業務で安全に使う数式の工夫について確認していきます。

単にエラーを消すだけでなく、入力漏れと該当なしを区別できる設計にすると、表の信頼性が上がります。

 

IFERROR関数によるエラー表示

それではまず、IFERROR関数を組み合わせた表示について解説していきます。

IFERROR関数は、数式の結果がエラーになったときだけ、別の文字列や値を表示する関数です。

この式なら、正常に検索できたときは商品名を表示し、検索できないときは該当なしと表示します。

利用者が見る一覧表では、#N/Aをそのまま出すより意味の分かる文言に変える方法が有効です。

ただし、検索範囲の設定ミスまで該当なしとして隠れる場合があるため、作成時には元のVLOOKUPで動作確認しましょう。

 

空欄の検索値を判定する式

続いては、検索値が未入力のときに不要な表示を出さない方法を確認していきます。

検索セルが空欄の状態でVLOOKUPを実行すると、空欄を検索して意図しない結果が出ることがあります。

この式は、F2が空欄なら結果も空欄にし、値が入力されたときだけVLOOKUPを実行します。

さらに該当なしも分かりやすくしたい場合は、IFERRORと組み合わせます。

入力フォームや見積書のように、空欄行が多いシートでは特に役立つ設計です。

 

XLOOKUP関数への置き換え

続いては、新しいExcelで使えるXLOOKUP関数について確認していきます。

XLOOKUP関数は、検索列と戻り値の列を別々に指定できるため、VLOOKUPの左端列という制約を受けません。

この数式では、F2をA列で検索し、B列の商品名を返します。

VLOOKUPのように列番号を数える必要がないため、列の追加や削除で式が分かりにくくなる問題も減らせます。

XLOOKUPが利用できる環境なら、将来的な表の変更に強い検索式として有力です。

ただし、古いExcelでファイルを共有する場合は、相手の環境でXLOOKUPが使えるか確認しましょう。

 

まとめ　反映されない・一致しないエクセルのVLOOKUP対処法

症状	主な対処法
#N/Aが出る	検索値、データ形式、空白を確認
#REF!が出る	第三引数と検索範囲を確認
更新されない	計算方法と参照範囲を確認

エクセルのVLOOKUPでエラーが出る場合は、検索値が検索範囲の左端列にあるか、検索範囲に該当データがあるかを最初に確認します。

商品コードや社員番号では、第四引数にFALSEを指定し、完全一致検索にすることが基本です。

見た目が同じ値でも、数値と文字列、先頭ゼロ、前後の空白が異なると一致しません。

そのため、検索値とマスタ表のデータ形式を統一することが、VLOOKUPを安定させる重要な対策です。

#N/Aは該当データが見つからない場合、#REF!は列番号が範囲外の場合に起こりやすいエラーです。

別シートを参照する場合はシート名、絶対参照、検索範囲の追加漏れも確認しましょう。

完成した帳票ではIFERROR関数を使って該当なしと表示し、入力欄が空欄のときは検索しない式にすると見やすくなります。

VLOOKUPの仕組みを理解し、検索値、範囲、列番号、データ形式の順に確認すれば、反映されない・一致しない問題の多くを解決できます。