Excel で INDEX MATCH を使う方法
INDEX MATCH は、表の中から値を検索する 2 つの関数の組み合わせです。MATCH は探している値の位置を求め、INDEX はその位置にある別の列のセルを返します。この組み合わせはすべてのバージョンの Excel で使え、左側の列も検索でき、列を挿入しても機能し続けます。
以下では、基本の書き方、左側への検索、双方向の検索を紹介します。最後にあるレッスンで練習できます。
1. 基本の書き方
書式は =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) です。最後の 0 は完全一致を指定します。ほとんどの場合、これが必要な指定です。両方の範囲は同じ行をカバーしている必要があり、return_range は答えを取り出したい列です。
例:インクの価格
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | 名前 | 価格 | 検索値 | 価格 | |
| 2 | 101 | ペン | 2.5 | インク | 7 | |
| 3 | 102 | インク | 7 | |||
| 4 | 103 | パッド | 4 |
MATCH は B2:B4 の 2 番目の位置で「インク」を見つけ、INDEX は C2:C4 の 2 番目のセルを返します。結果は 7 です。
2. 左側の列を検索する
VLOOKUP で返せるのは、検索列より右側の列だけです。INDEX MATCH では、return_range に指定するだけで、検索列より左にある列も返せます。
例:インクの ID
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | 名前 | 価格 | 検索値 | ID | |
| 2 | 101 | ペン | 2.5 | インク | 102 | |
| 3 | 102 | インク | 7 | |||
| 4 | 103 | パッド | 4 |
ID 列は名前列の左側にあります。INDEX は気にせず、102 を返します。
3. 双方向の検索
行と列が交わる位置の値を求めるには、INDEX に表全体を指定し、2 つの MATCH を使います。1 つ目の MATCH は行を、2 つ目の MATCH は列を見つけます。
例:2 月のインク
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 製品 | 1 月 | 2 月 | 3 月 | 結果 | |
| 2 | ペン | 5 | 6 | 7 | 9 | |
| 3 | インク | 8 | 9 | 10 |
インクは B2:D3 の 2 行目、2 月は 2 列目なので、INDEX は 9 を返します。
4. INDEX MATCH か XLOOKUP か
Microsoft 365 と Excel 2021 以降では、XLOOKUP が 1 つの関数で同じ作業をこなし、読みやすくなっています。それでも INDEX MATCH は、古いファイル、Google スプレッドシート、引き継いだブックで役立つため、知っておく価値があります。比較については、VLOOKUP と XLOOKUP のガイドをご覧ください。
よくあるミス
最後の 0 を省く。 最後の引数がないと、MATCH は並べ替え済みのリストだと見なし、誤った行を返すことがあります。
修正: MATCH の最後には、完全一致を表す 0 を必ず付けます。
範囲の開始行が違う。 一方の範囲が 2 行目、もう一方が 3 行目から始まっていると、すべての答えが 1 行ずれます。
修正: 両方の範囲で最初の行と最後の行を揃えて選択します。
#N/A の結果。 検索値がリストにありません。余分なスペースや、数値が文字列として入力されていることが原因の場合が多いです。
修正: TRIM でデータを整えるか、数式を IFERROR で囲んでメッセージを表示します。
よくある質問
INDEX MATCH は VLOOKUP より速いですか。
大きな表では速いことがあります。2 つの列だけを読むためです。日常的なシートでは差はほとんどないため、読みやすいほうを選んでください。
INDEX MATCH で複数の列を返せますか。
INDEX の return_range に複数の列を指定し、列番号に 0 を使います。または Microsoft 365 では、複数の列を直接返せる XLOOKUP を使います。
MATCH の 0 は何を意味しますか。
照合の種類を表します。0 は完全一致、1 は並べ替え済みリストで検索値を超えない最大の値、-1 は降順リストで検索値を下回らない最小の値を指定します。
関連する関数
- INDEXINDEX は、範囲の指定した行と列の値を返します。MATCH と組み合わせると、柔軟な検索になります。構文、例、INDEX/MATCH の説明を示します。.
- MATCHMATCH は、リスト内での値の位置を返します。例えば、「レナ」は 3 番目の名前だと分かります。一致の種類、例、INDEX と組み合わせた二元の検索を説明します。.
- VLOOKUPVLOOKUP は、表の最初の列から値を探し、同じ行の別の列の値を返します。構文、例、よくある間違い、練習問題を説明します。.
- XLOOKUPXLOOKUP は、1 つの列または行を検索し、どの方向でも、別の列または行から対応する値を返します。見つからなかったときのメッセージも組み込まれています。構文、例、練習問題を説明します。.
- XMATCHXMATCH は、一覧の中の値の位置を返します。既定では完全一致で、近似一致や末尾からの検索もできます。構文と例を紹介します。.
INDEX と MATCH について詳しく
このガイドでは、組み合わせの書き方と 2 つの検索例を紹介しました。この記事では、列を挿入しても壊れない理由、2 つの条件で検索する方法、他の人が書いた INDEX MATCH の数式を読む方法を説明します。
列を挿入しても壊れない理由
VLOOKUP は「3」のような列番号を指定します。表に列が挿入されると、その番号は間違った列を指すようになり、数式は隣の列の値を気づかれないまま返してしまいます。INDEX MATCH は範囲全体を指定します。Excel は列の挿入に合わせて範囲も移動するため、数式は正しい列の値を返し続けます。
=INDEX(C2:C100,MATCH(F2,B2:B100,0))2 つの条件で検索する
地域と製品のように 2 つの値で行を探すには、MATCH に結合したキーを検索させます。MATCH の中で 2 つの列を & で結合し、検索する 2 つのセルも同じように結合します。古いバージョンの Excel では、この数式を Ctrl + Shift + Enter で配列数式として入力する必要があります。Microsoft 365 では通常の数式として動作します。
=INDEX(C2:C100,MATCH(F1&F2,A2:A100&B2:B100,0))表が大きく、数式によってシートの動作が遅くなる場合は、2 つのキーを一度結合した補助列を追加し、その列を検索します。
INDEX MATCH の数式を読む
内側から順に読みます。まず MATCH を見て、何を、どの範囲で探しているか、最後の引数が 0 になっているかを確認します。次に INDEX を見て、答えがどの範囲から取られているか、その範囲が検索範囲と同じ行から始まっているかを確認します。壊れている数式の多くは、開始行が異なる 2 つの範囲を使っています。
Microsoft 365 を使っているなら、同じ検索を XLOOKUP と比べてみてください。[VLOOKUP と XLOOKUP の違い](/guides/vlookup-vs-xlookup) も参照できます。この組み合わせ自体を練習するには、[INDEX MATCH のレッスン](/learn/index-match) を開いてください。