VLOOKUPで検索値が見つからない時に#N/Aを出さない方法

By /

VLOOKUP関数で「#N/A」エラーが出たときに慌てない！ 見つからない検索値を上手に処理する徹底解説

Microsoft Excelでデータ集計や管理を行う上で、VLOOKUP関数は非常に強力で頻繁に利用される機能の一つです。膨大なデータの中から特定の条件に合致する情報を取り出す際に、VLOOKUPは欠かせません。しかし、VLOOKUPを使っていると、誰もが一度は目にするであろうエラーがあります。それが「#N/A」エラーです。

「#N/A」は”Not Available”（利用できません）の略で、VLOOKUP関数が指定された検索値を見つけられなかった場合に表示されます。このエラーが表示されると、せっかく作成した表が見づらくなったり、そのエラーを参照している別の計算が影響を受けたりすることがあります。

この記事では、VLOOKUP関数で発生する#N/Aエラーの原因を深掘りし、そして最も重要な点として、この#N/Aエラーを意図しない形で表示させないための様々な方法について、約5000語にわたり徹底的に詳細に解説します。簡単な方法から応用的なテクニックまで、それぞれのメリット・デメリット、具体的な数式例、そしてどのような状況でどの方法を選ぶべきかまでを網羅します。この記事を読み終える頃には、VLOOKUPの#N/Aエラーに冷静に対応し、より洗練された、エラーの少ないExcelシートを作成できるようになっているでしょう。

1. はじめに：VLOOKUPと#N/Aエラーの関係性

VLOOKUP関数は、指定した範囲の左端の列で特定の値を検索し、その値が見つかった行の、指定した列にある値を取り出す関数です。例えば、商品コードを検索して、その商品名や価格を取得するような場面でよく使われます。

基本的な書式は以下の通りです。

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

  • 検索値: 検索する値です。セル参照や直接入力した値になります。
  • 範囲: 検索を行うセル範囲です。検索値が含まれる列が必ずこの範囲の左端の列である必要があります。
  • 列番号: 範囲の左端の列を1として数え、戻り値として取り出したい値がある列の番号を指定します。
  • 検索方法: 検索値と完全に一致する値を探すか (FALSEまたは0)、あるいは近似値を探すか (TRUEまたは1) を指定します。通常はFALSE（完全一致）を指定することがほとんどです。

このVLOOKUP関数が「#N/A」エラーを返すのは、主に以下の状況です。

  1. 検索値が、指定した範囲の左端の列の中に見つからない場合。
  2. 検索方法にFALSE（完全一致）を指定したが、検索値と完全に一致する値が存在しない場合。
  3. 検索方法にTRUE（近似一致）を指定したが、範囲の左端の列が昇順に並べ替えられていない場合。
  4. 指定した範囲が正しくない、あるいは列番号が範囲の列数を超えている場合（ただし、後者の場合は#REF!エラーになることが多い）。
  5. 検索値や範囲内のデータに、目に見えないスペースや異なるデータ型（数値が文字列として保存されているなど）が含まれているために、一致と認識されない場合。

これらの原因の中で最も頻繁に遭遇し、かつエラーハンドリングの対象となるのが、「検索値が見つからない」というケースです。この記事では、この「検索値が見つからなかった」ために表示される#N/Aエラーを、数式を使って上手に処理する方法を詳細に解説していきます。

2. VLOOKUP関数の基本的な使い方と#N/Aが発生するメカニズム

N/Aエラーの回避策に入る前に、VLOOKUP関数の基本的な動きと、なぜ#N/Aが発生するのかをもう少し具体的に見ていきましょう。

2.1 VLOOKUP関数の引数の詳細

  • 検索値 (lookup_value):

    • 例: A2 (A2セルの値), "りんご" (文字列), 101 (数値)
    • VLOOKUPが「探しに行く」対象です。通常は別のセルを参照します。
    • 検索値が存在しない可能性がある場合に#N/Aエラーが発生します。

  • 範囲 (table_array):

    • 例: B2:D100 (B2からD100の範囲), 商品マスタ!A:C (別シートの商品マスタのA列からC列全体)
    • VLOOKUPが検索値を探す場所です。最も重要なのは、この範囲の左端の列に検索値が含まれている必要があるという点です。
    • この範囲が間違っている（例: 検索値がある列が左端でない）、または検索値が含まれるべき列が範囲に含まれていない場合、検索値が見つからず#N/Aエラーの原因となります。
    • 数式をコピーする際に範囲がずれないように、通常は絶対参照（例: $B$2:$D$100, 商品マスタ!$A:$C）を使うことが多いです。絶対参照を忘れることも、エラーの原因になることがあります。

  • 列番号 (col_index_num):

    • 例: 2 (範囲の左から2番目の列), 3 (範囲の左から3番目の列)
    • 検索値が見つかった行から、何列目の値を取り出すかを指定します。範囲の左端の列が1です。
    • この番号が、指定した範囲の列数より大きい場合、#REF!エラーとなります（これは#N/Aとは異なりますが、エラーの一種です）。

  • 検索方法 (range_lookup):

    • 例: FALSE または 0 (完全一致), TRUE または 1 (近似一致)
    • FALSE（完全一致）: 検索値と完全に同じ値を探します。一致する値が複数ある場合は、範囲の上から見て最初に見つかった行の値を返します。一致する値が一つも見つからない場合は#N/Aを返します。
    • TRUE（近似一致）: 検索値以下の最大の値を探します。この場合、範囲の左端の列が必ず昇順に並べ替えられている必要があります。 並べ替えられていない場合、正しい結果が得られないか、#N/Aを返すことがあります。数値範囲の階級分け（例: 点数によって評価を返す）などに使用されます。文字列や日付の場合も同様に昇順である必要があります。通常、正確な情報を参照したい場合はFALSEを指定します。

2.2 #N/Aエラーが発生する具体的なメカニズム

以下のデータがあるとします。

シート1 (集計シート)

A列 B列 C列
1 商品コード 2 商品名 3 価格
2 101
3 105
4 103
5 109
6 102
7 107
8 104

シート2 (商品マスタ)

A列 B列 C列
1 商品コード 2 商品名 3 価格
2 101 りんご 150
3 102 バナナ 100
4 103 みかん 120
5 104 ぶどう 250

シート1のB列に商品マスタから商品名を取得したいとします。B2セルに以下の数式を入力します。

=VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE)

  • 検索値: A2 (101)
  • 範囲: Sheet2!$A$2:$C$5 (商品マスタのA2からC5)
  • 列番号: 2 (範囲の2列目、つまり商品名)
  • 検索方法: FALSE (完全一致)

この数式をB列の他のセルにもコピーします。

  • B2 (=VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE)) -> A2の値101はSheet2のA列に見つかるので、「りんご」が返されます。
  • B3 (=VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE)) -> A3の値105はSheet2のA列に見つかりません。 -> #N/A が返されます。
  • B4 (=VLOOKUP(A4, Sheet2!$A$2:$C$5, 2, FALSE)) -> A4の値103はSheet2のA列に見つかるので、「みかん」が返されます。
  • B5 (=VLOOKUP(A5, Sheet2!$A$2:$C$5, 2, FALSE)) -> A5の値109はSheet2のA列に見つかりません。 -> #N/A が返されます。
  • B6 (=VLOOKUP(A6, Sheet2!$A$2:$C$5, 2, FALSE)) -> A6の値102はSheet2のA列に見つかるので、「バナナ」が返されます。
  • B7 (=VLOOKUP(A7, Sheet2!$A$2:$C$5, 2, FALSE)) -> A7の値107はSheet2のA列に見つかりません。 -> #N/A が返されます。
  • B8 (=VLOOKUP(A8, Sheet2!$A$2:$C$5, 2, FALSE)) -> A8の値104はSheet2のA列に見つかるので、「ぶどう」が返されます。

このように、シート1のA列にある商品コードが、シート2の商品マスタのA列に存在しない場合に#N/Aエラーが発生します。これは意図した動作（「その商品コードはマスタに登録されていません」という意味）ではありますが、表示上はエラーとなってしまいます。

3. #N/Aエラーを回避・抑制する様々な方法

ここからが本題です。前述のように発生した#N/Aエラーを、数式の工夫によって回避したり、別の表示に置き換えたりする方法を具体的に解説します。主に以下の方法があります。

  1. IFERROR関数を使う: 最も簡単でよく使われる方法。
  2. IFNA関数を使う: IFERRORと似ているが、#N/Aエラーのみを対象とする方法。
  3. IF関数とCOUNTIF関数を組み合わせる: VLOOKUPを実行する前に検索値の存在を確認する方法。
  4. IF関数とISNA/ISERROR関数を組み合わせる: VLOOKUPの結果がエラーかどうかを判定する方法。

それぞれの方法を詳しく見ていきましょう。

3.1 方法1: IFERROR関数を使う（最も一般的）

IFERROR関数は、指定した数式を評価し、その結果が何らかのエラー（#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!）であった場合に、指定した値を返す関数です。エラーでなかった場合は、数式の結果をそのまま返します。

IFERROR関数の書式は以下の通りです。

=IFERROR(値, エラーの場合の値)

  • 値: エラーかどうかを判定したい数式、あるいはセル参照です。ここにVLOOKUP関数をそのまま入れます。
  • エラーの場合の値: 値がエラーであった場合に表示したい値です。””（空白文字列）、0、特定の文字列（例: “該当なし”）、または別の数式などを指定できます。

先ほどの例（B3セルで商品コード105を検索し#N/Aになったケース）にIFERROR関数を適用してみましょう。

元の数式: =VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE) (結果: #N/A)

IFERROR関数を使った数式: =IFERROR(VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE), "該当なし")

この数式は以下のように動作します。

  1. まず、VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE) が評価されます。
  2. A3の値105はSheet2のA列に見つからないため、VLOOKUPは #N/A を返します。
  3. IFERROR関数は、最初の引数 (VLOOKUP(...)) の結果がエラーであると判定します。
  4. エラーであるため、IFERROR関数は2番目の引数 "該当なし" を返します。

結果として、B3セルには「該当なし」と表示されます。他のセルにもこのIFERRORを使った数式をコピーしてみましょう。

  • B2 (=IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), "該当なし")) -> VLOOKUPは「りんご」を返す -> エラーではないのでIFERRORは「りんご」をそのまま返す。
  • B3 (=IFERROR(VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE), "該当なし")) -> VLOOKUPは#N/Aを返す -> エラーなのでIFERRORは「該当なし」を返す。
  • B5 (=IFERROR(VLOOKUP(A5, Sheet2!$A$2:$C$5, 2, FALSE), "該当なし")) -> VLOOKUPは#N/Aを返す -> エラーなのでIFERRORは「該当なし」を返す。
  • B7 (=IFERROR(VLOOKUP(A7, Sheet2!$A$2:$C$5, 2, FALSE), "該当なし")) -> VLOOKUPは#N/Aを返す -> エラーなのでIFERRORは「該当なし」を返す。

このように、VLOOKUPの結果がエラーの行だけ、「該当なし」と表示されるようになります。

IFERRORのメリット:

  • 非常にシンプル: 既存のVLOOKUP数式を=IFERROR(既存の数式, エラーの場合の値) の形に囲むだけで使えます。
  • 幅広いエラーに対応: #N/Aだけでなく、#VALUE!や#REF!など、VLOOKUP以外の原因で発生する可能性のある様々な種類のエラーもまとめて処理できます。これはメリットであると同時に、意図しないエラー（例: 列番号の指定ミスによる#REF!）まで隠してしまう可能性があるというデメリットにもなり得ます。

IFERRORのデメリット:

  • エラーの種類を区別できない: 前述のように、あらゆる種類のエラーに対して同じ処理を行います。特定の原因（#N/A）で発生したエラーのみを処理したい場合には、不向きな場合があります。
  • Excelのバージョン: Excel 2007以降で利用可能な関数です。それ以前のバージョンでは使えません。

IFERRORを使う場合の「エラーの場合の値」の指定:

  • 空白を表示したい場合: "" (ダブルクォーテーション2つ) を指定します。これが最も一般的な使い方かもしれません。数式: =IFERROR(VLOOKUP(...), "")
  • 0を表示したい場合: 0 を指定します。数値として扱いたい場合に便利です。数式: =IFERROR(VLOOKUP(...), 0)
  • 特定の文字列を表示したい場合: "該当なし", "データなし", "Not Found" など、説明的な文字列をダブルクォーテーションで囲んで指定します。数式: =IFERROR(VLOOKUP(...), "該当なし")
  • 別のセルを参照したい場合: 例: A1 数式: =IFERROR(VLOOKUP(...), A1)
  • 別の計算を実行したい場合: 例: SUM(C1:C5) 数式: =IFERROR(VLOOKUP(...), SUM(C1:C5)) （ただし、複雑な数式をここに含めると可読性が低下します）

ほとんどの場合、"" (空白) または "該当なし" のように特定の文字列を指定することが多いでしょう。

3.2 方法2: IFNA関数を使う（#N/Aエラーのみを対象）

IFNA関数は、IFERROR関数と非常によく似ていますが、処理の対象を#N/Aエラーのみに限定している点が異なります。

IFNA関数の書式は以下の通りです。

=IFNA(値, #N/Aの場合の値)

  • 値: #N/Aエラーかどうかを判定したい数式、あるいはセル参照です。ここにVLOOKUP関数をそのまま入れます。
  • #N/Aの場合の値: 値が#N/Aエラーであった場合に表示したい値です。IFERRORと同様に""、0、特定の文字列などを指定できます。

先ほどの例にIFNA関数を適用してみましょう。

元の数式: =VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE) (結果: #N/A)

IFNA関数を使った数式: =IFNA(VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE), "データがありません")

この数式は以下のように動作します。

  1. まず、VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE) が評価されます。
  2. VLOOKUPは #N/A を返します。
  3. IFNA関数は、最初の引数 (VLOOKUP(...)) の結果が#N/Aエラーであると判定します。

  4. N/Aエラーであるため、IFNA関数は2番目の引数 "データがありません" を返します。

結果として、B3セルには「データがありません」と表示されます。

では、もしVLOOKUPの数式が間違っていて、例えば列番号を範囲の列数より大きい値（例: 4）に指定してしまったとします。この場合、VLOOKUPは#REF!エラーを返します。

=VLOOKUP(A2, Sheet2!$A$2:$C$5, 4, FALSE) (結果: #REF!)

この数式にIFNAを適用した場合:

=IFNA(VLOOKUP(A2, Sheet2!$A$2:$C$5, 4, FALSE), "データがありません")

  1. VLOOKUP(A2, Sheet2!$A$2:$C$5, 4, FALSE) が評価されます。
  2. 列番号が範囲を超えているため、VLOOKUPは #REF! を返します。
  3. IFNA関数は、最初の引数の結果が#N/Aエラーではないと判定します（この場合は#REF!エラー）。

  4. N/Aエラーではないため、IFNA関数はエラーをそのまま返します。

結果として、セルには#REF!が表示されたままになります。

IFNAのメリット:

  • #N/Aエラーのみを処理: VLOOKUPで最も頻繁に起こる「検索値が見つからない」という状況に特化してエラーを処理できます。これにより、数式の間違い（例: #REF!）など、#N/A以外の重要なエラーを見落としにくくなります。
  • シンプル: IFERRORと同様に、元の数式をIFNAで囲むだけで使えます。

IFNAのデメリット:

  • #N/A以外のエラーはそのまま表示される: これはメリットでもありますが、#N/A以外のエラーもまとめて処理したい場合には、IFERRORの方が適しています。
  • Excelのバージョン: Excel 2013以降で利用可能な関数です。IFERRORよりさらに新しいバージョンが必要です。

IFERRORとIFNAの選び方:

  • VLOOKUPの「検索値が見つからない」ことだけを処理したい場合: IFNA関数が推奨されます。他のエラーを見つけやすくするためです。
  • VLOOKUPの結果が何らかのエラーになった場合に、まとめて処理したい場合（例: VLOOKUPの結果を使った別の計算で#VALUE!などが発生する場合を含む）: IFERROR関数が便利です。
  • 古いExcelバージョン（Excel 2007〜2010）との互換性が必要な場合: IFERROR関数を使用する必要があります。
  • Excel 2013以降を使用しており、#N/Aだけを処理したい場合: IFNA関数を使用しましょう。

3.3 方法3: IF関数とCOUNTIF関数を組み合わせる（検索値の存在を事前に確認）

この方法は、VLOOKUPを実行する前に、検索値が範囲内に存在するかどうかを判定するというアプローチです。COUNTIF関数を使って、検索値が範囲の左端の列にいくつ存在するかを数え、その結果に応じてIF関数で処理を分岐させます。

COUNTIF関数の書式は以下の通りです。

=COUNTIF(範囲, 検索条件)

  • 範囲: 条件を満たすセルを数える範囲です。VLOOKUPで検索値を探す列（範囲の左端の列）を指定します。
  • 検索条件: 数えるための条件です。VLOOKUPの検索値を指定します。

COUNTIFを使ったVLOOKUPのエラー回避数式は以下の構造になります。

=IF(COUNTIF(検索列, 検索値)>0, VLOOKUP(検索値, 範囲, 列番号, FALSE), エラーの場合の値)

  • COUNTIF(検索列, 検索値)>0: これは論理テストです。VLOOKUPの検索値が、VLOOKUPの検索範囲の左端の列（検索列）に1つでも存在するかどうかを判定します。COUNTIFの結果が0より大きければ（つまり1つ以上存在すれば）TRUE、0であれば（つまり存在しなければ）FALSEとなります。
  • VLOOKUP(検索値, 範囲, 列番号, FALSE): 論理テストがTRUEの場合（検索値が存在する場合）に実行される処理です。ここで通常のVLOOKUP関数を実行し、値を取得します。
  • エラーの場合の値: 論理テストがFALSEの場合（検索値が存在しない場合）に表示したい値です。#N/Aを回避するためにここに指定します。"", "該当なし", 0などを指定できます。

先ほどの例（B3セルで商品コード105を検索し#N/Aになったケース）にIFとCOUNTIFを適用してみましょう。

VLOOKUPの検索値: A3 (105)
VLOOKUPの範囲: Sheet2!$A$2:$C$5
VLOOKUPの検索列（範囲の左端の列）: Sheet2!$A$2:$A$5

数式: =IF(COUNTIF(Sheet2!$A$2:$A$5, A3)>0, VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE), "見つからず")

この数式は以下のように動作します。

  1. まず、COUNTIF(Sheet2!$A$2:$A$5, A3) が評価されます。A3の値105はSheet2のA列には存在しないため、COUNTIFの結果は0となります。
  2. IF関数の論理テスト 0 > 0 はFALSEとなります。
  3. 論理テストがFALSEであるため、IF関数は3番目の引数 "見つからず" を返します。

結果として、B3セルには「見つからず」と表示されます。

他のセルにもこの数式をコピーしてみましょう。

  • B2 (=IF(COUNTIF(Sheet2!$A$2:$A$5, A2)>0, VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), "見つからず")) -> COUNTIFの結果は1 (A2の値101はSheet2のA列に存在)。論理テスト 1 > 0 はTRUE。VLOOKUPが実行され「りんご」が返される。
  • B3 (=IF(COUNTIF(Sheet2!$A$2:$A$5, A3)>0, VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE), "見つからず")) -> COUNTIFの結果は0 (A3の値105は存在しない)。論理テスト 0 > 0 はFALSE。「見つからず」が返される。
  • B5 (=IF(COUNTIF(Sheet2!$A$2:$A$5, A5)>0, VLOOKUP(Sheet2!$A$2:$A$5, A5, 2, FALSE), "見つからず")) -> COUNTIFの結果は0。「見つからず」が返される。

IFとCOUNTIFを組み合わせるメリット:

  • #N/Aエラーの原因が「検索値の不在」である場合に特化して処理できる: IFERRORやIFNAのように、VLOOKUPの結果がエラーになったら、という後処理ではなく、VLOOKUPを実行する前に検索値が存在するかどうかを判定するという、より原因に踏み込んだ処理が可能です。
  • 古いExcelバージョンでも利用可能: IFERRORやIFNAが登場する以前から使われている方法であり、ほぼ全てのExcelバージョンで利用できます。
  • 条件分岐の柔軟性: 検索値が存在する場合と存在しない場合で、全く異なる処理をさせることが可能です（例: 存在しない場合は空白、存在するがVLOOKUPで別のエラーが出た場合はそのエラーをそのまま表示するなど、IFERROR/IFNAより細かい制御が可能）。

IFとCOUNTIFを組み合わせるデメリット:

  • 数式が複雑になる: VLOOKUP関数を2回書く（IFの引数とCOUNTIFの引数）、あるいはCOUNTIFの範囲をVLOOKUPの範囲の左端列に正確に合わせる必要があるなど、数式の構造がIFERROR/IFNAに比べて複雑になります。
  • 計算効率: COUNTIFで検索値の存在をチェックし、さらにVLOOKUPでもう一度同じ範囲を検索するという二段階の処理を行うため、非常に大きなデータ範囲に対して使用すると、計算に時間がかかる可能性があります。
  • VLOOKUPで発生する#N/A以外のエラー（例: #REF!）は処理できない: この方法はあくまで「検索値が見つからない」ことによる#N/Aエラーにのみ有効です。VLOOKUP自体の引数ミスなどによる他のエラーは、VLOOKUPが実行される部分で発生し、そのまま表示されます。

補足: COUNTIFの範囲について

COUNTIF(検索列, 検索値) の 検索列 は、VLOOKUP(検索値, 範囲, ...) の 範囲 の左端の列と完全に一致させる必要があります。例えば、VLOOKUPの範囲が Sheet2!$A$2:$C$5 であれば、COUNTIFの範囲は Sheet2!$A$2:$A$5 となります。もしVLOOKUPの範囲が Sheet2!$A:$C のように列全体を指定している場合は、COUNTIFの範囲も Sheet2!$A:$A または Sheet2!A:A となります。範囲の指定ミスは、予期しない結果やエラーの原因となるため注意が必要です。

3.4 方法4: IF関数とISNA/ISERROR関数を組み合わせる（結果がエラーか判定）

この方法は、IFERRORやIFNAが登場する前に、IF関数とエラー判定関数（ISNAやISERROR）を組み合わせてエラーハンドリングを行っていた方法です。VLOOKUPを実行し、その結果がエラー（特に#N/A）であれば別の値を表示するというロジックになります。

  • ISNA関数: 指定した値が#N/Aエラーであるかどうかを判定します。#N/AならTRUE、それ以外のエラーや値ならFALSEを返します。
  • ISERROR関数: 指定した値が何らかのエラー（#N/A, #VALUE!, #REF!, など全て）であるかどうかを判定します。エラーならTRUE、それ以外の値ならFALSEを返します。

ISNA関数を使ったVLOOKUPのエラー回避数式は以下の構造になります。

=IF(ISNA(VLOOKUP(検索値, 範囲, 列番号, FALSE)), エラーの場合の値, VLOOKUP(検索値, 範囲, 列番号, FALSE))

  • ISNA(VLOOKUP(...)): VLOOKUP関数の結果が#N/Aであるかどうかを判定します。#N/AならTRUE、#N/A以外ならFALSE。
  • エラーの場合の値: 論理テストがTRUEの場合（VLOOKUPの結果が#N/Aの場合）に表示したい値です。
  • VLOOKUP(検索値, 範囲, 列番号, FALSE): 論理テストがFALSEの場合（VLOOKUPの結果が#N/Aではない場合、つまり検索値が見つかったか、#N/A以外のエラーが出た場合）に実行される処理です。ここでは再度同じVLOOKUPを実行し、本来の結果を取得します。

ISERROR関数を使う場合は、ISNAの部分をISERRORに置き換えるだけです。

=IF(ISERROR(VLOOKUP(検索値, 範囲, 列番号, FALSE)), エラーの場合の値, VLOOKUP(検索値, 範囲, 列番号, FALSE))

先ほどの例（B3セルで商品コード105を検索し#N/Aになったケース）にIFとISNAを適用してみましょう。

数式: =IF(ISNA(VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE)), "該当データなし", VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE))

この数式は以下のように動作します。

  1. IF関数の論理テスト ISNA(VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE)) が評価されます。
  2. まず内部の VLOOKUP(A3, Sheet2!$A$2:$C$5, 2, FALSE) が評価され、#N/Aを返します。
  3. ISNA(#N/A) はTRUEを返します。
  4. 論理テストがTRUEであるため、IF関数は2番目の引数 "該当データなし" を返します。

結果として、B3セルには「該当データなし」と表示されます。

他のセルにもこの数式をコピーしてみましょう。

  • B2 (=IF(ISNA(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE)), "該当データなし", VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE))) -> 内部のVLOOKUPは「りんご」を返す。ISNA("りんご") はFALSE。論理テストがFALSEなので、IF関数は3番目の引数、つまりもう一度実行したVLOOKUPの結果「りんご」を返す。

IFとISNA/ISERRORを組み合わせるメリット:

  • 古いExcelバージョンでも利用可能: IFERRORやIFNAが登場する以前から利用されており、幅広いバージョンで使えます。
  • エラーの種類を限定できる (ISNAの場合): #N/Aエラーのみを処理したい場合にISNAを使用できます。

IFとISNA/ISERRORを組み合わせるデメリット:

  • 数式が複雑になる: VLOOKUP関数を数式中に2回書く必要があるため、数式が長くなり、修正や理解が難しくなります。
  • 計算効率が悪い: 検索値が見つかった場合でも、VLOOKUP関数を2回実行するため、計算に時間がかかる可能性があります。これは特にデータ量が多い場合に顕著になります。
  • IFERROR/IFNAの登場により、多くのケースでIFERROR/IFNAの方が推奨される。

計算効率の観点から、この方法はIFERRORやIFNAが利用できるバージョンであれば、あまり積極的に選択する必要はないと言えます。しかし、古いバージョンとの互換性が必要な場合や、あえてこの構造を利用したい特定の場合には有効です。

4. 各エラー回避方法の比較と選び方

ここまで4つの主要な方法を解説しました。改めてそれぞれの特徴を比較し、どのような状況でどの方法を選ぶべきかを整理します。

方法 数式の構造 対象エラー シンプルさ 計算効率 Excelバージョン メリット デメリット
IFERROR関数 =IFERROR(VLOOKUP(...), 値) 全てのエラー 高 良い 2007以降 シンプルで記述しやすい。あらゆるエラーをまとめて処理できる。 #N/A以外のエラーも隠してしまう。エラーの種類を区別できない。2007未満では使えない。
IFNA関数 =IFNA(VLOOKUP(...), 値) #N/Aのみ 高 良い 2013以降 シンプル。#N/A以外のエラーを見つけやすい。 #N/A以外のエラーは処理できない。2013未満では使えない。
IFとCOUNTIF関数 =IF(COUNTIF(検索列, 検索値)>0, VLOOKUP(...), 値) 検索値の不在(#N/A) 中 やや悪い ほぼ全て VLOOKUP実行前に条件判定。#N/Aの原因(不在)に特化できる。古いバージョンでも使える。 数式がやや複雑。VLOOKUPを2回評価するわけではないが、COUNTIFとVLOOKUPの2つの処理が必要。
IFとISNA/ISERROR関数 =IF(ISNA/ISERROR(VLOOKUP(...)), 値, VLOOKUP(...)) ISNA: #N/Aのみ
ISERROR: 全て		 低 悪い ほぼ全て 古いバージョンでも使える。ISNAで#N/Aに限定できる。 数式が複雑。VLOOKUPを2回評価するため効率が悪い。

選び方のポイント:

  1. 使用しているExcelのバージョン:

    • Excel 2013以降: IFNAまたはIFERRORが最も推奨されます。#N/Aエラーだけを処理したいならIFNA、あらゆるエラーをまとめて処理したいならIFERRORを選びましょう。
    • Excel 2007～2010: IFERROR関数が使えます。これが最もシンプルで効率的な方法になります。
    • Excel 2003以前: IF関数とCOUNTIF関数、またはIF関数とISNA/ISERROR関数を組み合わせる方法を使います。IFとCOUNTIFの方が、通常はIFとISNA/ISERRORより（VLOOKUPが実行される回数が最大1回なので）効率が良いことが多いです。

  2. 処理したいエラーの種類:

    • VLOOKUPで発生しうるすべてのエラー（#N/A, #VALUE!, #REF!など）をまとめて処理したい場合は、IFERROR関数（または古いバージョンならIFとISERROR）が適しています。
    • VLOOKUPの「検索値が見つからない」ことによる#N/Aエラーのみを処理したい場合は、IFNA関数（または古いバージョンならIFとISNA、あるいはIFとCOUNTIF）が適しています。特に他の原因によるエラー（例: 数式の記述ミス）はそのまま表示させて発見したいという場合は、IFNAが便利です。

  3. 数式の複雑さや可読性:

    • IFERRORやIFNAは非常にシンプルで、既存のVLOOKUP数式に簡単に適用できます。数式が短く分かりやすいです。
    • IFとCOUNTIF、IFとISNA/ISERRORは数式が長くなり、特にIFとISNA/ISERRORはVLOOKUPを2回記述するため、メンテナンス性がやや低下します。

  4. 計算効率:

    • IFERRORやIFNAは、内部でVLOOKUPを一度だけ評価するため、効率が良いです。
    • IFとCOUNTIFは、COUNTIFとVLOOKUPの2つの処理を行いますが、COUNTIFは通常高速です。ただし、同じ範囲を2回参照することになります。
    • IFとISNA/ISERRORは、検索値が見つかった場合にVLOOKUPを2回実行するため、最も効率が悪くなる可能性があります。

推奨される順番:

  1. Excel 2013以降: IFNA を第一候補に検討。#N/A以外のエラーもまとめて処理したい場合は IFERROR。
  2. Excel 2007～2010: IFERROR を使用。
  3. Excel 2003以前: IF(COUNTIF(...)...) または IF(ISNA(...)...) を使用。データの量にもよるが、COUNTIFの方が効率が良い可能性がある。

ほとんどの現代的なExcel環境では、IFERROR関数またはIFNA関数を使用するのが、シンプルさ、可読性、計算効率のバランスが取れており、最も推奨される方法と言えます。

5. 実践的な応用例：#N/Aをどのように置き換えるか

IFERRORやIFNA関数の2つ目の引数（エラーの場合の値）には、様々な値を指定できます。具体的な応用例を見ていきましょう。

5.1 #N/Aを空白で表示する ("")

エラーセルを非表示にしたい場合、空白文字列""を指定します。これは最もよく使われる方法です。

数式例: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), "")
または: =IFNA(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), "")

これにより、検索値が見つからなかったセルは見た目上空白になり、表全体がすっきりします。空白のセルは、SUM関数などで合計する際も影響しません（数値として扱われないため）。

5.2 #N/Aを特定の文字列で表示する ("該当なし", "見つかりません")

エラーが発生した理由をユーザーに分かりやすく伝えたい場合、説明的な文字列を指定します。

数式例: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), "該当なし")
または: =IFNA(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), "商品コード不明")

これにより、どのデータが見つからなかったのかが明確になります。この文字列が表示されているセルは、数値計算ではエラーとなる可能性があるため注意が必要です（例: このセルを参照してSUM関数を実行すると#VALUE!になるなど）。

5.3 #N/Aを数値のゼロで表示する (0)

検索結果が数値であり、見つからない場合はゼロとして扱いたい場合（例: 在庫数を検索して、見つからない商品は在庫ゼロと見なす）、数値の0を指定します。

数式例: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$5, 3, FALSE), 0) (3列目は価格の例)
または: =IFNA(VLOOKUP(A2, Sheet2!$A$2:$C$5, 3, FALSE), 0)

これにより、そのセルを参照した他の計算（例: 合計や平均など）がエラーにならずに済みます。

5.4 #N/Aの場合に別のセルや別の計算を参照する

エラーの場合に表示する値を、固定値ではなく別のセルの値にしたり、別の計算結果にしたりすることも可能です。

数式例: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), B1) (B1セルの値を表示)
数式例: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE), "エラーコード: "&C2) (文字列と別のセルの値を結合)

これはあまり一般的ではありませんが、エラー処理のロジックをより複雑にしたい場合に利用できます。

5.5 #N/Aエラーが発生したセルを条件付き書式で強調表示する

これはエラーを「回避する」のではなく、「見つけやすくする」方法ですが、エラーハンドリングの文脈で役立ちます。IFERRORやIFNAでエラー表示を抑制した場合でも、元のVLOOKUP数式自体は#N/Aを返すロジックを持っています。この#N/Aエラーが発生しているかどうかを条件付き書式で判定し、セルの色を変えるなどで視覚的に分かりやすくすることができます。

手順（例: B列でVLOOKUPの結果#N/Aになるセルを黄色にする）:

  1. エラー表示を抑制したVLOOKUP数式が入っているセル範囲を選択します（例: B2:B100）。
  2. [ホーム]タブの[条件付き書式] -> [新しいルール] をクリックします。
  3. ルールの種類で「数式を使用して、書式設定するセルを決定」を選択します。
  4. 「次の数式を満たす場合に値を書式設定」のボックスに、元のVLOOKUP数式が#N/Aになるかどうかを判定する数式を入力します。IFNA関数やISNA関数を利用できます。
    • =ISNA(VLOOKUP(A2, Sheet2!$A$2:$C$5, 2, FALSE)) (これはB2セルに対するルールなので、セル参照は絶対参照にしない)
    • または、Excel 2013以降なら =ISNA(B2) のように、そのセル自体が#N/Aエラーを返しているかを判定する方がシンプルです。ただし、これはIFERRORやIFNAでエラー表示を抑制していない場合に有効な判定方法です。もしIFERRORやIFNAで""や"該当なし"に置き換えている場合は、その置き換えられた値ではなく、元の数式が#N/Aだったかどうかを判定する必要があります。
    • IFERRORで""に置き換えている場合は、元の数式が#N/Aかどうかを判定するには、例えば =$B2="" かつ ISNA(VLOOKUP($A2, Sheet2!$A$2:$C$5, 2, FALSE)) のように、少し複雑な条件が必要になる場合があります。あるいは、IFERROR/IFNAでエラーを空白にした後、別の列にISNA(元のVLOOKUP数式) の列を作成し、その列を条件付き書式の参照元にするという方法も考えられます。
    • 最も簡単なのは、IFERROR/IFNAでエラーを抑制せずに条件付き書式でエラーセルをハイライトし、その上でIFERROR/IFNAを適用してエラー表示を置き換える、という二段階で考えることです。条件付き書式でエラーを判定するルールとしては、選択範囲の最初のセル（例: B2）について =ISNA(B2) または =ISERROR(B2) とするのがシンプルです。
  5. [書式]ボタンをクリックし、フォントの色や塗りつぶしの色など、強調したい書式を設定します。
  6. [OK]をクリックしてルールを適用します。

この方法により、表面上はエラーが表示されていなくても、どのセルでエラーが発生しているのかを簡単に把握できます。

6. #N/Aエラーの根本原因を取り除く方法（予防策）

エラーを回避・抑制することも重要ですが、そもそも#N/Aエラーが発生する原因を取り除くこと、つまり予防策を講じることも非常に重要です。多くの場合、#N/Aエラーはデータ入力の不備やシート間の参照のずれによって発生します。

6.1 データの前処理とクレンジング

  • 不要なスペースの削除: VLOOKUPで最もよくある#N/Aエラーの原因の一つが、検索値や参照範囲のデータに目に見えないスペースが含まれていることです。例えば、「りんご」と「りんご 」はVLOOKUPでは一致と見なされません。
    • TRIM関数: 文字列の先頭や末尾にある不要な半角スペースを削除します。全角スペースは削除しません。
    • CLEAN関数: 印刷できない制御文字を削除します。
    • FIND/SUBSTITUTE関数や ASC/JIS 関数: 全角スペースやタブなどを削除・変換するのに使えます。
    • VLOOKUPの検索値や参照範囲のデータに対して、これらの関数を適用した列を用意するか、あるいはVLOOKUPの数式内でTRIM関数などをネストして使用することで対応できます。例: =VLOOKUP(TRIM(A2), Sheet2!$A$2:$C$5, 2, FALSE) または、商品マスタ側の検索キー列にTRIM関数などでクレンジングした補助列を作成し、その列をVLOOKUPの検索列として利用する。
  • 全角/半角の統一: 半角カタカナと全角カタカナ、半角英数字と全角英数字、あるいはハイフンなどの記号の全角/半角の違いも、VLOOKUPでは一致と見なされません。
    • JIS関数: 全角（ひらがな、カタカナ、英数字、記号）を半角に変換します。
    • ASC関数: 半角（カタカナ、英数字、一部記号）を全角に変換します。
    • これらの関数を使って、検索値や参照範囲のデータを統一します。例: =VLOOKUP(JIS(A2), Sheet2!$A$2:$C$5, 2, FALSE) または、マスタ側を変換しておく。
  • 書式の統一: セルの表示形式だけでなく、内部的なデータ型が異なる場合も問題になります。例えば、数値として入力された「101」と、文字列として入力された「’101」（アポストロフィ付きや、文字列として書式設定後に101と入力した場合など）は一致と見なされないことがあります。
    • TEXT関数やVALUE関数: 数値を特定の書式に変換したり、文字列を数値に変換したりできます。
    • 区切り位置機能: 「区切り位置」機能を使って、文字列として入力された数値をまとめて数値型に変換することができます。
    • 書式設定を確認し、必要に応じてデータ型を統一する処理を行います。

これらのデータクレンジングは、VLOOKUPを使う前に、参照元となるマスタデータや、検索値が含まれるデータを整形しておくのが理想的です。

6.2 検索範囲の適切な指定と絶対参照 ($)

VLOOKUPの範囲引数が正しく指定されているか確認します。特に数式を下や右にコピーする場合、範囲がずれてしまうと、検索値が含まれるべき列が範囲から外れたり、範囲自体がおかしくなったりして#N/Aの原因となります。

  • Sheet2!$A$2:$C$5 のように、範囲を絶対参照で指定することが非常に重要です。列全体を参照する場合も $A:$C または A:C のように絶対参照が推奨されます（コピーしてもA列からC列のままになります）。
  • VLOOKUPの検索値は必ず指定した範囲の左端の列に含まれていなければなりません。

6.3 データ型の不一致の確認

前述の書式の統一とも関連しますが、検索値のセルと、参照範囲の検索列のセルのデータ型（数値、文字列、日付など）が一致しているか確認します。見た目が同じでも、内部的に異なると一致と見なされません。セルを選択してリボンの[ホーム]タブにある表示形式を確認したり、ISTEXT関数やISNUMBER関数などで判定したりできます。

6.4 近似一致 (TRUE) の場合の並べ替え

VLOOKUPの検索方法にTRUEを指定して近似一致を行う場合は、範囲の左端の列が必ず昇順で並べ替えられている必要があります。 並べ替えられていない場合、誤った値が返されたり、#N/Aエラーが発生したりします。近似一致を使用する際は、検索列を事前に昇順に並べ替えることを忘れないようにしましょう。

6.5 検索値が本当に存在するかの確認

当たり前のことですが、そもそも検索したい値が参照範囲のマスタデータに存在しないのであれば、#N/Aエラーは避けられません。これはエラーではなく「見つからなかった」という正しい結果です。この正しい結果としての#N/Aを、空白や特定の文字列に置き換えたい場合に、これまで解説したIFERRORなどのエラー回避関数が役立ちます。

検索値が存在するはずなのに#N/Aが出る場合は、前述のスペース、全角半角、データ型、あるいは単純な入力ミス（例: 101を10lと入力しているなど）が原因である可能性が高いです。

7. Excelの新機能：XLOOKUPによるエラー回避

比較的新しいバージョンのExcel (Microsoft 365, Excel 2021など) では、VLOOKUP関数の後継ともいえるXLOOKUP関数が導入されています。XLOOKUP関数はVLOOKUPのいくつかの欠点を解消しており、その中にエラーハンドリングを容易にする機能も含まれています。

XLOOKUP関数の基本的な書式（簡易版）は以下の通りです。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

  • 検索値: VLOOKUPと同様、検索する値。
  • 検索範囲: VLOOKUPと異なり、検索値を探す列だけを指定します。範囲の左端である必要はありません。
  • 戻り範囲: VLOOKUPと異なり、戻り値を取り出したい列だけを指定します。列番号ではなく、具体的な列を指定します。
  • [見つからない場合] (省略可能): 検索値が見つからなかった場合に表示したい値を指定できます。この引数がXLOOKUPの大きな特徴であり、VLOOKUPの#N/Aエラーハンドリングを大幅に簡略化します。指定しない場合はVLOOKUPと同様に#N/Aが返されます。
  • [一致モード] (省略可能): VLOOKUPの検索方法に相当しますが、より多くのオプションがあります。0 (完全一致、デフォルト), -1 (近似一致 – より小さい値), 1 (近似一致 – より大きい値), 2 (ワイルドカード一致)。
  • [検索モード] (省略可能): 検索の方向などを指定できます。1 (先頭から末尾へ、デフォルト), -1 (末尾から先頭へ), 2 (昇順にソート済みと仮定して二分探索), -2 (降順にソート済みと仮定して二分探索)。

XLOOKUPで#N/Aエラーを回避するには、第4引数の[見つからない場合]を指定するだけです。

例: =XLOOKUP(A2, Sheet2!$A$2:$A$5, Sheet2!$B$2:$B$5, "データなし")

この数式は以下のように動作します。

  1. A2の値 (101) を Sheet2!$A$2:$A$5 の範囲で検索します。
  2. 101が見つかった行の、対応するSheet2!$B$2:$B$5 の値 (Sheet2!B2 の「りんご」) を返します。
  3. もしA2の値 (例: 105) が Sheet2!$A$2:$A$5 に見つからなかった場合は、第4引数の "データなし" を返します。

XLOOKUPは、VLOOKUPの「範囲」「列番号」といった概念がなくなり、「検索する場所」「結果を取り出す場所」をそれぞれ独立して指定できるため、数式が直感的で理解しやすくなっています。また、デフォルトが完全一致であり、近似一致の場合もVLOOKUPのように検索範囲を事前に並べ替えておく必要がありません（検索モードを2または-2にする場合は必要）。

そして何より、エラー処理のためにIFERRORやIFNAで囲む必要がなく、関数自体の引数として組み込まれているため、非常にシンプルに#N/Aエラーを回避できます。

もしお使いのExcelのバージョンがXLOOKUPに対応しているなら、今後はVLOOKUPよりもXLOOKUPを利用することを検討することをおすすめします。エラーハンドリングだけでなく、機能面でもVLOOKUPより優れています。

8. まとめ：最適なエラー処理を選び、データ活用を効率化する

この記事では、ExcelのVLOOKUP関数を使用する際に発生する#N/Aエラーについて、その原因と、エラーを回避・抑制するための様々な方法を詳細に解説しました。

主なエラー回避方法として、以下の4つを挙げました。

  1. IFERROR関数: シンプルで汎用性が高い。ほとんどのエラーをまとめて処理できる。Excel 2007以降で使用可能。
  2. IFNA関数: #N/Aエラーのみを対象とする。他のエラーを見つけやすい。Excel 2013以降で使用可能。
  3. IF関数とCOUNTIF関数: VLOOKUP実行前に検索値の存在を判定する。古いバージョンでも使用可能。
  4. IF関数とISNA/ISERROR関数: VLOOKUPの結果をエラー判定する。古いバージョンでも使用可能だが、効率は悪い場合がある。

どの方法を選ぶべきかは、主にExcelのバージョン、処理したいエラーの種類、そして数式のシンプルさや計算効率といった要素によって決まります。現代のExcel環境では、シンプルさと効率を兼ね備えたIFERROR関数またはIFNA関数を使用するのが最も一般的で推奨される方法です。特に#N/Aエラーのみを厳密に処理したい場合はIFNAが優れています。

また、エラー回避の数式を適用するだけでなく、#N/Aエラーの根本的な原因（データの不整合、スペース、書式、参照範囲のずれなど）を取り除くためのデータクレンジングや数式作成時の注意点（絶対参照、並べ替えなど）も非常に重要であることを解説しました。予防策と回避策の両方を適切に講じることで、より堅牢で信頼性の高いExcelシートを作成できます。

さらに、最新のExcelではXLOOKUP関数が登場し、エラーハンドリングを含むVLOOKUPの課題の多くが解決されています。対応バージョンをお使いであれば、積極的にXLOOKUPへの移行を検討する価値は十分にあります。

VLOOKUPの#N/Aエラーは一見厄介に感じますが、適切な数式やデータ管理の知識があれば、恐れる必要はありません。この記事で解説した内容を参考に、ご自身のデータやExcelのバージョン、目的に合った最適な方法を選び、効率的かつ正確なデータ集計・分析を実現してください。

エラーを上手に処理することで、見た目が分かりやすい報告書を作成したり、エラーによる計算の停止を防いだり、データの信頼性を高めたりすることができます。ぜひこれらのテクニックを活用して、Excelスキルを次のレベルへと引き上げてください。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール