INDEX関数は、範囲の「何行目・何列目」にある値を返す関数です。単体では検索できませんが、MATCH関数と組み合わせるとVLOOKUPでは取れない左側の列も引けます。列を挿入しても壊れないのも利点です。
この記事でわかること
- INDEX関数の3つの引数と、行番号・列番号の数え方
- INDEX＋MATCHで左側の列を引く組み立て方
- 行と列の両方を検索する二次元の引き方
- 複数条件で1件を特定する書き方
#REF!・#N/Aが出るときの原因と直し方
INDEX関数の構文と引数
構文
=INDEX(配列, 行番号, [列番号])| 引数 | 指定するもの | 注意点 |
|---|---|---|
| 配列 | 値を取り出したい範囲 | 見出し行は含めないほうが数えやすい |
| 行番号 | 範囲の上から何行目か | シートの行番号ではなく、範囲内の位置 |
| 列番号 | 範囲の左から何列目か | 範囲が1列だけなら省略できる |
行番号・列番号は、いずれも範囲の中での相対的な位置です。範囲が $C$2:$E$100 なら、行番号1はシートの2行目、列番号1はC列を指します。
範囲が1列だけ、または1行だけの場合は、2つ目の引数だけで位置を指定できます。実務でよく使うのはこの形です。
INDEX関数はExcel 2016以降のどのバージョンでも使えます。動的配列のような世代差はありません。
基本の使い方
商品マスタが F列（商品コード）・G列（商品名）・H列（単価）に並んでいるとします。3行目の2列目を取り出す式です。
=INDEX($F$2:$H$100, 3, 2)範囲の3行目＝シートの4行目、2列目＝G列。つまりG4の値が返ります。
1列だけの範囲なら列番号は要らない
=INDEX($G$2:$G$100, 3)商品名の列だけを範囲にすれば、行番号だけで済みます。INDEX＋MATCHで使うのは、ほぼこの形です。
ただしこのままでは「3行目」を人が決めていることになります。実務で欲しいのは「商品コードがA-102の行」であって、3行目ではありません。その番号を計算するのがMATCH関数の役割です。
INDEX＋MATCHの組み立て方
① 縦1本で引く（基本形）
A2に入力した商品コードから、商品名を引く式です。
=INDEX($G$2:$G$100, MATCH($A2, $F$2:$F$100, 0))分解すると次のようになります。
- MATCHが
$F$2:$F$100の中でA2を探し、上から何番目かを数値で返す - その数値をINDEXが行番号として受け取る
- INDEXが
$G$2:$G$100の同じ位置の値を返す
MATCHの第3引数0は完全一致を意味します。ここを省略すると近似一致（1）になり、範囲が昇順に並んでいないかぎり結果は当てになりません。必ず0を書いてください。
② 左側の列を引く
INDEXの強みはここです。検索する列と返す列の位置関係に制約がありません。
商品名（G列）から商品コード（F列）を逆に引く式です。
=INDEX($F$2:$F$100, MATCH($A2, $G$2:$G$100, 0))VLOOKUPは範囲の左端列しか検索できないため、この向きは書けません。表の並びを変えずに引けるのがINDEX＋MATCHです。
③ 行と列の両方を検索する（二次元）
縦に支店名、横に月が並んだクロス集計表から、交差する値を取り出します。MATCHを2本使う形です。
=INDEX($B$2:$M$50, MATCH($P2, $A$2:$A$50, 0), MATCH($Q$1, $B$1:$M$1, 0))1本目のMATCHが行、2本目が列を特定します。見出しの位置が変わっても、その都度数え直されるため、列の入れ替えで壊れません。
④ 複数条件で1件を特定する
「支店」と「商品」の2つが一致する行を引くなら、条件を掛け算でつなぎます。
=INDEX($D$2:$D$100, MATCH(1, ($A$2:$A$100=$G2)*($B$2:$B$100=$H2), 0))条件が一致した行だけが1になり、MATCHがその位置を探す仕組みです。
⚠️ この式は配列を扱います。Excel 2016・2019では入力時に Ctrl＋Shift＋Enter が必要です。Microsoft 365とExcel 2021以降なら、通常のEnterで確定できます。
うまくいかないときの原因と直し方
| 表示・症状 | 主な原因 | 直し方 |
|---|---|---|
#REF! | 行番号・列番号が範囲の外を指している | 範囲を広げるか、番号を数え直す |
#VALUE! | 行番号に数値以外が入っている | MATCHが文字列を返していないか確認する |
#N/A | MATCHが検索値を見つけられない | 下の「#N/Aの4つの原因」を確認する |
| 値が1行ずれる | 範囲に見出し行を含めている | 範囲をデータの先頭行から取り直す |
| 検索範囲と取得範囲の行がずれる | 2つの範囲の開始行が違う | どちらも同じ行番号で始め、同じ行で終える |
| コピーすると結果が変わる | 範囲が相対参照のまま | $F$2:$F$100 の形に固定する |
| 意図しない値が返る | MATCHの第3引数を省略している | 0 を明示して完全一致にする |
#N/Aの4つの原因
#N/A はINDEXではなくMATCHが出しています。見た目が同じでも一致しないケースがあるため、次の順で確認するのが早道です。
- 検索値が範囲に存在しない —
=COUNTIF($F$2:$F$100, $A2)が0なら不在 - 前後に余分な空白がある —
=TRIM(A2)で確認し、置換で除去する - 数値と文字列が混ざっている — 数値は右寄せ、文字列は左寄せ。片方だけ寄り方が違えば型の不一致
- 全角と半角が混ざっている — ASC関数で半角に寄せて比較する
エラーを隠したい場合はIFERROR関数で包みますが、IFNA関数のほうが安全です。IFERRORはすべてのエラーを飲み込むため、#REF! のような設計ミスまで見えなくなります。
=IFNA(INDEX($G$2:$G$100, MATCH($A2, $F$2:$F$100, 0)), "該当なし")範囲の開始行を揃える
INDEXとMATCHで開始行が違うのは、静かに間違った値を返す厄介な事故です。
INDEX($G$3:$G$100, MATCH($A2, $F$2:$F$100, 0)) のように片方が3行目から始まっていると、エラーも出ないまま1行ずれた値が返り続けます。数式を見直すときは、この2つの範囲の開始行と終了行が一致しているかを最初に確かめてください。
似た関数との使い分け
| 関数・組み合わせ | 対応バージョン | 選ぶ場面 |
|---|---|---|
| INDEX＋MATCH | 2016〜 | 左側の列も引きたい。2016・2019の相手にも渡す |
| VLOOKUP | 2016〜 | 左端列で検索するだけの単純な引き当て |
| XLOOKUP | 2021〜 | 同じことを1つの関数で書きたい |
| OFFSET | 2016〜 | 基準セルからずらした範囲が欲しい |
| FILTER | 365／2021〜 | 一致する行を1件でなく全件取り出したい |
| INDIRECT | 2016〜 | 参照先を文字列で組み立てたい |
VLOOKUPと比べたときの違い
3つあります。左側の列を取れること、列を挿入しても壊れないこと、検索する列と返す列を別々に指定できることです。
VLOOKUPは列番号を数値で持つため、マスタに列を1本足すと全部の式が別の列を指し始めます。INDEX＋MATCHは位置を毎回数え直すので、この事故が起きません。
参照する範囲が小さくなるのも構造上の違いです。VLOOKUPは検索列から取得列までの表全体を範囲にしますが、INDEX＋MATCHは2本の列だけを参照します。
OFFSETとの違い
似た用途に見えますが、OFFSETは揮発性関数です。シートのどこかを変更するたびに再計算されるため、数が増えるほど動作が重くなります。位置を指定して値を取るだけなら、INDEXを選ぶほうが軽くなります。
XLOOKUPがあるなら不要か
Excel 2021以降だけを使う環境なら、XLOOKUPで置き換えられます。1つの関数で完結し、見つからない場合の表示も指定できるためです。
ただしXLOOKUPは2021以降で、2016・2019で開くと #NAME? になります。配布するブックや、相手の環境が分からないファイルでは、INDEX＋MATCHのほうが安全です。
よくある質問
Q1：INDEX関数だけで値を検索できますか？
できません。INDEXは「何行目」を数値で受け取る関数で、探す機能を持っていません。位置を計算するMATCH関数とセットで使うのが基本形です。行番号が固定でよい場合（常に3行目、など）だけ、単体で使えます。
Q2：行番号に0を指定するとどうなりますか？
その範囲の列全体が返ります。単独のセルに入れると使いにくいですが、=SUM(INDEX($B$2:$E$100, 0, MATCH($G$1, $B$1:$E$1, 0))) のように、MATCHで特定した列をまるごと合計したいときに使えます。
Q3：MATCHの第3引数を省略すると何が起きますか？
近似一致（1）として動きます。範囲が昇順に並んでいれば「検索値以下の最大値」を探しますが、並んでいない表では見当違いの位置を返します。エラーにならず、間違った値が返るのが厄介な点です。完全一致の0を必ず明示してください。
Q4：別シートのマスタを参照できますか？
できます。=INDEX(マスタ!$G$2:$G$100, MATCH($A2, マスタ!$F$2:$F$100, 0)) の形です。別ブックも参照できますが、参照先のブックを閉じていると値が更新されないことがあり、ファイルを移動するとリンクが切れます。継続して使うなら同じブック内にマスタを持たせるのが確実です。
Q5：一致する行が複数あるとどうなりますか？
MATCHは最初に一致した位置を返すため、1件だけが取得されます。すべて取り出したいなら、Excel 2021以降のFILTER関数を使うか、作業列に連番を振って何件目かを指定する形にします。
まとめ
- 引数は配列・行番号・列番号。番号は範囲内の位置で数える
- 単体では検索できない。MATCHで位置を計算させてセットで使う
- MATCHの第3引数は必ず
0。省略すると誤った値が静かに返る - 2つの範囲は開始行と終了行を揃える。ずれてもエラーは出ない
- 左側の列を引ける・列の挿入に強い。2016・2019へ配布するならこの形
※関数の対応バージョンはExcelの一般提供時点の情報にもとづいています。お使いの環境や更新プログラムの適用状況により、利用できる関数や表示が異なる場合があります。
