ExcelのVLOOKUP関数のエラーの対処法をお探しですね。
広告
VLOOKUPの「#N/A」エラーを空白にする方法【IFERROR関数で解決】
ExcelでVLOOKUP関数を使っていると、検索値が見つからないときに「#N/A」というエラーが表示されますよね。
集計表や見積書、顧客リストなどで、この「#N/A」がずらっと並んでいると、表が見づらくなって印刷や共有のときにも困ってしまいます。
この記事では、VLOOKUPの「#N/A」エラーを表示させず、**IFERROR関数を使って空白にする方法**をわかりやすく解説します。
基本の数式はもちろん、使うときの注意点や、空白以外の表示にする方法、エラーの原因を確認するポイントまでまとめて紹介しますね。
VLOOKUPで「#N/A」が出てしまう理由
VLOOKUPの「#N/A」は、検索した値が参照先の表に見つからなかったときに出るエラーです。
たとえば、商品コードをもとに商品名を取り出そうとしたとき、入力した商品コードが一覧表に存在しなければ、Excelは「該当するデータがありません」という意味で「#N/A」を返します。
つまり「#N/A」はExcelの不具合ではなくて、VLOOKUPがちゃんと検索した結果、「見つからなかった」ことを知らせてくれているサインなんです。
VLOOKUPの基本の書き方
VLOOKUPの基本形は、次のように書きます。
“`
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
“`
たとえば、A2セルの商品コードをもとに、D列からF列にある一覧表の2列目を取り出す場合は、こんな数式になります。
“`
=VLOOKUP(A2,$D$2:$F$100,2,FALSE)
“`
最後の「FALSE」は、完全一致で検索するという指定です。
商品コードや社員番号、管理番号のように、ぴったり一致する値を探したいときは、基本的に「FALSE」を使います。
ここで検索値が一覧表に存在しなければ、結果は「#N/A」になるわけです。
見た目は同じなのに「#N/A」になることも
ただし、見た目では同じ値に見えても「#N/A」になってしまうケースがあります。
たとえば、
– 片方は**数値**の「1001」、もう片方は**文字列**の「1001」になっている
– セルの末尾に**余分なスペース**が入っている
こんな場合、Excel上では同じように見えても、VLOOKUPでは別の値として扱われるため、検索に失敗してしまいます。
なので、エラーを非表示にする前に、「本当にデータがないのか」「データの形式がずれているのか」を確認することも大切です。
IFERRORでVLOOKUPのエラーを空白にする基本の式
VLOOKUPの「#N/A」を表示させない一番簡単な方法は、**IFERROR関数でVLOOKUPを包む**ことです。
IFERROR関数は、数式がエラーになったときに、指定した別の値を表示できる関数です。
VLOOKUPでエラーが出たときだけ空白にしたい場合は、次のように書きます。
“`
=IFERROR(VLOOKUP(A2,$D$2:$F$100,2,FALSE),””)
“`
この数式では、まずVLOOKUPでA2の値を検索します。
該当するデータが見つかれば、普通に結果を表示します。
一方、検索値が見つからず「#N/A」などのエラーになった場合は、最後の `””` が表示されます。
`””` は「空文字」と呼ばれるもので、見た目上は空白に見える値です。
具体例で見てみましょう
A列に商品コード、B列に商品名を表示したい表があるとします。
商品マスタがD列からE列にあって、D列に商品コード、E列に商品名が入っている場合、B2セルにはこう入力します。
“`
=IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),””)
“`
この式を下の行へコピーすれば、A列に入力された商品コードに応じて商品名が表示されます。
もしA列が未入力だったり、商品マスタに存在しないコードが入力されたりしても、B列には「#N/A」ではなく**空白**が表示されます。
表全体がすっきり見えるので、入力用のシートや印刷用の帳票では特に便利ですよ。
IFERRORの便利なところと注意点
IFERRORは「#N/A」だけでなく、「#VALUE!」「#REF!」「#DIV/0!」など、ほかのエラーもまとめて処理してくれます。
そのため、数式の見た目を整える目的では、とても使いやすい関数です。
ただし、**すべてのエラーを空白にしてしまう**ため、参照範囲の指定ミスや列番号の間違いまで見えなくなる可能性があります。
作成途中の表では、まずVLOOKUP単体で正しく動くことを確認してから、最後にIFERRORを追加すると安心です。
空白以外を表示する方法とIFNAとの使い分け
IFERRORでは、エラーのときに空白だけでなく、好きな文字を表示することもできます。
たとえば、該当データがないことをわかりやすく示したい場合は、こんな風に書きます。
“`
=IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),”未登録”)
“`
この式では、VLOOKUPで該当データが見つからないときに「未登録」と表示されます。
入力担当者に確認を促したい場合や、商品コードの登録漏れを見つけたい場合は、完全に空白にするよりも「未登録」「該当なし」「確認」などの文字を表示したほうが便利ですね。
一方、見積書や請求書のように、見た目をきれいに整えることが優先される書類では、空白にする方法が向いています。
よく使われる表示パターン
– **空白にする**：`=IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),””)`
– **「該当なし」と表示する**：`=IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),”該当なし”)`
– **0を表示する**：`=IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),0)`
ここで注意したいのは、空白に見える `””` と、数値の `0` は別物だという点です。
金額や数量の計算に使う列では、空白にすると後続の計算や集計で意図しない結果になることがあります。
たとえば、未登録の商品価格を空白にした場合、見た目は自然でも、あとから合計や平均を出すときに扱いを確認する必要があります。
数値として計算したい場合は、空白ではなく**0を返す**ほうが適しているケースもあります。
IFNA関数という選択肢もあります
また、ExcelにはIFNA関数というものもあります。
IFNAは「#N/A」エラー**だけ**を処理する関数で、その他のエラーはそのまま表示します。
VLOOKUPで「検索値が見つからない場合だけ空白にしたい」のであれば、こんな風にIFNAを使うこともできます。
“`
=IFNA(VLOOKUP(A2,$D$2:$E$100,2,FALSE),””)
“`
IFERRORは幅広いエラーをまとめて隠せるので便利ですが、数式ミスまで見えなくなることがあります。
一方、IFNAは「#N/A」だけを処理するので、参照範囲の指定ミスなど別のエラーには気づきやすくなります。
初心者のうちはIFERRORで十分ですが、業務用の重要な表では、目的に応じてIFNAも検討してみるといいでしょう。
IFERRORを使うときの注意点とエラーを減らすコツ
IFERRORでVLOOKUPのエラーを空白にすると、表は見やすくなります。
でも、**エラーが見えなくなる**ということは、**データの不備にも気づきにくくなる**ということでもあります。
特に、商品マスタや顧客マスタなど、参照元のデータが正しいことを前提にした表では、単にエラーを隠すだけでなく、原因を確認できる状態にしておくことが大切です。
まず確認したいのはデータの形式
まず確認したいのは、**検索値と参照先のデータ形式**です。
同じ「001」と表示されていても、片方が文字列、もう片方が数値になっていると、VLOOKUPで一致しない場合があります。
また、コピーしたデータに余分な半角スペースや全角スペースが混ざっていると、見た目ではわかりにくいエラーの原因になります。
こんなときは、**TRIM関数**で余分なスペースを取り除いたり、表示形式やデータ型をそろえたりすると改善できます。
VLOOKUPの参照範囲にも注意
次に、VLOOKUPの参照範囲にも注意が必要です。
VLOOKUPは、**参照範囲の一番左の列から検索**します。
たとえば、商品コードで検索したいのに、参照範囲の左端が商品名になっていると、正しく検索できません。
また、列番号の指定を間違えると、意図しない列の値を返したり、範囲外を指定してエラーになったりします。
VLOOKUPでエラーを減らすチェックポイント
– 検索値が参照表の**左端列**に存在しているか
– 検索値と参照先の**データ形式**がそろっているか
– 余分なスペースや表記ゆれがないか
– 参照範囲をコピーしてもずれないように**絶対参照**にしているか
– 完全一致で検索する場合、最後の引数を**FALSE**にしているか
特に、参照範囲には `$D$2:$E$100` のようにドル記号を付けて**絶対参照**にするのがおすすめです。
絶対参照にしておくと、数式を下方向や横方向にコピーしても参照範囲がずれません。
範囲がずれると、最初の行では正しく表示されていたのに、下の行では「#N/A」や別の結果が出ることがあります。
IFERRORで空白にしていると、この範囲ずれに気づきにくいので、最初に数式の参照先を確認しておきましょう。
新しいExcelならXLOOKUPも選択肢に
なお、Microsoft 365やExcel 2021以降を使っている場合は、**XLOOKUP関数**を使う選択肢もあります。
XLOOKUPには、見つからない場合の表示を指定できる引数があるので、VLOOKUPよりもわかりやすく書ける場面があります。
ただし、古いExcelでも使える互換性を重視するなら、VLOOKUPとIFERRORの組み合わせは今でも十分実用的です。
社内で複数のExcelバージョンが混在している場合は、VLOOKUP＋IFERRORで作成しておくと共有しやすくなりますよ。
まとめ
VLOOKUPの「#N/A」を表示させない基本は、`=IFERROR(VLOOKUP(…),””)` という形です。
空白にすれば表は見やすくなって、入力欄が未完成の状態でも余計なエラー表示を避けられます。
ただし、エラーを隠すだけでは根本的な原因がわからなくなることもあるので、作成段階ではVLOOKUP単体で動作を確認して、仕上げとしてIFERRORを追加する流れが安全です。
目的に合わせて、
– 空白
– 「未登録」などの文字
– 0
– IFNA
などを使い分ければ、見やすさと正確性の両方を保ったExcel表を作れますよ。
広告
