VLOOKUP関数で#REF!エラーはなぜ起こる？その原因・理由

ここでは、エクセルのVLOOKUP関数で#REF!エラーがなぜ起こるか、その原因・理由と対処法を解説します。

数式を落ち着いて見返すと、なるほどと思う理由になっています。

また、合わせてVLOOKUP関数でエラーを表示しない方法も説明します。

【目次】

本記事はアフィリエイト広告を含みます

１．列番号がセル範囲を超えて無効になっている

VLOOKUP関数が#REF!エラーになる原因は「列番号がセル範囲を超えて無効になっている」ことです。

では、例を確認し、その後対処法を見てみましょう。

まずは、例の確認です。

上の表では、A列に№、B列に都道府県が入っています。

そして、セルD1にVLOOKUP関数を使って「№が3の都道府県名」を抽出しようとしましたが、#N/Aエラーになりました。なぜでしょうか？

ここで原因を知る前に、まずはVLOOKUP関数の基本的な動きを確認しておきましょう。

【VLOOKUP関数の基本的な動き】

VLOOKUP関数は「範囲の左端から検索値と一致するものがないかを検索（①）」し、「範囲内における列番号で指定した列の値を返す（②）」関数です。

特にここでのポイントは「範囲と列番号の関係」です。

例えば、範囲を「A1:B7」、列番号を「2」とした場合にはAから2つ目がBなので「B列の値」を、範囲を「D1:F7」、列番号を「3」とした場合にはDから3つ目がFなので「F列の値」を返します。

このことを踏まえて例の数式を見てみると、範囲が「A1:B7」にもかかわらず列番号は「3」となっています。

つまり「列番号が範囲を超えている」のです。

範囲を超えた列番号を指定しているので、「範囲を超えていますよ」と#REF!エラーを出しているのです。

このケースへの対処法は「範囲を広げる」か「列番号を減らす」です。

つまり、「指定した範囲内で列番号を指定する」ようにしましょう。

例でいうと、列番号を「2」にすることでエラーを回避することができます。

これが「列番号が範囲を超えている」ことの原因と対処法です。

また、もう一つの対処法として、「範囲を広げる」という方法もあります。これは元になっている表の範囲がもともと広いときに使えます。

VLOOKUP関数で#REF!を含め、エラーを表示しないようにするには、IFERROR関数と組み合わせます。

数式の形は次の通りです。

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

IFERROR関数は、「エラーの時に返す値を指定できる関数」です。そして、上の数式では「""（空白）」を指定しているので、エラー時には「何も表示しない」状態となります。

これも合わせて覚えておくと、とても便利ですよ。

以上、参考になれば幸いです。

【口コミ・評判のいいExcel本の紹介】

リンク

VLOOKUP関数のその他の記事はこちらです。