検索値と同じ番号が表にあるのに、VLOOKUP関数で「#N/A」エラーになります。見た目は同じなのに、なぜ検索できないのでしょうか？
VLOOKUP関数で「#N/A」エラーが表示される場合は、検索値と一致するデータを検索範囲から見つけられていません。
同じ値があるように見えても、数式の設定やセルに入力されたデータの違いによって、Excelが一致する値として認識できないことがあります。
最初に、元データの検索列へオートフィルターを設定し、検索値が実際に存在するか確認してください。
元データに検索値がなければ、VLOOKUP関数の数式が正しくても「#N/A」エラーになります。
VLOOKUP関数の検索値・検索範囲・検索の型が正しく指定されているか確認します。
検索値が検索範囲の左端列にあり、完全一致を表す「0」が指定されているかを順番に調べましょう。
数式の指定に問題がなければ、検索値と検索元のデータそのものを確認します。
値の前後にある空白、全角と半角、見た目が似た記号、数値と文字列の違いを順番に調べてください。
この記事では、VLOOKUP関数で同じ値があるように見えるのに「#N/A」エラーになる原因と直し方を解説します。
数式の設定からデータ型までを順番に確認し、どこで検索値が一致していないのかを特定しましょう。
VLOOKUP関数の設定と検索値の違いを順番に確認し、同じ値があるように見えるのに発生する#N/Aエラーを解消できるようになる
- オートフィルターで検索値が元データにあるか確認する
- 検索値と検索範囲を確認する
- 検索の型に「0」が指定されているか確認する
- 値の前後に空白がないか確認する
- 全角・半角や記号が同じか確認する
- TYPE関数で数値と文字列を確認する
IFERROR関数を使うと「#N/A」エラーを空白や任意の文字へ変更できますが、検索値が一致しない原因は解消されません。
同じ値があるはずなのに検索できない場合は、先に数式の設定やデータの内容を確認してください。
原因を修正したうえで「#N/A」エラーを空白へ変更する方法は、「VLOOKUPとIFERRORでエラーを空白にする方法」で解説しています。
同じ値なのにVLOOKUP関数で#N/Aになる原因
VLOOKUP関数の「#N/A」エラーは、検索値と一致するデータを検索範囲から見つけられない場合に表示されます。
検索値が元データにない場合だけでなく、数式の設定やデータの内容が異なる場合も、VLOOKUP関数は一致する値を見つけられません。
- 元データに検索値が存在しない
- 検索値が検索範囲の左端列にない
- 検索範囲に目的の値が含まれていない
- 検索の型が完全一致になっていない
- 値の前後に余分な空白がある
- 全角・半角や記号が異なる
- 一方が数値でもう一方が文字列になっている
オートフィルターで検索値の有無を確認する
最初に、VLOOKUP関数が検索する元データに、検索値が実際に存在するか確認します。
検索列のデータが多い場合は、目視で探すのではなく、オートフィルターの検索欄を使用してください。
- 元データ内のセルを選択する
- 「データ」タブの「フィルター」をクリックする
- VLOOKUP関数が検索する列のフィルターボタンをクリックする
- 検索欄へ検索値を入力する
- 一致するデータが表示されるか確認する
検索値が表示される
→ 元データに検索値があるため、VLOOKUP関数の設定やデータの違いを確認する
検索値が表示されない
→ 元データに検索値がないか、空白・全角と半角・データ型などに違いがある
オートフィルターで検索値が表示されない場合でも、元データに似た値がないか確認してください。
値の前後に空白がある場合や、全角・半角が異なる場合は、入力した検索値と完全には一致せず、目的のデータを見つけられないことがあります。
オートフィルターで文字列や数値を検索する方法は、「Excelのオートフィルターの使い方」で詳しく解説しています。
VLOOKUP関数の設定と検索範囲を確認する
元データに検索値があることを確認したら、VLOOKUP関数の数式を調べます。
検索値、検索範囲、列番号、検索の型が、目的の検索に合っているか順番に確認しましょう。
=VLOOKUP(E2,A2:C6,3,0)
検索値 E2
検索範囲 A2:C6
列番号 3
検索の型 0検索値が正しく指定されているか確認する
最初の引数には、元データから探したい値または、その値が入力されたセルを指定します。
数式をオートフィルでコピーした場合は、検索値のセル参照が目的の行へ移動しているか確認してください。
検索値が検索範囲の左端列にあるか確認する
VLOOKUP関数は、指定した検索範囲の左端列から検索値を探します。
検索値が検索範囲の2列目以降にあっても、その列から検索することはできません。
商品番号がA列にある
→ 検索範囲をA列から始める
商品番号がB列にある
→ 検索範囲をB列から始める
検索範囲に目的の値が含まれているか確認する
元データに検索値があっても、VLOOKUP関数で指定した検索範囲の外にある場合は検索できません。
数式内の検索範囲を選択し、目的の値が範囲内に含まれていることを確認してください。
検索範囲を相対参照のまま数式をコピーすると、A2:C6がA3:C7、A4:C8のように移動します。
複数の行で同じ元データを検索する場合は、検索範囲を絶対参照で固定してください。
絶対参照の数式
=VLOOKUP(E2,$A$2:$C$6,3,0)数式をコピーしても検索範囲を動かさない方法は、「Excelの絶対参照・相対参照・複合参照の違い」で解説しています。
検索の型へ「0」を指定する
商品番号や社員番号など、検索値と同じデータを探す場合は、4つ目の引数「検索の型」に「0」を指定します。
「0」は完全一致を表し、検索値と同じデータを検索します。
検索の型が「0」でも、完全に一致する値がなければ「#N/A」エラーになります。
検索の型を確認した後もエラーが続く場合は、空白、全角・半角、データ型の違いを確認してください。
VLOOKUP関数の引数と基本的な検索方法は、「VLOOKUP関数の使い方」で詳しく解説しています。
元データに検索値があり、VLOOKUP関数の設定にも問題がない場合は、セルに入力された文字の内容を確認します。
次は、検索値や検索元の前後に余分な空白が含まれていないか調べましょう。
値の前後に空白がないか確認する
検索値と元データが同じように見えても、どちらかの前後に空白が含まれていると、VLOOKUP関数で一致しない場合があります。
特に、ほかのシステムやWebページからコピーしたデータには、見た目では気付きにくい空白が含まれていることがあります。
セルを編集して空白を確認する
検索値と元データのセルをそれぞれダブルクリックし、文字の前後に空白が含まれていないか確認します。
セルを選択してF2キーを押す方法でも、セル内の文字を編集できる状態に切り替えられます。
検索値 ”P001″
元データ ”P001 “
元データの「P001」の後ろには、空白が1つ含まれています。
画面上では同じ値に見えても、検索値の「P001」と元データの「P001 」は異なる文字列です。
空白の有無を目視で判断できない場合は、LEN関数で検索値と元データの文字数を比較できます。
見た目が同じなのに文字数が異なる場合は、どちらかに余分な空白や文字が含まれている可能性があります。
検索と置換で不要な空白を削除する
商品番号や社員番号など、本来空白を使用しないデータは、検索と置換を使って列内の空白をまとめて削除できます。
半角スペースと全角スペースは異なる文字として扱われるため、それぞれ分けて置換してください。
- 空白を削除してよい列を選択する
- Ctrlキーを押しながらFキーを押す
- 「検索する文字列」へ削除するスペースを入力する
- 「置換後の文字列」は空欄にする
- 「すべて置換」をクリックする
- VLOOKUP関数の「#N/A」エラーが解消されたことを確認する
検索する文字列：削除する半角スペースまたは全角スペース
置換後の文字列：何も入力しない
検索と置換を実行すると、選択範囲内にある該当のスペースがすべて削除されます。
商品名や氏名などに必要な空白が含まれている場合は、商品番号や社員番号など、空白を削除してよい列だけを選択してください。
複数のセルから特定の文字や空白をまとめて削除する方法は、「Excelの検索と置換の使い方」で詳しく解説しています。
共有ファイルなどで元データを直接修正できない場合は、空いている列へTRIM関数を入力し、前後の不要な半角スペースを削除した値を作成できます。
=TRIM(A2)TRIM関数で作成した値を使用する場合は、修正後の列をVLOOKUP関数の検索範囲の左端列に指定してください。
TRIM関数では、全角スペースやWebページなどで使用される一部の特殊な空白を削除できない場合があります。
検索と置換で不要な空白を削除してもエラーが続く場合は、入力されている文字そのものを比較します。
次は、検索値と元データで全角・半角や記号が異なっていないか確認しましょう。
全角・半角や記号の違いを確認する
検索値と元データの文字数が同じでも、全角と半角や、使用している記号が異なる場合があります。
見た目が似ている文字でもExcelでは別の文字として扱われるため、VLOOKUP関数の完全一致では検索できません。
数字
半角：123
全角：１２３
英字
半角：ABC
全角：ＡＢＣ
ハイフンと長音
半角ハイフン：-
全角ハイフン：－
長音：ー
スペース
半角スペース
全角スペース
検索と置換で入力を統一する
全角・半角や記号が混在している場合も、検索と置換を使って列内の文字を統一できます。
たとえば、全角ハイフンを検索し、半角ハイフンへ置換すれば、商品番号に使用する記号を揃えられます。
検索する文字列 －
置換後の文字列 -
修正前：ＡＢＣ１２３
修正後：ABC123英数字やカタカナを全角から半角へまとめて変換する場合は、ASC関数を使用できます。
=ASC(A2)
ただし、今回の記事では検索と置換で修正できない場合の補助的な方法として扱います。
空白、全角・半角、記号を確認しても原因が見つからない場合は、見た目では判断しにくいデータ型を調べます。
次は、TYPE関数を使って、検索値と元データが数値と文字列のどちらで入力されているか確認しましょう。
検索値と検索元のデータ型を確認する
VLOOKUP関数の設定、空白、全角・半角などを確認しても「#N/A」エラーが解消しない場合は、検索値と検索元のデータ型を調べます。
同じ「1001」と表示されていても、一方が数値、もう一方が文字列の場合は、VLOOKUP関数で一致しないことがあります。
検索値：数値の1001
元データ：文字列の”1001″
TYPE関数で数値と文字列を判定する
セルに入力された値が数値と文字列のどちらであるかは、TYPE関数で確認できます。
検索値と検索元へそれぞれTYPE関数を使用し、計算結果を比較してください。
=TYPE(E2)
結果：1→数値 2→文字列検索値の結果が「1」、元データの結果が「2」の場合は、検索値が数値、元データが文字列です。
表示されている値が同じでもデータ型が異なるため、どちらかを変換して同じデータ型へ揃えます。
セルの表示形式が「標準」や「数値」になっていても、実際の値が文字列として保存されている場合があります。
表示形式を変更しただけでは、入力済みの値のデータ型が変わらないこともあるため、TYPE関数の結果で判断してください。
検索値と検索元を同じデータ型へ揃える
TYPE関数の結果が異なる場合は、検索値または検索元を変換し、数値同士または文字列同士へ揃えます。
商品番号や社員番号など、先頭に「0」が必要なデータは、数値へ変換すると「0」が消える場合があるため注意してください。
計算に使用する数値
→数値へ変換する
商品番号や社員番号などの識別番号
→文字列へ統一する
先頭に「0」が必要な番号
→文字列へ統一する
TYPE関数でデータ型を確認する方法と、数値・文字列を同じ型へ変換する方法は、「TYPE関数でデータ型を確認する方法」で詳しく解説しています。
数値と文字列のどちらへ統一するかは、データの用途に合わせて判断します。
金額や数量など計算に使用する値は数値、商品番号や社員番号など計算しない識別番号は文字列として管理すると分かりやすくなります。
変換後にVLOOKUP関数を再計算する
検索値と検索元のデータ型を揃えたら、VLOOKUP関数の検索結果を確認します。
「#N/A」エラーが解消され、目的の値が表示されれば、データ型の違いが原因だったと判断できます。
目的の値が表示された
→ データ型の違いが原因
「#N/A」エラーが続いている
→ 空白、記号、検索範囲、元データの内容をもう一度確認する
TYPE関数で検索値と検索元のデータ型を確認し、数値または文字列へ統一すると、見た目では分からない不一致を解消できます。
最後に、「#N/A」エラーが表示されたときの確認順をまとめましょう。
まとめ｜#N/Aの原因を順番に確認しよう
VLOOKUP関数で同じ値があるように見えるのに「#N/A」エラーになる場合は、検索値を検索範囲から正しく見つけられていません。
最初に元データと数式の設定を確認し、解決しない場合は、空白、全角・半角、記号、データ型の違いを順番に調べましょう。
- オートフィルターで元データに検索値があるか確認する
- 検索値と検索範囲が正しいか確認する
- 検索値が検索範囲の左端列にあるか確認する
- 検索の型に完全一致を表す「0」が指定されているか確認する
- 検索値と元データに余分な空白がないか確認する
- 全角・半角や記号が同じか確認する
- TYPE関数で数値と文字列の違いを確認する
「#N/A」エラーは表示されないのに目的とは異なる値が表示される場合は、検索の型、列番号、元データの重複を確認してください。
検索列に同じ値が複数ある場合、VLOOKUP関数は上から検索して最初に一致したデータを返します。
商品番号や社員番号など、本来1件を識別する値が重複していると、目的とは異なる行の値が表示される可能性があるため、元データの重複を確認しましょう。
検索の型や列番号、元データの重複を含むVLOOKUP関数の基本的な注意点は、「VLOOKUP関数の使い方」で確認してください。
TYPE関数でデータ型を確認し、数値と文字列を変換する方法は、「TYPE関数でデータ型を確認する方法」で詳しく解説しています。
原因を修正した後に「#N/A」エラーを空白へ変更する場合は、「VLOOKUPとIFERRORでエラーを空白にする方法」を確認してください。
元データと数式から確認し、空白や文字、最後にデータ型を調べれば、原因を順番に絞り込めるんですね！
VLOOKUP関数でエラーが表示された場合は、数式を何度も作り直すのではなく、確認項目を1つずつ切り分けることが大切です。
関数の基本とエラーの直し方を順番に学びたい場合は、Level3攻略フローから進めてください。
コメント