テストの表から、最高点を取った子の名前を探したい。ところが、名前の列が、点数の列より左にある。教員の仕事では、数字から名前を探す場面があります。
INDEX(インデックス)関数とMATCH(マッチ)関数を組み合わせると、左の列の値も取り出せます。この記事では、教員の仕事でよく出る例を使って、組み合わせ方を説明します。
教員の仕事でINDEXとMATCHを使う場面|最高点の子の名前を出す
表の上のアルファベット(A、B、C…)が列、左の数字(1、2、3…)が行です。「B2」は、B列の2行目のセルのことです。
| A | B | |
|---|---|---|
| 1 | 氏名 | 算数 |
| 2 | 青木 | 72 |
| 3 | 伊藤 | 88 |
| 4 | 上田 | 60 |
=INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0))
MAXで最高点を出し、MATCHで、その点数がある行の位置を探し、INDEXで、その行の氏名を取り出します。上の表では「伊藤」と出ます。
ここからは、INDEXとMATCHの組み合わせ方を順に見ていきます。
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とMATCHの組み合わせでは、MATCH関数の3つ目の引数「照合の型」が結果を左右します。ここを正しく指定できると、使える場面がぐっと広がります。
照合の型の3種類
- 0:完全一致。完全に同じ値を探す(ほとんどの場面でこれを使う)
- 1:検索値以下で最大の値を探す(範囲が昇順に並んでいる必要がある)
- -1:検索値以上で最小の値を探す(範囲が降順に並んでいる必要がある)
省略すると「1」になるため、完全一致で探したいのに「0」を書き忘れて、違う行を返してしまうミスが多く見られます。迷ったら「0」と書いておきましょう。
ワイルドカードで部分一致検索
完全一致(0)のときは、ワイルドカードが使えます。商品名の一部だけが分かっているときは、次のように書きます。
=INDEX(B2:B100,MATCH("*"&E1&"*",A2:A100,0))
E1に入力したキーワードを含む、最初の行が見つかります。
複数条件で探すとき
2つの条件がそろう行を探すときは、検索値と検索範囲をそれぞれ「&」でつなぎます。Microsoft 365やExcel 2021では、そのまま入力して確定できます。古いバージョンでは、「Ctrl」+「Shift」+「Enter」で確定する必要があります。
INDEXとMATCHを使うときの注意点
INDEXとMATCHの組み合わせは柔軟ですが、2つの関数の範囲がずれていると、違う行の値を返してしまいます。
- INDEXの範囲とMATCHの範囲は、同じ開始行・同じ行数にそろえる(A2:A100とB2:B100のように)
- 範囲は絶対参照($A$2:$A$100)にして、コピーしてもずれないようにする
- 見つからないときの#N/Aは、IFERROR関数で「該当なし」などの表示に置き換える
VLOOKUPは列番号を数字で指定するため、表の途中に列を挿入すると結果がずれることがあります。INDEXとMATCHは列そのものを範囲で指定するので、列を挿入しても壊れにくいのが大きな利点です。また、検索する列を並べ替えておく必要もありません。
迷ったときの選び方
どの関数を使うか迷ったときは、次の目安で選ぶと分かりやすくなります。
- 検索する値が表の左端にあり、列の追加も少ない:VLOOKUPで十分
- 検索する値が表の右側にある、列をよく挿入する:INDEXとMATCH
- Microsoft 365やExcel 2021以降を使っている:XLOOKUPがもっとも書きやすい
自分の使っているExcelのバージョンと、表の作りを見て、書きやすい方法を選びましょう。
INDEXとMATCHを使うときの注意
- 同じ最高点の子が複数いると、一番上の子の名前だけが出ます。
- 範囲の行数が、そろっているかを確認します。


コメント