👈
Googleスプレッドシートの VLOOKUP関数でマイナス方向に値を参照したい場合
背景
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)
以上のように指定することで、検索列の左方向の値を参照可能になる。
Discussion