INDEX+MATCH関数で#N/Aエラーが起こる原因と回避法
ここでは、INDEX+MATCH関数で#N/Aエラーが起こる原因と回避法を解説します。
よくあるのは、「データの型の違い」ですが、もう一つ「データの並びが悪い&照合の種類を指定していない」でも#N/Aエラーが起こるので、その説明です。
なお、その他「うまくいかない」、「おかしい」といった時の対処法は次のページで解説しています。
１．そもそも#N/Aエラーとは？
まずは、#N/Aエラーとはどんなエラーか、内部ではどのように判定されているのかから確認します。
１－１．#N/Aエラーとは
#N/Aエラーは、「数式で参照されている値が見つからなかった場合」に起こります。
MATCH関数やVLOOKUP関数では検索値を検索範囲から検索しますが、その範囲に検索値がない場合、#N/Aエラーになるのです。
１－２．検索値があるのに#N/Aエラーになる場合も
しかし実際には、検索値があるのに#N/Aエラーになることもあります。
この時に「なぜ？」となるのですが、原因は「データの型が違う」、もしくは「データの並びが悪い&照合の種類を指定していない」の主に２パターンがよくあることとして考えられます。
２．データの型が違うから#N/Aエラーになる
INDEX+MATCH関数で#N/Aエラーになる原因の一つ目は、「データの形が違う」ことが考えられます。
２－１．データの型が違うとは
データの型とは、「文字列」や「数値」などのことです。
例えば見た目上は同じ「１」でも、内部的には「文字列」と「数値」では「違う値」として扱われています。
そのため、数値の「１」で検索しても、検索範囲の値が文字列であれば、「一致する値がない」と判定されてしまうのです。
２－２．例で確認
一つ例を見てみましょう。
上の表はA列に№を、B列に値を入れたものです。そして、セルD2に検索値を入れ、セルE2にINDEX+MATCH関数を使って№が「1」の値を抽出しようとしています。
しかし結果は、#N/Aエラーとなっています。
原因は、「A列が文字列、検索値が数値」だからです。
見た目的に気づくポイントは、「文字列の数字が入っているセルには左上に『緑色の▲が付く」ところです。
緑色の▲が付いたセルをクリックすると「！」が出てきて、これをクリックすると「数値が文字列として保存されています」と警告が出ます。
このように、数字が文字列で入力されていると、「緑色の▲」で気づくことができます。
２－３．回避法は「データの型をどちらかに合わせる」
#N/Aエラーの回避法は、「データの型をどちらかに合わせる」ことです。
「文字列」か「数値」かのどちらかに合わせることによって、検索値が拾えるようにします。
そして、その方法には「手入力で合わせる」やり方と「数式で合わせる」やり方の２パターンがあります。
２－３－１．手入力で合わせる方法
手入力でデータの型を合わせるには、先ほどの警告メッセージから「数値の変換する」を選択します（数値に合わせる場合）。
後は、その他のセルにもコピーして、全て「数値」に変えます。
これで、検索範囲も検索値もデータの型が数値になり、正しく検索することができるようになります。
２－３－２．数式で合わせる方法
手入力で合わせる方法は、手間がかかります。直すセルの数がたくさんだと、とても面倒です。
そこで、数式で合わせる方法もあります。
まず、先ほどの例で#N/Aエラーになっている数式は次のようになっています。
=INDEX(A2:B5,MATCH(D2,A2:A5,0),2)
この場合、検索値が数値なので、検索範囲も関数で数値に変えます。
具体的には、MATCH関数の引数「検査範囲」にVALUE関数を使い、配列数式にします。
結果的に数式は、次のようになります。
{=INDEX(A2:B5,MATCH(D2,VALUE(A2:A5),0),2)}
配列数式にするには、数式入力後に「Ctrlキー」と「Shiftキー」を押しながら「Enterキー」を押します。
この結果、数式に「｛｝」が付いたら成功です。
VALUE関数は、「文字列を数値に変える」関数です。これで元の検査範囲の値が文字列でも、検索時には数値に変えることができ、結果正しい検索ができるようになります。
また、もう一つの方法は「検査値を文字列にする」です。
具体的には、MATCH関数の引数「検査値」にTEXT関数を使い、TEXT関数の引数「表示形式」を「"@"」にします。
結果数式は、次のようになります。
=INDEX(A2:B5,MATCH(TEXT(D2,"@"),A2:A5,0),2)
ちなみに、この方法では配列数式にする必要はありません。
元の表と検査値の関係から、簡単な方を選択するとよいでしょう。
３．データの並びと照合の種類で#N/Aエラーになる
INDEX+MATCH関数で#N/Aエラーになる原因の二つ目は、「データの並びが悪い&照合の種類を指定していない」ことが考えられます。
３－１．データの並びと照合の種類がよくないとは
「データの並びと照合の種類がよくない」とは、MATCH関数の引数「照合の種類」を省略しつつ、元データが昇順（小さい値→大きい値）に並んでいないことを言います。
そもそも照合の種類では、検索方法を指定します。「0」では完全一致、「1」や「-1」は近似値一致になり、省略すると自動で「1」が選ばれます。
「1」は、完全に一致する値がない場合に「検査値よりも一つ小さい値」を検索できますが、この関係上元データは昇順に並んでいる必要があるため、並んでいないと#N/Aエラーになることがあります。
３－２．例で確認
一つ例を見てみましょう。
上の表は、先ほどの例と似ていますが、今度はA列の値も検査値も数値となっています。
そして、セルE2でINDEX+MATCH関数を使い、検索値「1」の値を抽出しようとしていますが、結果は#N/Aエラーとなっています。
なお、セルE2に入れた数式は、
=INDEX(A2:B5,MATCH(D2,A2:A5),2)
で、照合の種類を省略しています。
このように、「照合の種類を省略しつつデータが昇順になっていない」場合には、INDEX+MATCH関数で#N/Aエラーになるのです。
３－３．回避法は「照合の種類を指定する」か「データを並び替える」
#N/Aエラーの回避法は、「照合の種類を指定する」か「データを並び替える」です。
３－３－１．照合の種類を指定する
一つ目は、「照合の種類を指定する」です。
MATCH関数の引数「照合の種類」に「0」を指定します。
例の場合数式は、
=INDEX(A2:B5,MATCH(D2,A2:A5,0),2)
となります。これだけで#N/Aエラーが解消されます。
ただし、検索方法は「完全一致」になります。
３－３－２．データを昇順に並び替える
二つ目は、「データを昇順に並び替える」です。
完全に一致する値がない場合に「検査値よりも一つ小さい値」を検索したい場合には、この方法になります。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
INDEX＋MATCH関数の、他の記事へのリンクはこちらです。
- INDEX＋MATCH関数その他の記事へのリンクはこちら