概要
VLOOKUPでは検索キーが一番左側にある前提で利用しますが、例えば以下の表のように検索キーが右側にある場合もあります。要は左側に出力したい情報が格納されている場合。
| A | B | C | |
|---|---|---|---|
| 1 | 値段 | 商品名 | コード |
| 2 | 100 | りんご | abc |
| 3 | 200 | いちご | def |
| 4 | 150 | みかん | hij |
| 5 | 150 | ばなな | klm |
列が並びかえれない場合は、MATCH関数とINDEX関数を利用することで例えば以下のようにE列に検索キーを入れてF列に該当する情報を出力することが可能です。
| E | F | |
|---|---|---|
| 1 | コード | 値段 |
| 2 | hij | =INDEX(A$2:C$5,MATCH(E2,C$2:C$5,0),1) |
以下、それぞれの関数の意味を分かりやすく解説しました。
MATCH関数の役割
MATCH関数は以下の構文で利用して検索キーと完全合致する行数を取得できます。
=MATCH(検索キー,検索エリア,0)
例えば以下の表にて
| A | B | C | |
|---|---|---|---|
| 1 | 値段 | 商品名 | コード |
| 2 | 100 | りんご | abc |
| 3 | 200 | いちご | def |
| 4 | 150 | みかん | hij |
| 5 | 120 | ばなな | klm |
E列に以下のコードを入れるとF列に行数が出ます。
| E | F | |
|---|---|---|
| 1 | コード | 値段 |
| 2 | klm | =MATCH(E2,C$2:C$5,0) |
↓計算結果↓
| E | F | |
|---|---|---|
| 1 | コード | 値段 |
| 2 | klm | 4 |
確かに検索エリア(C$2:C$5)から欲しい文字列を探して何行目かを返しています。
注意点はコードが一意であることは前提です。例えば以下のように複数ヒットする場合は
| A | B | C | |
|---|---|---|---|
| 1 | 値段 | 商品名 | コード |
| 2 | 100 | りんご | abc |
| 3 | 200 | いちご | klm |
| 4 | 150 | みかん | hij |
| 5 | 120 | ばなな | klm |
以下のように最初のヒットが返り値になります。
| E | F | |
|---|---|---|
| 1 | コード | 値段 |
| 2 | klm | 2 |
INDEX関数の役割
INDEX関数の構文は以下で、それにより指定したセルの値が取得できます。
=INDEX(表のエリア,何行目,何列目)
同じく以下の表にして
| A | B | C | |
|---|---|---|---|
| 1 | 値段 | 商品名 | コード |
| 2 | 100 | りんご | abc |
| 3 | 200 | いちご | def |
| 4 | 150 | みかん | hij |
| 5 | 120 | ばなな | klm |
任意のセルに以下を入力すれば2行目の2列目という指定なので「いちご」が返ってきて
=INDEX(A$2:C$5,2,2)
以下のようにすれば3行目の1列目という指定なのでみかんの値段である「150」が返ってきます。
=INDEX(A$2:C$5,3,1)
二つの関数を組み合わせる
=INDEX(表のエリア,MATCH(検索文字列,検索列,0),何列目)
とすればMATCH関数にて行が特定できるのでVLOOKUPのような検索が行えます。
| E | F | |
|---|---|---|
| 1 | コード | 値段 |
| 2 | hij | =INDEX(A$2:C$5,MATCH(E2,C$2:C$5,0),1) |
という計算式にて
| E | F | |
|---|---|---|
| 1 | コード | 値段 |
| 2 | hij | 150 |
が返ってくるというわけです。これが冒頭の計算式です。
終わりに
VLOOKUPの応用のような使い方ですが、覚えておくと役立つと思います。
