INDEXは、範囲の中から「何行目・何列目」を指定して値を取り出す関数です。
単体では地味ですが、MATCH と組み合わせることで、VLOOKUP の弱点をすべて回避した検索が組めます。この INDEX+MATCH は長年スプレッドシートの定番テクニックとして使われてきました。
書式
=INDEX(範囲, [行番号], [列番号])| 引数 | 内容 |
|---|---|
| 範囲 | 取り出す対象の範囲 |
| 行番号 | 範囲の上から何番目か |
| 列番号 | 範囲の左から何番目か。範囲が1列なら省略可 |
行番号・列番号はシート全体ではなく、指定した範囲の中での位置です。ここを間違えやすいので注意してください。
基本の使い方
| A | B | C | |
|---|---|---|---|
| 1 | 商品コード | 商品名 | 価格 |
| 2 | A-101 | ボールペン | 150 |
| 3 | A-102 | ノート | 320 |
| 4 | B-201 | ハサミ | 480 |
=INDEX(A1:C4, 3, 2) → ノート範囲 A1:C4 の中で、上から3番目・左から2番目 ＝ B3セルの「ノート」が返ります。
範囲を1列だけにすれば、列番号は省略できます。
=INDEX(B1:B4, 3) → ノート行番号を0にすると列全体が返る
行番号に 0 を指定すると、その列の全体が配列として返ります。
=INDEX(A1:C4, 0, 2) → 商品名 / ボールペン / ノート / ハサミ列番号を 0 にすれば行全体です。この挙動は他の関数に範囲を渡すときに使います。
INDEX + MATCH ── 定番の検索パターン
INDEXの本領はここです。行番号を MATCH で求めると、検索関数になります。
「B-201」の商品名を取り出す例：
=INDEX(B2:B4, MATCH("B-201", A2:A4, 0))分解するとこうなります。
- 1.
MATCH("B-201", A2:A4, 0)→ A列の上から3番目にある、なので3 - 2.
INDEX(B2:B4, 3)→ B列の3番目 ＝ハサミ
MATCHで位置を求め、INDEXでその位置の値を取る。 これだけです。
VLOOKUPより優れている点
左方向にも検索できます。 VLOOKUPは範囲の左端列しか検索できませんが、INDEX+MATCHは検索する列と取り出す列が独立しているため、位置関係を問いません。
=INDEX(A2:A4, MATCH("ハサミ", B2:B4, 0)) → B-201列を挿入しても壊れません。 列番号を数字で書いていないためです。
処理が軽いという利点もあります。VLOOKUPは指定した範囲全体を読みますが、INDEX+MATCHは検索列と結果列の2列しか見ません。行数が数万規模になると体感で差が出ます。
XLOOKUPがあるなら
XLOOKUP は INDEX+MATCH と同じことを、1つの関数で書けます。
=XLOOKUP("B-201", A2:A4, B2:B4)読みやすさでは明らかにXLOOKUPが上です。新しく書くならXLOOKUPで構いません。
INDEX+MATCHを覚えておく価値があるのは、次の場合です。
- – 古いExcelとファイルを共有する（XLOOKUPが動かない）
- – 既存ファイルの数式を読む・直す必要がある
- – 行と列の両方をMATCHで求める二次元検索をしたい
二次元の検索
行も列も動的に決めたい場合、MATCHを2つ使います。
=INDEX($B$2:$D$10, MATCH($F$1, $A$2:$A$10, 0), MATCH($F$2, $B$1:$D$1, 0))F1に行の項目、F2に列の項目を入れると、その交差点の値が返ります。クロス集計表からのピンポイント抽出はこの形が定番です。
エラーの対処
#REF! が出る
指定した行番号・列番号が範囲の外を指しています。範囲が3行なのに行番号4を指定した、というケースです。MATCHの結果と範囲の開始行がずれていることも多いので、MATCH 単体の結果を別セルで確かめると原因が早く分かります。
#N/A が出る
INDEXではなく、中の MATCH が失敗しています。検索キーが見つかっていません。
結果が1つずれる
範囲の開始行のずれが原因です。MATCH を A1:A4（見出し行を含む）で取り、INDEX を B2:B4（見出しなし）で指定すると1行ずれます。両方を同じ行から始めるのが鉄則です。
まとめ
- – 書式は
=INDEX(範囲, 行番号, 列番号) - – 位置はシートではなく指定範囲の中での番号
- –
INDEX(結果列, MATCH(キー, 検索列, 0))が検索の定番形 - – 左方向の検索・列挿入への耐性・処理の軽さが利点
- – 新規に書くなら XLOOKUP のほうが読みやすい
コメントを残す