INDEX+MATCH関数がうまくいかない、おかしい時の対処法
ここでは、エクセルの「INDEX+MATCH関数がうまくいかない、おかしい時の対処法」を解説します。
返ってくる値が思っていたのと違う、検索値がないのに値が返ってくるなどの原因と対処法です。
１．返ってくる値が全部ズレる場合
まずは、「返ってくる値が全部ズレる場合」からです。
１－１．現象を確認
一つ例を見てみましょう。
上の表から、セルF2にINDEX+MATCH関数を使って「№2の都道府県名」を取り出そうとしています。
検査値は、セルD2の「№2」です。
しかし結果は、「№3の岩手県」が返ってきました。
ちなみに、検索値を「3」にすると「№4の秋田県」が返ってきます。
このように、INDEX+MATCH関数で「返ってくる値が全部ズレる」ことがあります。
１－２．原因は
「返ってくる値が全部ズレる」原因は、「INDEX関数の『配列』とMATCH関数の『検査範囲』の『行番号』がずれている」からです。
上の数式は、先ほどの例のセルF2に入れていたものです。
見ると、赤字のところの「INDEX関数の『配列』」と「MATCH関数の『検査範囲』」の行範囲がずれているのが分かります。
それぞれの関数の動きを、もうちょっと詳しく見てみましょう。
まず、MATCH関数の検査範囲は「A1:A7」で、検査値は「3」なので、MATCH関数は「4」を返します。
一方、INDEX関数の配列は「A2:C7」で、列番号は「2」なので、INDEX関数は「秋田県」を返すのです。
つまり、「MATCH関数の『検査範囲』が1行目から始まっているのに、INDEX関数の『配列』が2行目から始まってる」ためズレるです。
このように、INDEX関数の「配列」とMATCH関数の「検査範囲」の行番号がずれていると、INDEX+MATCH関数の返す値は「全部ズレる」のです。
１－３．対処法は
対処法は、「INDEX関数の『配列』とMATCH関数の『検査範囲』の行番号を合わせる」ことです。
INDEX関数の配列が「A2:C7」、MATCH関数の検査範囲が「A2:A7」と、同じ「2行目から7行目」になっているのがわかると思います。
このように、INDEX+MATCH関数で正しい値を返させるためには、「行番号を合わせる」ことが必要なのです。
２．値が規則性なくズレる場合
次に、「返ってくる値が規則性なくズレる場合」です。
２－１．現象を確認
一つ例を見てみましょう。
上の表から、セルF2にINDEX+MATCH関数を使って「青森県の県庁所在地」を取り出そうとしています。
検査値は、セルD2の「青森県」です。
しかし結果は、「仙台市」と表示されてしまいました。
ちなみに、同じ数式で他の都道府県を検査値にしてみると、合ったり合わなかったりします。
このように、INDEX+MATCH関数で「正しくない値」が返ってくることがあります。
２－２．原因は
「返ってくる値が規則性なくズレる」原因は、「MATCH関数の『照合の種類』が省略されている」からです。
上の数式は、先ほどの例のセルF2に入れていたものです。
見ると、「照合の種類」の指定がないのが分かります。
２－３．対処法は
対処法は、「MATCH関数の『照合の種類』に『0』を入れる」ことです。
照合の種類とは、「検索方法を指定する」ところで、「0」は「完全一致」での検索、省略すると「近似値一致」での検索となります。
そして、文字列を条件に近似値一致で検索をすると、正しくない値が返ってくるのです。
このように、INDEX+MATCH関数で正しい値を返させるためには、「MATCH関数の『照合の種類』に『0』を入れる」ことが必要なのです。
３．検査値がないのに値が返ってくる場合
最後に、「検査値がないのに値が返ってくる場合」です。
３－１．現象を確認
一つ例を見てみましょう。
上の表から、セルF2にINDEX+MATCH関数を使って「埼玉県の県庁所在地」を取り出そうとしています。
検査値は、セルD2の「埼玉県」です。
結果、元の表には「埼玉県」がないにもかかわらず「仙台市」と表示されてしまいました。
このように、INDEX+MATCH関数で「検査値がないのに値が表示される」ことがあります。
３－２．原因は
「検査値がないのに値が返ってくる原因」も、「MATCH関数の『照合の種類』を省略してしまっている」からです。
上の数式は、先ほどの例のセルF2に入れていたものです。
見ると、「照合の種類」の指定がないのが分かります。
３－３．対処法は
対処法は、「MATCH関数の『照合の種類』に『0』を入れる」ことです。
このように、INDEX+MATCH関数で正しい値を返させるためには、やはり「MATCH関数の『照合の種類』に『0』を入れる」ことが必要なのです。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
INDEX＋MATCH関数の、他の記事へのリンクはこちらです。
- INDEX＋MATCH関数その他の記事へのリンクはこちら