こんにちは。福田泰裕です。
学校でEXCELを使って業務を行う中で、VLOOKUP関数はとても使用頻度の高い関数です。
しかし、そのVLOOKUP関数を越える検索機能をもつのが、INDEX関数とMATCH関数の組み合わせです。
今回は、INDEX関数とMATCH関数を組み合わせて自在に検索する方法をご紹介します！
最後まで読んでいただけると嬉しいです。
目次
| 範囲 | 検索するセルの範囲。 |
| 行番号 | 値を返す行番号。上から何行目かを指定する。 |
| 列番号 | 値を返す列番号。左から何列目かを指定する。 |
INDEX関数とは、ある範囲から、「上から何番目、左から何番目」と指定して抽出する関数です。
例えば下の表で、範囲「B2:F12」の上から4番目、左から2番目と指定すると…👇
「ち」と表示されます👇
これがINDEX関数の機能です。
詳しくは、こちらの記事をご覧ください👇
| 検査値 | 検査する値。 |
| 検査範囲 | 検索するセルの範囲。（1行or1列にしないとエラーとなる。） |
| 照合の種類 | 1…検査値以下の最大値。 0…完全一致。 -1…検査値以上の最小値。 （省略可。省略した場合は「1…検査値以下の最大値」となる。） |
MATCH関数は、ある範囲から、検索値が「上から何番目か」or「左から何番目か」を検査する関数です。
下の表で、セルD8にある検査値「里中 智」は、範囲「D2:D6」のうち…👇
上から3番目にあります👇
また、セルD9にある検査値「理」は、範囲「E1:I1」のうち…👇
左から4番目にあります👇
これがMATCH関数の機能です。
詳しくは、こちらの記事をご覧ください👇
それでは、INDEX関数とMATCH関数を組み合わせて自在に検索する方法をご紹介します。
まずは、表の左・上の項目を入力して、それに対応する値を抽出する方法です。
下の表を例に考えていきます。
この表から、「山田 太郎」くんの「英語」の得点を、セルE9に抽出してみましょう。
まず、INDEX関数の範囲を指定します。
検索範囲は「C1:H6」なので、
次に、「山田 太郎」がこの表の上から何番目にあるのかを入力します。
もちろん上から5番目なのですが、それをMATCH関数で検査しましょう。
検査値「山田 太郎」はセル「C9」にあり、検査範囲は「C1:C6」、照合の種類は完全一致の「0」なので、
MATCH(C9,C1:C6,0)
となります👇
次に、「英語」がこの表の左から何番目にあるのかを入力します。
検査値「英語」はセル「D9」にあり、検査範囲は「C1:H1」、照合の種類は完全一致の「0」なので、
MATCH(D9,C1:H1,0)
となります👇
ENTERを押すと…👇
見事、「山田 太郎」くんの「英語」の83点を抽出することができました！
今回の数式の流れは、このような感じです。
このようにして、表の項目からそれに該当する値を抽出することが可能です。
次は、表の中にある項目をつかって、対応する値を抽出する方法をご紹介します。
下の表を使います。
セルC9に「山田 太郎」と入力すると、残りの項目をすべて表示させたいと思います。
セルD9にINDEX関数を入力して組を抽出していきましょう。
検索範囲はA1:I6です。後でコピーすることを考えて、$A$1:$I$6と絶対参照にしておきましょう。
（※範囲を選択した後すぐF4を押せば絶対参照になります！）
次の行番号は、「山田 太郎」がこの表の上から何番目にあるのかを、MATCH関数を使って入力します。
「山田 太郎」はセルC9に入っているので、検査値は$C$9、検査範囲は$C$1:$C$6、照合の種類は完全一致の「0」とします。
（※絶対参照にすることを忘れずに！）
次の列番号は、「国語」がこの表の左から何番目にあるのかを、MATCH関数を使って入力します。
「国語」はセルD8に入っているので、検査値はD8、検査範囲は$A$1:$I$1、照合の種類は完全一致の「0」とします。
（※検査値はオートフィルの際に移動するので相対参照、検査範囲は固定なので絶対参照です。）
ENTERを押すと…👇
「山田 太郎」くんの「国語」の57点を抽出できました！
あとは残りのセルにオートフィルとコピペをすると…👇
このように、「山田 太郎」くんに関するすべての情報を得ることができました！
INDEX関数とMATCH関数で表を検索できることを紹介しましたが、
VLOOKUP関数との違いは何？
と思われた方も多いと思います。
次は、INDEX関数とMATCH関数を組み合わせてた表検索と、VLOOKUPの表検索の違いを説明します。
まず最大の違いが、VLOOKUP関数は左端からしか検索できないということです。
VLOOKUP関数を使う場合、表の中の「山田 太郎」を使って、それより左にある「組」と「番」を抜き出すことはできません。
もし同じようにVLOOKUP関数を使って「山田 太郎」を検索するならば…👇
このように左端に作業用の列を作り…👇
このようにVLOOKUP関数を使って検索しなければなりません。
INDEX関数とMATCH関数を組み合わせることで生じるデメリットは何でしょう。
ハッキリ言うと、無いです。
強いて言うなら、式が長くなるので後で訂正するのが大変だということくらいです。
| INDEXとMATCH | VLOOKUP | |
|---|---|---|
| 検索の方法 | どこからでも検索できる | 左端からのみ検索できる |
| 数式の長さ | 長い | 短い |
しかし、それは慣れてしまえばそんなに苦ではありません。
VLOOKUP関数を使うためには、検索する値を左端に置いておく必要があります。
そのため、VLOOKUP関数を使うつもりであれば最初から左端に「通し番号」を入れておくと良いです。
このように番号で管理することができれば、VLOOKUP関数の方がシンプルな数式になります。
番号で管理できるならばVLOOKUP関数、番号で管理できないならばINDEX関数とMATCH関数を使うと良いでしょう。
いかがでしたでしょうか。
INDEX関数とMATCH関数を使うことで、VLOOKUP関数よりも自由に表を検索できるようになりました。
番号で管理するのが難しいような場合は、ぜひ試しに使ってみてください。