【Excel】VLOOKUPの#N/Aを直す方法|IFNA・IFERRORで隠す前の確認点

【Excel】VLOOKUPの#N/Aを直す方法|IFNA・IFERRORで隠す前の確認点
🛡️ 超解決

VLOOKUPの#N/Aは、検索値が見つからない時などに出ます。見た目を整えるためにすぐ空白や0へ置き換えると、未登録データや参照ミスを見逃します。原因を確認してから、見つからない場合の表示方法を決めましょう。

ADVERTISEMENT

1. 検索値と範囲と完全一致を確認する

VLOOKUPは、指定した範囲の左端列で検索します。例えば=VLOOKUP(A2,$D$2:$E$100,2,FALSE)なら、A2をD列で探してE列の値を返します。第4引数のFALSEは完全一致です。省略すると近似一致になり、並び順などによって意図しない結果が出ます。

数式を下へコピーする場合は、検索表の範囲を$で固定します。検索値が入るA2まで固定すると、すべての行で同じ値を探してしまいます。式の構成が正しいかを、最初の数行と末尾で確認してください。

※ お探しの解決策が見つからない場合は、こちらの「Excelトラブル完全解決データベース」で他のエラー原因や解決策をチェックしてみてください。

2. 見た目が同じでも値の違いを調べる

数値の123と文字列の「123」、末尾に空白がある商品名などは一致しないことがあります。数式バーで確認し、必要ならLENで文字数、ISTEXTやISNUMBERで型を調べます。セルの表示形式を変更するだけでは、保存された値の型が直らない場合があります。

VLOOKUPで数値の123は検索表のペンに一致するが、文字列の123は数値の検索列と一致せずN/AになるExcelの比較
Excel 16.0の検証用セル出力。図の検索表はD2:E4です。見た目が同じ123でも、検索値と検索列の型が異なると一致しません。

TRIMは主に半角空白の整理に使えますが、すべての種類の空白を除去するわけではありません。全角空白やWeb由来の空白は別に確認します。VALUEで数値へ変換する方法も、先頭ゼロのある商品コードや長いIDへ一律に適用してはいけません。

検索表に本当に未登録なら、登録漏れなのか対象外なのかを確認します。同じ検索値が複数ある場合は先に見つかった値が返るため、エラーが出ないことだけで正しい表と判断しないでください。

3. 未登録だけを表示し直すならIFNAを使う

#N/Aだけを「未登録」に置き換える例は次のとおりです。利用中のExcelがIFNAに対応していることを確認します。

=IFNA(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"未登録")

IFERRORでも同じような表示にできますが、#REF!や#VALUE!など別のエラーもまとめて置き換えます。列を削除して参照が壊れた場合まで空白にすると、計算の不具合に気づきにくくなります。使用するなら、隠す範囲を理解した上で選びます。

未登録を0へ置き換えると、本来0円の商品や件数0と区別できなくなります。空白にした場合も、集計対象から漏れたことに気づけない場合があります。提出用の表示を整える場合は、別に未登録件数を確認できる列や一覧を残します。

4. XLOOKUPを使う時も確認は必要

対応するMicrosoft 365やExcel 2021以降などでは、次のように検索範囲と戻り範囲を分けて指定できます。XLOOKUPは既定で完全一致を使います。

=XLOOKUP(A2,$D$2:$D$100,$E$2:$E$100,"未登録")

第4引数は「見つからない場合」の値です。すべてのエラーを消す引数ではなく、範囲の不整合などは別に直します。古いExcelへ渡すブックでは対応状況も確認してください。

最後に、登録済み、未登録、空欄、先頭ゼロのあるIDなどの例で結果を確認します。未登録を隠した前後で合計だけを見るのではなく、件数と内訳も照合します。原因の詳細はMicrosoftの#N/Aの対処を参照できます。

📊
Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。
この記事の監修者
📈

超解決 Excel・Word研究班

企業のDX支援や業務効率化を専門とする技術者チーム。20年以上のExcel・Word運用改善実績に基づき、不具合の根本原因と最短の解決策を監修しています。ExcelとWordを使った「やりたいこと」「困っていること」「より便利な使い方」をクライアントの視点で丁寧に提供します。

🏆
超解決 Excel検定 あなたのExcel実務能力を3分で測定!【1級・2級・3級】