【教師のEXCEL講座】INDEX関数とMATCH関数~高度な検索~

教師のEXCEL講座

こんにちは。福田泰裕です。

学校でEXCELを使って業務を行う中で、VLOOKUP関数はとても使用頻度の高い関数です。
しかし、そのVLOOKUP関数を越える検索機能をもつのが、INDEX関数とMATCH関数の組み合わせです。

今回は、INDEX関数とMATCH関数を組み合わせて自在に検索する方法をご紹介します!

最後まで読んでいただけると嬉しいです。

INDEX関数とは

範囲検索するセルの範囲。
行番号値を返す行番号。上から何行目かを指定する。
列番号値を返す列番号。左から何列目かを指定する。

INDEX関数とは、ある範囲から、「上から何番目、左から何番目」と指定して抽出する関数です。

例えば下の表で、範囲「B2:F12」の上から4番目、左から2番目と指定すると…👇

「ち」と表示されます👇

index

これがINDEX関数の機能です。

詳しくは、こちらの記事をご覧ください👇

MATCH関数とは

検査値検査する値。
検査範囲検索するセルの範囲。(1行or1列にしないとエラーとなる。)
照合の種類1…検査値以下の最大値。
0…完全一致。
-1…検査値以上の最小値。
(省略可。省略した場合は「1…検査値以下の最大値」となる。)

MATCH関数は、ある範囲から、検索値が「上から何番目か」or「左から何番目か」を検査する関数です。

下の表で、セルD8にある検査値「里中 智」は、範囲「D2:D6」のうち…👇

match

上から3番目にあります👇

matich

また、セルD9にある検査値「理」は、範囲「E1:I1」のうち…👇

match

左から4番目にあります👇

match

これがMATCH関数の機能です。

詳しくは、こちらの記事をご覧ください👇

INDEX関数とMATCH関数で自在に検索する

それでは、INDEX関数とMATCH関数を組み合わせて自在に検索する方法をご紹介します。

表の左・上から値を抽出する

まずは、表の左・上の項目を入力して、それに対応する値を抽出する方法です。

下の表を例に考えていきます。

index-match

この表から、「山田 太郎」くんの「英語」の得点を、セルE9に抽出してみましょう。

まず、INDEX関数の範囲を指定します。
検索範囲は「C1:H6」なので、

index-match

次に、「山田 太郎」がこの表の上から何番目にあるのかを入力します。
もちろん上から5番目なのですが、それをMATCH関数で検査しましょう。

検査値「山田 太郎」はセル「C9」にあり、検査範囲は「C1:C6」、照合の種類は完全一致の「0」なので、
MATCH(C9,C1:C6,0)
となります👇

index-match

次に、「英語」がこの表の左から何番目にあるのかを入力します。

検査値「英語」はセル「D9」にあり、検査範囲は「C1:H1」、照合の種類は完全一致の「0」なので、
MATCH(D9,C1:H1,0)
となります👇

index-match

ENTERを押すと…👇

index-match

見事、「山田 太郎」くんの「英語」の83点を抽出することができました!

今回の数式の流れは、このような感じです。

index-match

このようにして、表の項目からそれに該当する値を抽出することが可能です。

表の中から値を抽出する

次は、表の中にある項目をつかって、対応する値を抽出する方法をご紹介します。
下の表を使います。

index-match

セルC9に「山田 太郎」と入力すると、残りの項目をすべて表示させたいと思います。

セルD9にINDEX関数を入力して組を抽出していきましょう。
検索範囲はA1:I6です。後でコピーすることを考えて、$A$1:$I$6と絶対参照にしておきましょう。
(※範囲を選択した後すぐF4を押せば絶対参照になります!)

index-match

次の行番号は、「山田 太郎」がこの表の上から何番目にあるのかを、MATCH関数を使って入力します。
「山田 太郎」はセルC9に入っているので、検査値は$C$9、検査範囲は$C$1:$C$6、照合の種類は完全一致の「0」とします。
(※絶対参照にすることを忘れずに!)

index-match

次の列番号は、「国語」がこの表の左から何番目にあるのかを、MATCH関数を使って入力します。
「国語」はセルD8に入っているので、検査値はD8、検査範囲は$A$1:$I$1、照合の種類は完全一致の「0」とします。
(※検査値はオートフィルの際に移動するので相対参照、検査範囲は固定なので絶対参照です。)

index-match

ENTERを押すと…👇

index-match

「山田 太郎」くんの「国語」の57点を抽出できました!

あとは残りのセルにオートフィルとコピペをすると…👇

index-match

このように、「山田 太郎」くんに関するすべての情報を得ることができました!

VLOOKUP関数の検索との違い

INDEX関数とMATCH関数で表を検索できることを紹介しましたが、

読者
読者

VLOOKUP関数との違いは何?

と思われた方も多いと思います。
次は、INDEX関数とMATCH関数を組み合わせてた表検索と、VLOOKUPの表検索の違いを説明します。

VLOOKUP関数は表の左端からしか検索できない

まず最大の違いが、VLOOKUP関数は左端からしか検索できないということです。

index-match

VLOOKUP関数を使う場合、表の中の「山田 太郎」を使って、それより左にある「組」と「番」を抜き出すことはできません。

もし同じようにVLOOKUP関数を使って「山田 太郎」を検索するならば…👇

vlookup
vlookup

このように左端に作業用の列を作り…👇

vlookup

このようにVLOOKUP関数を使って検索しなければなりません。

INDEX関数とMATCH関数を組み合わせるデメリットは…?

INDEX関数とMATCH関数を組み合わせることで生じるデメリットは何でしょう。

ハッキリ言うと、無いです。

強いて言うなら、式が長くなるので後で訂正するのが大変だということくらいです。

INDEXとMATCHVLOOKUP
検索の方法どこからでも検索できる左端からのみ検索できる
数式の長さ長い短い

しかし、それは慣れてしまえばそんなに苦ではありません。

VLOOKUP関数との使い分け

VLOOKUP関数を使うためには、検索する値を左端に置いておく必要があります。

そのため、VLOOKUP関数を使うつもりであれば最初から左端に「通し番号」を入れておくと良いです。

vlookup

このように番号で管理することができれば、VLOOKUP関数の方がシンプルな数式になります。

番号で管理できるならばVLOOKUP関数、番号で管理できないならばINDEX関数とMATCH関数を使うと良いでしょう。

まとめ:INDEX関数とMATCH関数の検索は便利

いかがでしたでしょうか。

INDEX関数とMATCH関数を使うことで、VLOOKUP関数よりも自由に表を検索できるようになりました。
番号で管理するのが難しいような場合は、ぜひ試しに使ってみてください。

最後まで読んでいただき、ありがとうございました。

質問やご意見、ご感想などがあればコメント欄にお願いします👇

コメント

タイトルとURLをコピーしました