よく「絶対に覚えるべし!」と言われているVLOOKUP関数ですが、下記2つの欠点があります。
- 列番号を数えるのが面倒
- 検索先の列(キー)が左側に無いと使えない
と常々、感じています。
とはいえ…パッと見た感じで難しそうですよね。
=INDEX(①,MATCH(②,③,④))
① | 取得したいデータ列を指定します。 |
② | 検索する値を指定します。直接入力するか、セル参照します。 |
③ | 検索先の列を指定します。 |
④ | 照合の型を指定します。 ・検索値(②)と完全一致する最初の値を検索=0 ・検索値(②)以下の最大の値を検索=1または省略 ・検索値(②)以上の最小の値を検索=-1 ※照合の型を”1″とする場合は検索先の列(③)を昇順に並び替える必要あり。”-1″とする場合は検索先の列(③)を降順に並び替える必要あり。 |
慣れればVLOOKUP関数より簡単に感じますし、汎用的にデータ取得できるので、気に入ってくれるでしょう。
これから誰でもマスターできるように、丁寧に解説していきます。
目次
VLOOKUP関数の「2つの欠点」
①列番号を数えるのが面倒
C3セルに入力している関数は…
=VLOOKUP(B3,$E$3:$F$5,2,FALSE)
①検索値=B3(商品ID”ID001″)
②範囲=$E$3:$F$5(商品マスタ)
③列番号=2(商品マスタの”商品名”列)
④検索方法=FALSE(完全一致)
上記の場合、指定した「②範囲」が2列であるため、列番号を数えるのは簡単です。
しかし商品マスタが50列,100列のように、沢山の列で構成されている場合はどうでしょうか?
列番号を数えるだけで一苦労ですね。また人間の目で一つ一つ数えるので、数え間違えることもあります。
ぶるたろう
②検索先の列(キー)が左側に無いと使えない
商品マスタの”商品ID”列(F列)が”商品名”列(E列)の右側にあるため、VLOOKUP関数でデータ取得することができません。
VLOOKUP関数は“検索先の列”が”取得データ列”の左側に無いと、VLOOKUPを使えない制約があるのです。
どうしてもVLOOKUP関数を使いたい場合は、”商品ID”列を”商品名”列の左側に移動させることになります。
ぶるたろう
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の入力方法まとめ
=INDEX(①,MATCH(②,③,④))
① | 取得したいデータ列を指定します。 |
② | 検索する値を指定します。直接入力するか、セル参照します。 |
③ | 検索先の列を指定します。 |
④ | 照合の型を指定します。 ・検索値(②)と完全一致する最初の値を検索=0 ・検索値(②)以下の最大の値を検索=1または省略 ・検索値(②)以上の最小の値を検索=-1 ※照合の型を”1″とする場合は検索先の列(③)を昇順に並び替える必要あり。”-1″とする場合は検索先の列(③)を降順に並び替える必要あり。 |
INDEX×MATCHの仕組みを理解したい方向け
行と列を数値で指定してデータを取得するのがINDEX関数です。
しかし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」です。
次にINDEX関数部分を算出します
C3セルに入力している関数は…
=INDEX(E:E,B3)
取得データ列=E:E(商品マスタの”商品名”列)
行番号=B3(MATCH関数で求めた”ID001の位置”)
商品マスタの”商品名”列(E列)の起点であるE1セルから3行目にある”フルーツ盛合せ”を取得します。
INDEX×MATCHを使った応用テクニック
INDEX,MATCH関数は垂直方向の検索と同時に、水平方向にも検索できます。VLOOKUPとHLOOKUPを同時に行うイメージです。
下図「都道府県・男女別の社員データ」の通り、縦軸が都道府県、横軸が性別の表があったとします。
東京都出身の男性は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関数の欠点は、列番号を数えるのが面倒だったり、検索先の列(キー)が左側に無いと使えないことです。
VLOOKUP関数に欠点を感じていた方は、INDEX,MATCH関数を使っていきましょう!
- VLOOKUP関数の欠点を乗り越えられる。
- 実は、VLOOKUP関数より簡単に入力できる。
- VLOOKUP関数より処理が速い(はず)。
INDEX,MATCH関数は単体では活躍の機会が少ないですが、組み合わせて使えば超実用的になりますね。
以上です。
さらにレベルアップを図りたい方は、以下の記事も合わせてどうぞ。


