エクセル VLOOKUPが正しいのに動かない原因と対処法を症状別に徹底解説

「エクセルのVLOOKUPを正しく入力したはずなのに、なぜか動かない……」。そんな経験はありませんか。

• #N/A や 0 が表示される
• 間違った値が返ってくる
• 一部のデータだけ検索できない

実は、VLOOKUPがうまくいかない原因はほぼパターンが決まっています。原因を症状から切り分ければ、ほとんどのトラブルはその場で解決できます。

この記事では、数式は合っているのにVLOOKUPが動かない原因を症状別に整理し、それぞれの対処法を解説します。あわせて、VLOOKUPの限界を超える INDEX+MATCH と XLOOKUP も紹介します。

まずは「症状」から原因を切り分ける

VLOOKUPのトラブルは、画面に出る症状である程度あたりがつきます。下の表で自分の症状を確認し、該当するセクションへ進んでください。

原因①：検索値のデータ形式が違う（数値 vs 文字列）

症状

• #N/A が表示される
• 一覧に同じ値があるのに検索できない

原因

VLOOKUPは、検索値と検索対象のデータ型が一致していないと一致と見なしません。たとえば見た目は同じ「123」でも、片方が数値の 123、もう片方が文字列の “123”（先頭にアポストロフィが付いているなど）だと一致せず、#N/A になります。他システムからコピーした番号やコードでよく起こります。

解決策

まず、どちらの形式かを確認します。

• =ISNUMBER(A1) → TRUE なら数値
• =ISTEXT(A1) → TRUE なら文字列

形式が混在している場合は、どちらかに揃えます。

文字列を数値に変換する：

=VALUE(A1)

数値を文字列に変換する：

=TEXT(A1,"0")

列全体をまとめて変換したいときは、対象列を選択して〔データ〕タブ →〔区切り位置〕→〔完了〕とするだけでも、文字列の数字を数値に戻せます。手早く直したいときに便利です。

参考：エクセルVLOOKUPで（式は間違っていないのに）#N/Aエラーが出る原因と解決方法：文字形式の違いに注意

原因②：検索範囲の1列目に検索キーがない

症状

• #N/A が表示される
• 間違った値が返ってくる

原因

VLOOKUPは、指定した検索範囲のいちばん左の列（1列目）からしか検索できません。たとえば次のような表があるとします。

この表で「名前（B列）」を手がかりに部署を調べることは、検索範囲 A2:C4 のままではできません。検索キーの「名前」が1列目（A列）になく、2列目にあるためです。

解決策

方法は2つあります。

• 検索範囲を作り直す： 検索キーの列が1列目に来るよう、範囲を B2:C4 のように指定し直す。
• INDEX+MATCH を使う： 列の位置に縛られず検索できます。

=INDEX(C2:C4, MATCH("田中", B2:B4, 0))

MATCHが「田中」の行番号（この表では2）を求め、INDEXが同じ行のC列「総務」を返します。INDEX+MATCH の詳しい仕組みは、記事末尾の番外編で解説します。

原因③：完全一致（FALSE）を指定していない

症状

• 間違った値が返ってくる
• 存在しない検索値なのにエラーにならず、別の値が返る

原因

VLOOKUPの4番目の引数（検索方法）を TRUE にする、または省略すると「近似一致」になります。近似一致には次の前提と落とし穴があります。

• 1列目が昇順に並んでいる必要があります。 並んでいないと、結果は予測できません。
• 完全一致する値が無い場合、エラーにならず「検索値以下でもっとも近い値」を黙って返します。気づかないまま誤った値を使ってしまう、最も危険なパターンです。
• 第4引数を省略すると TRUE 扱いになるため、書き忘れがそのまま近似一致になります。

たとえば次の式は、データが昇順でない、あるいは1002が存在しない場合に、意図しない値を返すことがあります。

=VLOOKUP(1002, A2:C4, 3, TRUE)

解決策

商品IDや社員IDのように「ぴったり一致するものを探す」用途では、必ず FALSE（または 0）を指定します。

=VLOOKUP(1002, A2:C4, 3, FALSE)

これで、完全に一致するデータだけを検索し、無ければ #N/A を返すようになります。「黙って近い値を返す」事故を防げます。
なお、後述する XLOOKUP は初期設定が完全一致なので、この指定ミス自体が起こりません。

原因④：検索範囲が相対参照のままズレる

症状

• 数式を下方向にコピーすると、途中から検索できなくなる

原因

検索範囲を相対参照（例：B2:D100）のまま下にコピーすると、行が1つずつズレて B3:D101、B4:D102 ……と範囲が下にずれてしまい、表の末尾のデータが範囲から外れていきます。

解決策

検索範囲を絶対参照（行・列に $ を付ける）にして固定します。範囲を選択して F4 キーを押すと一発で $ が付きます。

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

こうすれば、どこにコピーしても検索範囲がズレません。

補足：相対参照・絶対参照・複合参照の違い

セル参照には3種類あり、$ を付ける位置で固定する向きが変わります。

F4 キーは、押すたびに次の順で切り替わります。

1. A1（相対）→ $A$1（絶対）
2. $A$1 → A$1（行を固定）
3. A$1 → $A1（列を固定）
4. $A1 → A1（相対に戻る）

VLOOKUPでは「検索範囲」を絶対参照で固定するのが定番です。検索値（第1引数）は1行ずつ動かしたいので相対参照のまま、というのが基本の形になります。

原因⑤：検索値が重複している

症状

• 期待した値とは違う行のデータが返ってくる

原因

VLOOKUPは最初に見つかった1件だけを返します。検索対象に同じ値が複数あると、2件目以降は無視されるため、思っていたのと違うデータが返ることがあります。

解決策

まず、条件付き書式の〔重複する値〕で重複の有無を確認します。該当するデータを全件取り出したい場合は、FILTER 関数が便利です。

=FILTER(C2:C100, B2:B100=A2)

条件に合う行をすべて抽出して、下方向にスピル（自動展開）します。
※ FILTER は Microsoft 365・Excel 2021・Excel 2024 で使えます（公式の対応バージョン。Excel 2019・2016では使えません）。古いバージョンでは、重複を避けるために検索キーを一意にする（IDを振り直すなど）対応が現実的です。

原因⑥：別シート・別ブックを参照している

症状

• #REF! や #N/A が出る

原因

別ブックを参照している数式は、参照先ファイルが移動・リネーム・削除されるとリンクが切れ、#REF! になります。なお、通常のリンク式（例：='C:\…\[商品.xlsx]Sheet1'!$A$1）は、参照先を閉じていても保存済みのキャッシュ値は表示できます（ただし最新化はされません）。

解決策

• 参照先ブックを開いた状態にして再計算する。
• リンクが切れている場合は、〔データ〕タブ →〔リンクの編集〕で参照元を貼り直す。
• 複数ブックを安定して結合したいなら、Power Query での取り込みを検討する。

INDIRECTは「別ブックの安定化」には使えない

ネット上には「INDIRECT関数で参照を安定させる」という解説が見られますが、外部ブック参照では逆効果なので注意してください。Microsoft公式のとおり、INDIRECTは参照先ブックが開いていないと #REF! を返します。通常のリンク式と違ってキャッシュ値も保持できないため、閉じたブックは一切読めません。

INDIRECTが本当に役立つのは、同じブック内でシート名を動的に切り替えるケースです。たとえばA1に「1月」「2月」などのシート名を入れて、参照先を切り替える使い方です。

=VLOOKUP(B2, INDIRECT("'"&$A$1&"'!B:D"), 2, FALSE)

この場合、$A$1 の値を変えるだけで参照シートを切り替えられます。シート名に記号やスペースを含む場合に備え、シングルクォートで囲んでおくと安全です。

原因⑦：「0」が返ってくる（参照先が空白）

症状

• 検索自体は成功しているのに、結果が 0 になる

原因

VLOOKUPは検索に成功しても、取り出すセルが空白だと 0 を返します。#N/A ではなく 0 が出るときは、検索のミスではなく「参照先が空欄」のサインです。

解決策

空白を空文字として表示したい場合は、結果が 0 のときだけ空欄にします。

=IF(VLOOKUP(A2,$B$2:$D$100,2,FALSE)="","",VLOOKUP(A2,$B$2:$D$100,2,FALSE))

また、検索値が見つからないときの #N/A を見やすく出し分けたい場合は、IFNA を併用します。

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

原因⑧：そもそもVLOOKUPの仕様に限界がある

ここまでの対処をしても扱いにくい場合、原因はVLOOKUPの仕様そのものにあるかもしれません。VLOOKUPには次の制約があります。

• 検索キーは検索範囲の1列目にしか置けない（左方向に検索できない）
• 取り出す列を列番号で手動指定するため、列の挿入・削除でズレる

これらを回避できるのが INDEX+MATCH と XLOOKUP です。

INDEX+MATCH（バージョンを問わず使える）：

=INDEX(C2:C100, MATCH(A2, B2:B100, 0))

XLOOKUP（よりシンプル）：

=XLOOKUP(A2, B2:B100, C2:C100)

※ XLOOKUP が使えるのは Microsoft 365・Excel 2021・Excel 2024 です。Microsoft公式のとおり、Excel 2019・2016では使えません。買い切り版のExcel 2019以前を使っている場合は、INDEX+MATCH を使ってください。

まとめ：VLOOKUPが動かないときのチェックリスト

• #N/A が出る → 検索値のデータ型（数値か文字列か）と、1列目に検索キーがあるかを確認
• 間違った値が返る → 第4引数に FALSE（完全一致）を指定。重複の有無も確認
• コピーで崩れる → 検索範囲を $ で絶対参照に固定
• #REF! が出る → 参照先ブックを開く／リンクを貼り直す（INDIRECTでの外部参照は不可）
• 0 が返る → 参照先セルが空白。IF や IFNA で表示を整える
• そもそも扱いにくい → INDEX+MATCH、環境が許せば XLOOKUP へ

【番外編】VLOOKUPの限界を超える INDEX+MATCH

VLOOKUPの「1列目しか検索できない」「列番号がズレる」という弱点を解消するのが、INDEX と MATCH の組み合わせです。考え方を分けて理解すれば、難しくありません。

INDEX関数：位置からデータを取り出す

INDEXは「指定した範囲の○番目」を取り出す関数です。

=INDEX(範囲, 行番号, [列番号])

C列の2番目を取り出すなら、結果は「総務」です。

=INDEX(C2:C4, 2)

MATCH関数：値が何番目にあるかを調べる

MATCHは「指定した値が範囲の何番目にあるか」を返します。

=MATCH(検索値, 検索範囲, 検索方法)

「田中」がB列の何番目かを調べると、結果は「2」です。

=MATCH("田中", B2:B4, 0)

※ 第3引数の 0 は完全一致を表します。VLOOKUPの FALSE と同じ役割です。

組み合わせてVLOOKUPの代わりにする

MATCHで「行番号」を求め、それをINDEXに渡します。

=INDEX(取り出したい列, MATCH(検索値, 検索する列, 0))

「田中」の部署（C列）を調べる式は次のとおりで、結果は「総務」です。

=INDEX(C2:C4, MATCH("田中", B2:B4, 0))

INDEX+MATCHがVLOOKUPより優れている点

• 1列目の制約がない： どの列を検索キーにしてもよい。
• 列の挿入・削除に強い： 列番号ではなく「列の範囲」を指定するため、列構成が変わっても壊れにくい。
• 左方向に検索できる： 検索キーより左の列も取り出せる。

応用：行と列を同時に検索する

MATCHを2つ使えば、行と列の両方を検索できます（VLOOKUP単体では不可）。

=INDEX(A1:D3, MATCH("田中", A1:A3, 0), MATCH("部署", A1:D1, 0))

行方向で「田中」、列方向で「部署」を探し、その交点の値を返します。表の行・列どちらの変更にも強い検索です。

新しい環境なら XLOOKUP がいちばん簡単

XLOOKUP は、INDEX+MATCH でできることをよりシンプルに書けます。

=XLOOKUP("田中", B2:B4, C2:C4)

• 検索キーが1列目でなくてよい（左方向の検索も可能）
• 初期設定が完全一致なので、近似一致による誤りが起きない
• 見つからないときの値を [見つからない場合] 引数で直接指定できる

3つの関数を比較すると、次のようになります。

使っているExcelのバージョンが分からないときは、セルに =XLOOKUP( と入力してみてください。引数のヒントが表示されれば対応済み、#NAME? になる場合は非対応なので INDEX+MATCH を使いましょう。

VLOOKUPで行き詰まったら、まずは症状から原因を切り分け、必要に応じて INDEX+MATCH や XLOOKUP に切り替える。これで、検索系のトラブルにはほぼ対応できます。