「VLOOKUPでは、検索値より左の列を取り出せない」。そんなときに使えるのが、INDEX関数とMATCH関数の組み合わせです。
少し難しく見えますが、「MATCHで何番目かを調べ、INDEXでその番目の値を取り出す」という流れが分かれば、仕組みはシンプルです。図解で説明します。
=INDEX(B2:B5,MATCH(D2,A2:A5,0))INDEXとMATCHの役割
まず、2つの関数をそれぞれ見てみましょう。
- MATCH関数：検索値が範囲の「何番目」にあるかを返します。=MATCH(検索値,検索範囲,0)
- INDEX関数：範囲の「何番目」の値を返します。=INDEX(範囲,番号)
MATCHで調べた番号を、そのままINDEXに渡すことで、検索ができます。末尾の「0」は完全一致で探す指定です。
基本の使い方｜商品コードから商品名を探す
- 結果を表示したいセル（E2）を選び「=INDEX(」と入力する
- 取り出したい商品名の範囲（B2:B5）をドラッグして選び、カンマを入れる
- 「MATCH(」と入力し、検索値（D2）、検索範囲（A2:A5）、「0」をカンマで区切って入力する
- 「))」で2つの括弧を閉じて Enter を押す
VLOOKUPでできない｜検索値より左の列を取り出す
VLOOKUPは、検索値の列より右の列しか取り出せません。INDEXとMATCHなら、検索の列と取り出す列を別々に指定するので、左側の列も取り出せます。
行と列の2方向で検索する
INDEX関数は、行番号と列番号の2つを指定すると、表の交点の値を取り出せます。行と列をそれぞれMATCHで調べれば、表の見出しから値を探せます。
=INDEX(B2:D5,MATCH(F1,A2:A5,0),MATCH(F2,B1:D1,0))F1に行の見出し（商品名など）、F2に列の見出し（月など）を入力すると、2つの見出しが交わるセルの値が表示されます。
VLOOKUPとの使い分け
| 比較項目 | VLOOKUP | INDEX+MATCH |
|---|---|---|
| 左の列の取得 | できない | できる |
| 列を挿入したとき | 列番号がずれることがある | 式が崩れにくい |
| 式の短さ | 短い | やや長い |
| 初心者への分かりやすさ | 分かりやすい | 慣れが必要 |
| 2方向の検索 | 難しい | できる |
まとめ
- MATCHで位置を調べ、INDEXでその位置の値を取り出す
- 基本形は =INDEX(取り出す範囲,MATCH(検索値,検索範囲,0))
- 検索の列より左にある列も取り出せる
- 行と列の両方をMATCHにすると、表の交点を検索できる
- 完全一致で探すため、MATCHの最後は0にする
VLOOKUPで困ったときの代わりとして、覚えておくと便利です。
コメント