Excel(エクセル)INDEX関数とMATCH関数の組み合わせた使い方

別のシートにある表からデータを参照するINDEX関数とMATCH関数を組み合わせることによって目的のデータを表示させる方法をご紹介します。

INDEX関数の書き方

INDEX関数には２パターンの書き方があります。

1. INDEX(配列,行番号,[列番号])
2. INDEX(参照, 行番号, [列番号], [領域番号])

❷の参照設定方は使う場面があまりないので、今回は❶の配列参照の方の説明をさせていただきます。

まずこのようなリストがあるとします。「C9」セルに田中と入力してあるので、「C10」セルに居住地の「東京」を表示させるようにします。

表の範囲は「B2」セルから「E6」セルまで、田中さんは2行目で居住地は3列目にありますので以下のように記載します。

=INDEX(B2:E6,2,3)

するとこのように「C10」セルに「東京」と表示されました。

しかし当然このままでは「C9」セルを変えても「C10」セルは東京のままです。

そこでMATCH関数の登場です。

MATCH関数の書き方

MATCH関数はこのように書きます。

• MATCH(検査値,検査範囲,[照合の種類])

「照合の種類」は以上は「1」、以下は「-1」、完全一致は「0」と入力します。

ほとんどの場合完全一致しか使いませんので、「0」としてください。

行数をMATCH関数で調べる方法

まず、行数を調べる方法を見ていきます。

今回検査値は「田中」さん、検査範囲は名前のデータが入っているのは「B2」セルから「B6」セルまでですので、上の表の田中さんが何行目かを知りたい場合はこのように記載します。

=MATCH("田中",B2:B6,0)

すると、田中さんは表の中で2行目ですので「２」という結果が返ってきます。

次にこの「”田中”」という部分をセルの参照に切り替えます。

検査値の名前が入力されているのは「C9」セルですのでこのように記載します。

=MATCH(C9,B2:B6,0)

すると名前を変更すると居住地も自動で変化するようになりました。

列数をMATCH関数で調べる方法

今度は「居住地」の列数を調べる方法を見ていきます。

今回の検査値は「居住地」、検査範囲は項目名が入っている1行目の「B2」セルから「E2」セルまでですので、上の表の「居住地」が何列目か知りたい場合はこのように記載します。

=MATCH("居住地",B2:E2,0)

すると、「居住地」は表の中で３列目ですので「3」という結果が返ってきます。

次にこの「”居住地”」という部分をセルの参照に切り替えます。

検査値の「居住地」が入力されているのは「B10」ですのでこのように変更します。

=MATCH(B10,B2:E2,0)

すると「居住地」の列数である「３」という結果が返ってきます。

次にこのMATCH関数を先ほどのINDEX関数と組み合わせます。

INDEX関数とMATCH関数の組み合わせ方

先ほどのINDEX関数はこちら

=INDEX(B2:E6,2,3)

赤字の2の部分が田中さんの行数を表していますので、ここに先ほどのMATCH関数を代入していきます。

行数を調べるMATCH関数はこちらでした。

=MATCH(C9,B2:B6,0)

列数を調べるMATCH関数はこちらでした。

=MATCH(B10,B2:E2,0)

INDEX関数にこれらのMATCH関数を代入すると

=INDEX(B2:E6,MATCH(C9,B2:B6,0),MATCH(B10,B2:E2,0))

となります。

絶対参照と相対参照を組み合わせた応用

次に今回作成した関数をこのように書き換えます。

=INDEX($B$2:$E$6,MATCH($C$9,$B$2:$B$6,0),MATCH($B10,$B$2:$E$2,0))

行列それぞれの座標の前に「$」を入力するわけですが、「B10」のところだけ「$B10」として数字の前には「$」を入れていないのがポイントです。これによって、下までドラッグしたときに参照範囲が固定ではなく相対的に移動するようになります。

実際に、関数を書いた「C10」セルの右下を「C12」セルまでドラッグしてみます。

するとこのように出席番号も年齢も自動的に表示されました。

さらに、このように記述してあれば間で行挿入や列挿入がされても参照先がズレることはありません。

※黄色に塗りつぶした行と列が挿入した部分です。

「C11」セルの内容もこのように自動的に変わりました。

=INDEX($B$2:$F$7,MATCH($C$10,$B$2:$B$7,0),MATCH($B11,$B$2:$F$2,0))

ちなみにこの状況で「G」列や「８」行目より外側で行列挿入されても参照範囲は増えませんので、エラーになってしまいます。

絶対参照と相対参照についてはこちらでも詳しく説明しています。

Excel(エクセル)の絶対参照と相対参照

以上、INDEX関数とMATCH関数の使い方でした。