【Excel 関数】VLOOKUP 関数より優れた INDEX × MATCH 関数の使い方
janjf93 / Pixabay

はじめに

VLOOKUP 関数はデータ抽出を行う際に非常に便利な関数ですが、

ある2つの関数を組み合わせることで、VLOOKUP関数と同等の(もしくはそれ以上の)動作を実現できます。

その2つの関数とは、INDEX関数とMATCH関数です。

このページでは、INDEX関数とMATCH関数を組み合わせたデータ抽出方法をご紹介します。

なお VLOOKUP関数について詳しく知りたい方は下記リンクをどうぞ。

スポンサーリンク

MATCH 関数と INDEX 関数

まず、MATCH関数とINDEX関数について説明します。そして、その2つを組み合わせを用いたデータ抽出方法をご紹介します。

MATCH 関数

指定したキーワードがデータの何行目にあるかを調べる関数です。

MATCH関数の書式

MATCH関数の書式
=MATCH(検査値,検査範囲,照合の種類)
検査値

指定するキーワードのことです。

検査範囲

キーワードを検索するデータの範囲のことです。

照合の種類

下記の値を指定します。文字検索として MATCH関数を使用する場合、「0」を指定します。

  • 1 → 以下
  • 0 → 完全一致
  • -1 → 以上

具体例

例えば、下記画面のようなデータがあるとし、名前が「山田」のデータが何行目にあるかを調べます。

まず、検査値として「山田」のセル(E2)とし、検査範囲を下記赤枠のデータ範囲(A1:A6)、照合の種類を完全一致(0)とします。

そうすると、下記左側のデータでは4行目に「山田」のデータが存在するため、「4」という値になります。

INDEX 関数

あるデータ範囲の指定した行と列番号の値を調べる関数です。

INDEX関数の書式

INDEX関数の書式
=INDEX(配列,行番号,列番号)
配列

調べるデータ範囲のことです。

行番号 & 列番号

データ範囲から調べたいデータの行番号と列番号を指定します。

※ 【配列】(調べるデータ範囲)が1列のみの場合は、列番号は省略可能です。

具体例

例えば、下記画面のようなデータの4行2列にあるデータを調べたいとする場合は下記のようにします。

データ範囲(青色の枠)の4行2列目のデータは、「156」となります。

MATCH × INDEX 関数

もう一度おさらいしますと、

  • MATCH 関数 → あるデータが何行目にあるかがわかる
  • INDEX 関数   → あるデータ範囲の指定した行番号と列番号のデータがわかる

これを組み合わせると、MATCH関数であるデータの行番号がわかり、INDEX関数でその行番号の別の列のデータがわかるんです。

つまりこれは、VLOOKUP関数と同じ動作(あるデータから別の列のデータを取得する)です。

下記画面は【氏名】が「山田」の【身長】のデータを抽出する場合の例です。

MATCH × INDEX 関数の書式

MATCH × INDEX 関数の書式
=INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

少し難しく見えるかもしれませんが、要は INDEX 関数の行番号の部分を MATCH 関数に置き換えただけです。

INDEX関数は下記の書式でした。

=INDEX(配列,行番号,列番号)

これの行番号にMATCH関数の書式を加えただけです。

=INDEX(配列,行番号,列番号) → =INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

具体例

では下記のようなデータがあり、【氏名】が「山田」の【身長】はいくつかを調べます。

(1)まずは「山田」が何行目にあるかを調べます。これは MATCH 関数でさきほどと同様に記述し、4行目にあるとわかります。

(2)次に INDEX 関数を追加します。INDEX関数の行番号を指定する箇所にMATCH関数をそのまま貼り付ければOKです。

INDEX × MATCH関数を書きなれていない方は、まず先にMATCH関数を書いてからINDEX関数を書くことをお勧めします!

※  INDEX × MATCH関数 は検索した値がない場合は「0」が返されます。もし「0」ではなく空欄で返したい場合は下記リンクを参考にしてください。

VLOOKUP 関数との違い

① MATCH×INDEX関数には、検索キーワードを1列目にする制約がない!

下記ページにも記載していますが、VLOOKUP関数では、検索キーワードを1列目にする制約があります。

その制約が MATCH× INDEX 関数にはなく、優れた関数といえます。

➁ 膨大なデータの場合、若干 MATCH×INDEX関数のほうが速い!

これは、私の仕事の同僚(システムエンジニアとして20年勤めているベテラン)の聞いた話ですが、

いままでは6件ほどの少ないデータを例に挙げていましたが、何十万件でのデータとなると VLOOKUP関数よりも処理が速くなるそうです。

最後に

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

MATCH 関数と INDEX 関数は、2つの関数をただ組み合わせるだけで VLOOKUP関数と同等かそれ以上の優れた関数になります。

もちろん VLOOKUP 関数で書けば1つの関数で処理が終わるというメリットもあります。

しかし、VLOOKUP関数を使っていると、エラーになってうまく動作しないということがあると思います。

そういう場合はぜひ MATCH×INDEX関数を使ってみてください。

スポンサーリンク
おすすめの記事