VLOOKUP関数でできない場合、MATCH/INDEX関数を使おう。

この記事では、SpreadSheetの関数、MATCHとINDEXについて紹介します。
VLOOKUP関数では実現できない、右側の列から、左側の列を参照するといった事ができるようにもなるので、参考になれば幸いです。

まずは、各関数の挙動を確認してから、組み合わせてつかう例を記載していきます。

MATCH関数

MATCH(検索キー, 範囲, 検索の種類)
指定の値を検索して、見つかったセル番号を返してくれます。

検索の種類に関しては、範囲がソート済みかどうかになります。
1: 昇順
0: ソートされてない
-1: 降順

例えば、A3に"hoge"というセルがある場合、
=MATCH("hoge", A:A, 0) この結果は、3となります。(上から3つ目)

範囲の指定は、単一の行か列である必要があります。
A:Aや、A1:A10などの範囲を指定した場合、MATCH関数は、縦方向に検索します。

次の例では、D1に"hoge"というセルがある場合、
=MATCH("hoge", 1:1, 0) この結果は、4となります。(左から4つ目)

1:1や、A1:Z1などの範囲を指定した場合、MATCH関数は、横方向に検索します。

INDEX(範囲, 行, 列)
INDEX関数は、指定の範囲に行と列を指定して参照する事ができるものです。

例
=INDEX(A1:C5, 4, 2)
「A1:C5」範囲の、(4行目, 2列目)のセルを参照する。
結果は、B4のセル値が参照される事になります。

LOOKUP/VLOOKUP関数では、指定範囲の1列目または1行目のセルからしか検索ができませんが、
MATCH関数と、INDEX関数を組み合わせる事で、どの行と列からでも検索する事ができます。

商品コードから、商品名を参照するのは、VLOOKUP関数でできますが、
商品名から商品コードを検索することができません。

VLOOKUP関数を使った例

商品名が２列目、商品コードが1列目にあるため、
VLOOKUP関数では対応できないのです。

そういった場合に、
MATCH/INDEX関数を組み合わせて、下記のように記載することができます。
=INDEX(A2:A4, MATCH("おしるこ", B2:B4, 0), 1)

MATCH関数で、検索したい値を渡し、行数を取得します。
("おしるこ"は、B2:B4範囲の3行目)

取得した行数を使って、INDEX関数で1列目の値を取得します。
(A2:A4範囲の3行目,1列目)