Googleスプレッドシートの VLOOKUP関数でマイナス方向に値を参照したい場合

1 min read読了の目安(約1600字

背景

VLOOKUP関数は既存の表データから、検索キーを縦方向(列方向)に探索し、探索列から右方向の値を抽出できる関数であるが、探索列から左方向(マイナス方向)を抽出することはできない。

以下、公式ドキュメントから引用

VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])

  • 検索キー - 検索する値です(例: 42、"ネコ"、I24)。
  • 範囲 - 検索対象の範囲です。範囲の先頭列で検索キーとして指定したキーを検索します。
  • 番号 - 値を返す列の番号です。範囲の先頭列を 1 とします。
    • 番号が 1 と範囲内の列数の間に含まれない場合は、#VALUE! が 返されます。
  • 並べ替え済み - [既定値は TRUE] - 検索対象の列(指定した範囲の先頭列)が並べ替え済みであるかどうかを指定します。通常は FALSE をおすすめします。
    • 並べ替え済みを FALSE に設定することをおすすめします。並べ替え済みが FALSE の場合、完全一致のみが返されます。一致する値が複数ある場合は、最初に見つかった値に対応するセルの内容が返され、一致する値が見つからない場合は #N/A が返されます。
    • 並べ替え済みを TRUE に指定するか省略する場合、最も近い一致(検索キー以下)が返されます。検索列のすべての値が検索キーよりも大きい場合は、#N/A が返されます。

左方向(マイナス方向)を指定したい場合は、INDEX関数と MATCH関数を組み合わせることで、 VLOOKUP関数と同様のことができるようになる。

以下のようなスプレッドシートがあり、 シート1 のデータを参照し、 シート2* の部分を埋める場合を想定

シート1(検索される側)

A B C D
1 last_name first_name id age
2 Suzuki Kazu 1 23
3 Yamada Goro 3 26
4 Takahashi Hanako 4 21
5 Tanaka Mami 7 34
6 Nakano Kena 9 31

シート2(検索する側)

A B C
1 id age last_name
2 1 * *
3 3 * *
4 4 * *
5 7 * *
6 9 * *

シート2 の B列は検索キーに対して右方向なので、以下のように VLOOKUP関数を用いて検索できる。

シート2 のセルB2
=VLOOKUP(C2, シート1!$C$2:$D$6, 2, false)

シート2 の C列は検索キーに対して左方向なので、VLOOKUP関数は使えない。そこで以下のように INDEX関数と MATCH関数を組み合わせて指定する。

書き方
=index(検索範囲, match(検索したい値, 検索したい列の範囲, 0), 左から数えて何番目)
シート2 のセルC2
=index(シート1!$A$2:$C$6, match(C2, $C$2:$C$6, 0), 1)

以上のように指定することで、検索列の左方向の値を参照可能になる。