【教師のEXCEL講座】INDEX関数とMATCH関数～高度な検索～ | 高校教師とICTのブログ[数学×情報×ICT]

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

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

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

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

INDEX関数とは

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

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

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

「ち」と表示されます👇

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

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

MATCH関数とは

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

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

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

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

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

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

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

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

INDEX関数と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」とします。
（※検査値はオートフィルの際に移動するので相対参照、検査範囲は固定なので絶対参照です。）