INDEX関数とMATCH関数で#REF!エラーが起きる原因
ここでは、ExcelのINDEX関数とMATCH関数の組み合わせで#REF!エラーになる原因と回避方法を解説します。
「配列の範囲を超えて指定してしまっている」場合と、「外部参照時のパスの指定の仕方がまずい」場合の２通りに対する説明です。
１．まずは問題点を確認
まずは、問題点を例を含めて確認します。
１－１．INDEX+MATCH関数でREF!になる
問題点は、「INDEX+MATCH関数で#REF!エラーになる」ことです。
いつも通り数式を作ったはずなのに、なぜか今回は#REF!エラーになる、原因が分からないというケースです。
原因を突き止め、対処法を確認します。
１－２．例で確認
ひとつ例を見てみましょう。
上の表は社員の基本情報の一覧です。この表から№1の人のデータを抜き出すため、セルB10にINDEX関数＋MATCH関数の数式を入れましたが、#REF!エラーになっています。
数式は、
=INDEX($A$4:$F$8,MATCH($A4,$A$4:$A$8,0),7)
です。
ざっと見た感じはおかしくない数式ですが、どこが悪いのでしょうか？
２．#REF!エラーの原因と対処・回避方法
次に、#REF!エラーになる原因と対処法です。
２－１．#REF!エラーの原因は
#REF!エラーになっている原因は「INDEX関数で指定した列番号が配列の範囲を超えている」ことです。
では、INDEX関数の引数「列番号」に注目して見てみましょう。
先ほどの数式の「列番号」は７になっています。
これは「配列で指定した範囲の左から７番目の列の値を返しなさい」という意味になります。
では次に、INDEX関数の引数「配列」を見てみましょう。
「配列」はA～Fの６列が指定されています。
配列は全部で6列なのに、列番号は7列目…。
つまり「７番目の列は配列の範囲にはない」ということで、#REF!エラーとなっているのです。
２－２
．#REF!エラー
への対処法
原因が分かれば対処・回避するのは簡単です。
原因は「列番号が配列の範囲を超えている」ことだったので、対処法は「配列の範囲を修正する」か「列番号を修正する」とよいのです。
ちなみに、上の例では「列番号」を「2」に直してみました。
これで#REF!エラーを回避することができます。
３．範囲を超えて列番号を指定するのはなぜか？
ではなぜ、配列の範囲を超えて列番号を指定してしまうのでしょうか？
それは「配列の列番号表記が英字（A：Cなど）に対して列番号は数字のため」です。
例えば配列が「D1:Z10」の時、７番目の列はなにかすぐには分かりませんよね。
そのため、つい間違ってしまうのです。
これは仕様なので仕方ありませんが、間違えやすいので注意しなければならないポイントとして覚えておきましょう。
４．パスがIPアドレスだと#REF!エラーになる
最後にもう一つ、INDEX＋MATCH関数で#REF!エラーになる原因と対処法が分かったので載せておきます。
４－１．IPアドレスで外部参照している
これはINDEX＋MATCH関数に限った話ではないのですが、エクセルは「IPアドレスで外部参照している」と、#REF!エラーになります。
そもそもエクセルは、他のファイルを参照させると自動でパスを付加してくれます。
そしてどのようなパスになったかは、「数式を入れたファイル」と「参照先のファイル」をどちらも閉じて、「数式を入れたファイル」を開き直すと数式バーで確認できます。
この時、「¥¥192.168.～¥フォルダ名.～」などとなっているとアウトです。
「数式を入れたファイル」の後に「参照先のファイル」を開くと、シート名が全て#REF!になります。
４－２．対処法は「パスをコンピュータ名に直す」
【口コミ・評判のいいExcel本の紹介】