Excel VBA(エクセル マクロ) 小技集そのF INDEXとMATCH関数で指定した検索値の左側の列を検索する(VLOOKUPの代替)

ExcelでVLOOKUPを使用すると、検索する列を検索範囲の一番左側に持ってこなければ為らず、検索する列の左側の値を取り出すことはできない。
そこで、INDEX、MATCH関数を使用して取り出すことを考える。

ExcelでVLOOKUP関数を使用していると使いにくい部分に出会う。

=VLOOKUP(A1,データ!B1:D4,2,0)

まずA1の値をシート:データのB1:D4から探し、B行から2列目の部分をとりだす。
下記例では、A1をオレンジとすると200円という値がでてくる。

シート:データ
  A   B     C   D
1 8/10 リンゴ  300円  30%
2 8/11 オレンジ 200円  20%
3 8/12 イチゴ  500円  25%
4 8/13 キューイ 400円  40%

VLOOKUPではA1の検索値をデータ!B1:D4の範囲の一番左の列(B1)からしか検索できない。

では、A列の8/11を検索するにはどうするか。


INDEX関数とMATCH関数を使用する。

INDEX関数は列と行を指定して値を得る。


=INDEX(データ!A1:D4,2,2)

  A   B     C   D
1 8/10 リンゴ  300円  30%
2 8/11 オレンジ 200円  20%
3 8/12 イチゴ  500円  25%
4 8/13 キューイ 400円  40%

A1〜D4までの範囲で2行目2列目を検索するので、オレンジの値を得る。


MATCH関数は検索値がある行数を返す。
ただし、MATCH関数は1列しか検索されない欠点がある。

=MATCH('オレンジ', データ!B1:B4, 0)

  A   B     C   D
1 8/10 リンゴ  300円  30%
2 8/11 オレンジ 200円  20%
3 8/12 イチゴ  500円  25%
4 8/13 キューイ 400円  40%

オレンジは2行目なので2を返す。最後の,0は完全一致を示す。


さあ、INDEX関数とMATCH関数を組み合わせて、VLOOOKUP関数より高性能な検索関数を作る。

=INDEX(データ!A1:D4,Match('オレンジ', データ!B1:B4, 0),1)

これでやると、MATCH関数でオレンジがある行数2を返し、データ!A1:D4の範囲の1列目である8/11を返す。

これで、検索値より左側にあるものを検索できるようになった。

VLOOKUP関数より高性能です。


PS.

実はこの方法を使うと上下左右どの方向にも検索ができる。

=INDEX(データ!A1:D4,MATCH('オレンジ', データ!B1:B4,0)+1,3)とやると

オレンジより1つ下の3列目の500円が検索される。

VLOOKUPで左から何番目を検索するだけより、上下左右どの方向も検索できるので、この方法は是非ともマスターしておきたい。

忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP