INDEX関数とMATCH関数を組み合わせて、VLOOKUPの欠点を克服!

VLOOKUP関数の欠点を乗り越えよ
スポンサーリンク

「絶対に覚えるべし!」と言われている、VLOOKUP関数

しかし、VLOOKUP関数には2つの欠点があります(詳細は後述)。

  1. 列番号を数えるのが面倒
  2. 検索先の列(キー)が左側に無いと使えない

 

INDEX関数MATCH関数を組み合わせて、これらの欠点を克服しましょう。

エク先輩
INDEX,MATCH関数を組み合わるデータ取得方法は、VLOOKUP関数より「簡単」で「汎用的」なんだ!
是非、覚えておこう。

VLOOKUP関数の「2つの欠点」

①列番号を数えるのが面倒

VLOOKUPの欠点1:列番号を数えるのが面倒
C3セルに入力している関数は、以下の通り。
=VLOOKUP(B3,$E$3:$F$5,2,FALSE)
①検索値=B3(商品ID”ID001″)
②範囲=$E$3:$F$5(商品マスタ)
③列番号=2(商品マスタの”商品名”列)
④検索方法=FALSE(完全一致)

上記の場合、指定した「②範囲」が2列であるため、列番号を数えるのは簡単です。

しかし、商品マスタが50列,100列のように、沢山の列で構成されている場合はどうでしょうか?

列番号を数えるだけで一苦労ですね。また、人間の目で一つ一つ数えるので、数え間違えることもあります。

セル子
列が沢山あると、いくつ数えたか忘れてしまいそう。
エク先輩
そうだね。
INDEX,MATCH関数は、列番号を数えないでOK!

②検索先の列(キー)が左側に無いと使えない

VLOOKUPの欠点2:検索列が左側に無いと使えない
上図のように、商品マスタの”商品ID”列(F列)が”商品名”列(E列)の右側にあるため、VLOOKUP関数でデータ取得することができません。

VLOOKUP関数は、”検索先の列”が”取得データ列”の左側に無いと、VLOOKUPを使えない制約があるのです。

どうしてもVLOOKUP関数を使いたい場合は、”商品ID”列を”商品名”列の左側に移動させましょう。

エク先輩
INDEX,MATCH関数は、列を並び替える必要なし!

INDEX×MATCHを使って、VLOOKUPの欠点を克服!

VLOOKUP関数の代わりに、INDEX関数とMATCH関数を組み合わせて、商品名を取得してみましょう。

VLOOKUP関数の代替策:INDEX・MATCH関数を利用
C3セルに入力している関数は、以下の通り。
=INDEX(E:E,MATCH(B3,F:F,0))
①取得データ列=E:E(商品マスタの”商品名”列)
②検索値=B3(商品ID”ID001″)
③検索先の列=F:F(商品マスタの”商品ID”列)
④照合の型=0(完全一致)

エク先輩
INDEX関数MATCH関数の細かいことは理解しなくても良いけど、形をしっかり覚えておこう。

セル子
INDEXと入力して、「①取得データ列」を入力する。
セル子
次にMATCHと入力して、「②検索値」→「③検索先の列」→「④照合の型」の順で入力する。
セル子
よし、できました!けっこう簡単ですね。
エク先輩
いいね!

あと、1点だけ補足させて。
「①取得データ列」と「③検索先の列」は列全体を指定するのがポイント!

INDEX・MATCH関数はメンテナンスが容易

商品マスタに「メロン」を追加してみましたが、関数を修正しないで良いのです。商品マスタが更新されるたびに指定した範囲をメンテナンスする時間が省けます。

入力方法のまとめ

=INDEX(,MATCH(,,))

取得したいデータ列を指定します。
検索する値を指定します。直接入力するか、セル参照します。
検索先の列を指定します。
照合の型を指定します。
検索値()と完全一致する最初の値を検索=0
検索値()以下の最大の値を検索=1または省略
検索値()以上の最小の値を検索=-1
※照合の型を”1″とする場合は検索先の列()を昇順に並び替える必要あり。”-1″とする場合は検索先の列()を降順に並び替える必要あり。

仕組みを理解したい方向けの説明

行と列を数値で指定してデータを取得するのがINDEX関数です。数値で指定しなければならないのが、INDEX関数のデメリット。

INDEX関数のデメリットを補ってくれるのが、セルの位置を数値で返すMATCH関数です。

 

まず、MATCH関数部分を算出します。

INDEX,MATCH関数の使い方①:MATCH関数の使い方
B3セルに入力している関数は、以下の通り。
=MATCH(A3,F:F,0)
検索値=A3(商品ID”ID001″)
検索先の列=F:F(商品マスタの”商品ID”列)
照合の型=0(完全一致)

商品マスタの”商品ID”列(F列)に”ID001″があるか、上から検索していきます。一番最初に見つかったのは、F3セルです。

F3セルは起点であるF1セルから数えて”3番目”です。よって、MATCH関数が求めた答えは「3」です。

MATCHって何?

1分でわかるMATCH関数

 

次に、INDEX関数部分を算出します。

INDEX,MATCH関数の使い方①:INDEX関数の使い方
C3セルに入力している関数は、以下の通り。
=INDEX(E:E,B3)
取得データ列=E:E(商品マスタの”商品名”列)
行番号=B3(MATCH関数で求めた”ID001の位置”)

商品マスタの”商品名”列(E列)の起点であるE1セルから3行目にあるデータである”フルーツ盛合せ”を取得します。

INDEXって何?

1分でわかるINDEX関数

INDEX×MATCHを使った応用テクニック

INDEX,MATCH関数は垂直方向の検索と同時に、水平方向にも検索できます。VLOOKUPHLOOKUPを同時に行うイメージです。

行・列をキーにしたデータ取得が可能
上図「都道府県・男女別の社員データ」の通り、縦軸が都道府県、横軸が性別の表があったとします。

東京都出身の男性は300人、千葉県出身の女性は150人といった感じです。

  • C4セルに入力している関数は、以下の通り。
    =INDEX(G4:H6,MATCH(A4,F4:F6,0),MATCH(B4,G3:H3,0))
  • まず、MATCH関数の結果をそれぞれ計算してみます。
    1つ目のMATCH関数の結果=2
    2つ目のMATCH関数の結果=2
  • MATCH関数部分の結果をINDEX関数に反映させると、「=INDEX(G4:H6,2,2)」になります。G4:H6(セル範囲)中の2行目/2列目にあるデータである”150″を取得します。

まとめ:INDEX関数とMATCH関数の組み合わせは便利すぎる

  • VLOOKUP関数の欠点は、以下の通り。
    • 列番号を数えるのが面倒。
    • 検索先の列(キー)が左側に無いと使えない。
  • INDEX,MATCH関数は単体では余り使いませんが、組み合わせて使えばとても実用的になります。
    • VLOOKUP関数の欠点を乗り越えられる。
    • 実は、VLOOKUP関数より簡単に入力できる。
    • VLOOKUP関数より、処理が速い(はず)。
  • VLOOKUP関数に限界を感じた方や飽きてしまった方は、INDEX,MATCH関数を使いましょう!
エク先輩
INDEX,MATCH関数の便利さが、もっと世間に広まってほしいなー!

 

▼Excel作業を高速化したい方は必見▼

スポンサーリンク

この記事が気に入ったらシェア!