30代会社員のリアルな投資成績公開中

INDEX関数とMATCH関数の組み合わせが最強!わかりやすく使い方を解説

VLOOKUP関数の欠点を乗り越えよ

よく「絶対に覚えるべし!」と言われているVLOOKUP関数ですが、下記2つの欠点があります。

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

 

ぶるたろう

みんな、INDEX関数MATCH関数を組み合わて使えば良いのに…

と常々、感じています。

 

とはいえ…パッと見た感じで難しそうですよね。

=INDEX(,MATCH(,,))

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

慣れれば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関数は、列番号を数えないで大丈夫!

 

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

VLOOKUPの欠点2:検索列が左側に無いと使えない

商品マスタの”商品ID”列(F列)が”商品名”列(E列)の右側にあるため、VLOOKUP関数でデータ取得することができません。

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

 

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

ぶるたろう

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

VLOOKUPの欠点を克服!INDEX×MATCHのメリット

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の入力方法まとめ

=INDEX(,MATCH(,,))

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

INDEX×MATCHの仕組みを理解したい方向け

行と列を数値で指定してデータを取得するのが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」です。

 

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

INDEX,MATCH関数の使い方①:INDEX関数の使い方

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

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

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関数の欠点は、列番号を数えるのが面倒だったり、検索先の列(キー)が左側に無いと使えないことです。

VLOOKUP関数に欠点を感じていた方は、INDEX,MATCH関数を使っていきましょう!

INDEX,MATCH関数は凄い
  • VLOOKUP関数の欠点を乗り越えられる。
  • 実は、VLOOKUP関数より簡単に入力できる。
  • VLOOKUP関数より処理が速い(はず)。

INDEX,MATCH関数は単体では活躍の機会が少ないですが、組み合わせて使えば超実用的になりますね。

 

以上です。

さらにレベルアップを図りたい方は、以下の記事も合わせてどうぞ。

Excel(エクセル)は難しすぎ?覚えるべき関数は最低10個 セル結合はやめよう【セル結合を使わないで!】「やめる」だけでエクセル作業効率アップ Excelクイックアクセスツールバーを使っていますか?Excelクイックアクセスツールバーおすすめ設定【外資コンサル直伝】