「この値、表の何番目にあるんだろう？」と手作業で数えた経験はありませんか。データが数十行、数百行になると、目で追いかけるのは正直しんどいですよね。
MATCH関数を使えば、範囲の中から値の位置番号を一発で取得できます。さらにINDEX関数と組み合わせると、VLOOKUPでは難しかった「左方向の検索」も自由自在です。
この記事では、MATCH関数の基本から照合の型の使い分けまで解説していきます。INDEX+MATCHの黄金パターンやエラーの対処法も実例つきで紹介します。
この記事は次のような人におすすめ
- 指定した範囲の中から値が何番目にあるか知りたい
- INDEX+MATCHの組み合わせをマスターしたい
- VLOOKUPの左方向検索問題を解決したい
MATCH関数とは？
MATCH関数は、指定した範囲の中から検索値を探し、その相対的な位置番号を返す関数です。読み方は「マッチ関数」で、英語の match（一致する）が語源です。
たとえば、A1:A5に商品名が5つ入っているとします。「バナナ」の位置を調べると3が返ります。セルのアドレスではなく「範囲の先頭から数えて何番目か」を返すのがポイントです。
MATCH関数は単体で使うこともありますが、INDEX関数と組み合わせて使うケースが実務では圧倒的に多いです。
NOTE
MATCH関数はExcel 2007以降のすべてのバージョン、Microsoft 365、Googleスプレッドシートで使用できます。
MATCH関数の書き方（構文と引数）
基本構文
=MATCH(検査値, 検査範囲, [照合の型])引数の説明
| 引数 | 必須/省略可 | 説明 |
|---|---|---|
| 検査値 | 必須 | 検索したい値を指定します。数値・文字列・セル参照が使えます |
| 検査範囲 | 必須 | 検索対象の範囲を指定します。1行または1列の範囲のみ有効です |
| 照合の型 | 省略可 | 検索の方法を 1, 0, -1 のいずれかで指定します。省略すると 1 になります |
検査範囲には1行または1列しか指定できません。複数行かつ複数列の範囲を指定するとエラーになるので注意してください。
照合の型の詳細
照合の型は MATCH関数の動作を決める重要な引数です。3種類の指定方法があります。
| 照合の型 | 動作 | データの並び順 | 用途 |
|---|---|---|---|
| 0（完全一致） | 検査値と完全に一致する値を検索します | 並び順は問いません | 商品コード・名前など正確な値を探すとき |
| 1（以下の最大値） | 検査値以下の最大の値を検索します | 昇順に並べておく必要あり | 価格帯・ランク分けなど範囲で区切るとき |
| -1（以上の最小値） | 検査値以上の最小の値を検索します | 降順に並べておく必要あり | 在庫数や得点の上限判定をするとき |
実務で最も使うのは 照合の型 0（完全一致） です。迷ったらまず 0 を指定しておけば間違いありません。
TIP
照合の型を省略すると 1（以下の最大値）になります。完全一致のつもりで省略すると意図しない結果になるので、明示的に 0 を書く習慣をつけてみてください。
MATCH関数の基本的な使い方
完全一致で位置を取得する（照合の型 0）
商品リストから「バナナ」の位置を調べる例です。
A列に商品名が入っているとき、次の数式で位置番号を取得できます。
=MATCH("バナナ", A1:A5, 0)A1:A5が「りんご, みかん, バナナ, ぶどう, もも」なら、結果は 3 です。
セル参照を使って検索値を指定することもできます。
=MATCH(D1, A1:A5, 0)D1に入っている値をA1:A5から探して位置を返します。
以下の最大値で位置を取得する（照合の型 1）
価格帯やランク分けに便利なのが照合の型 1 です。
たとえばA1:A4に「0, 60, 70, 80」と昇順で入っているとします。得点が75のとき、次の数式を使います。
=MATCH(75, A1:A4, 1)75以下の最大値は「70」なので、結果は 3（3番目の位置）です。これを使えば得点に応じたランク分けが簡単にできます。
以上の最小値で位置を取得する（照合の型 -1）
照合の型 -1 は降順データから検索するときに使います。
A1:A4に「100, 80, 60, 40」と降順で入っている場合を考えます。
=MATCH(70, A1:A4, -1)70以上の最小値は「80」なので、結果は 2 です。
ワイルドカードを使った部分一致検索
照合の型 0 のときだけ、ワイルドカードが使えます。
| ワイルドカード | 意味 | 例 |
|---|---|---|
| * | 任意の文字列（0文字以上） | “東京*” で「東京都」「東京支店」に一致 |
| ? | 任意の1文字 | “田?太郎” で「田中太郎」に一致 |
| ~ | ワイルドカードのエスケープ | “~*” でアスタリスクそのものを検索 |
=MATCH("東京*", A1:A10, 0)「東京」で始まる最初の値の位置を返します。
INDEX+MATCH関数の黄金パターン
MATCH関数が真価を発揮するのは、INDEX関数との組み合わせです。INDEX関数は「行番号と列番号を指定して値を取り出す」関数です。ここにMATCH関数で取得した位置番号を渡すと、柔軟な検索ができるようになります。
基本の書き方
=INDEX(取り出す範囲, MATCH(検索値, 検索する範囲, 0))具体例を見てみましょう。A列に商品コード、B列に商品名、C列に価格が入った表があるとします。
商品コード「A003」の価格を取得するには、次のように書きます。
=INDEX(C1:C10, MATCH("A003", A1:A10, 0))MATCH関数がA列から「A003」の位置を取得し、INDEX関数がC列の同じ行の値を返す仕組みです。
VLOOKUPの左方向検索問題を解決する
VLOOKUP関数は便利な検索関数ですが、検索列より左側の列からは値を取得できないという制限があります。
たとえば、A列に商品名、B列に商品コードが並んでいるとき、商品コードから商品名を調べたい場合です。VLOOKUPだと検索列（B列）の左にある商品名（A列）は取り出せません。
INDEX+MATCHならこの制限がありません。
=INDEX(A1:A10, MATCH("A003", B1:B10, 0))B列から「A003」の位置を取得し、A列（左側）の値を返しています。検索列と取得列の位置関係を気にしなくてよいのが大きなメリットです。
行と列の両方をMATCHで検索する
INDEX関数に行番号と列番号の両方をMATCHで渡すと、クロス検索ができます。
=INDEX(B2:D10, MATCH("東京", A2:A10, 0), MATCH("4月", B1:D1, 0))A列から「東京」の行位置を取得し、1行目から「4月」の列位置を取得します。交差するセルの値が結果です。大きな集計表から特定のデータを引き出すときに重宝します。
MATCH関数の実務活用パターン
ランク分けの自動判定
得点に応じてランクを自動判定する例です。F列にランク境界値（0, 60, 70, 80, 90）を入れます。G列にランク名（E, D, C, B, A）を用意しておきます。
=INDEX(G1:G5, MATCH(B2, F1:F5, 1))照合の型 1 で「得点以下の最大の境界値」の位置を取得し、対応するランク名を返します。IF関数をネストするより圧倒的にスッキリ書けます。
最終入力行の自動取得
データの最後の行を自動で見つけたいときにも使えます。
=MATCH(9.99E+307, A:A, 1)9.99E+307 はExcelで扱える最大の数値です。照合の型 1 で「最大値以下」を検索すると、数値データの最終行の位置が返ります。
文字列データの場合は COUNTIF関数 などと組み合わせる方法もあります。
重複チェックでの活用
同じ値が複数あるとき、MATCH関数は最初に見つかった位置を返します。この性質を利用して重複チェックに使えます。
=MATCH(A2, A:A, 0)<>ROW(A2)MATCH関数が返す位置と現在の行番号が一致しない場合、その値は上のほうで既に出現しています。結果がTRUEなら重複データです。
IFERROR関数と組み合わせてエラーを防ぐ
MATCH関数は検索値が見つからないと #N/A エラーを返します。IFERROR関数で囲んでおくと安心です。
=IFERROR(INDEX(B1:B10, MATCH(D1, A1:A10, 0)), "該当なし")検索値が見つからない場合に「該当なし」を表示します。
よくあるエラーと対処法
MATCH関数で発生しやすいエラーと、その原因・対処法をまとめました。
| エラー | 原因 | 対処法 |
|---|---|---|
| #N/A | 検索値が範囲内に見つからない | 値のスペルや全角半角を確認。IFERROR関数で囲む |
| #N/A | 照合の型 1 で昇順になっていない | データを昇順に並べ替えるか、照合の型 0 に変更 |
| #N/A | 照合の型 -1 で降順になっていない | データを降順に並べ替えるか、照合の型 0 に変更 |
| #N/A | 文字列の前後にスペースが混入 | TRIM関数で空白を除去してから検索 |
| #VALUE! | 検査範囲が複数行かつ複数列 | 1行または1列の範囲に修正 |
| #REF! | INDEX+MATCHで取得範囲がずれている | INDEX関数の範囲とMATCHの範囲の行数が一致しているか確認 |
#N/A エラーが最も多いトラブルです。「値が合っているはずなのに #N/A が出る」ときは、次の点をチェックしてみてください。
- 全角と半角が混在していないか（SUBSTITUTE関数やASC関数で統一）
- 数値が文字列として入力されていないか（セルの左上に緑の三角が出ていたら文字列格納の可能性）
- 前後に余計なスペースが入っていないか
TIP
LEN関数でセルの文字数を確認すると、見えないスペースが混入しているかどうかを判別できます。
VLOOKUP・XLOOKUP・INDEX+MATCHの違いと使い分け
検索関数はどれを使えばいいか迷いますよね。3つの主要な検索方法を比較してみましょう。
| 比較項目 | VLOOKUP | INDEX+MATCH | XLOOKUP |
|---|---|---|---|
| 検索方向 | 左端列のみ | 自由（左右どちらも可） | 自由（左右どちらも可） |
| 列の追加・削除への強さ | 列番号がずれる場合あり | 列番号に依存しない | 列番号に依存しない |
| 近似値検索 | 第4引数で対応 | 照合の型 1 / -1 で対応 | 一致モードで対応 |
| 複数条件の検索 | 補助列が必要 | 配列数式で対応可 | 補助列が必要 |
| 対応バージョン | すべてのバージョン | すべてのバージョン | Microsoft 365 / Excel 2021以降 |
| 数式の読みやすさ | シンプル | やや長い | シンプル |
どれを選べばいい？
- シンプルに左から右へ検索したい → VLOOKUP関数でOK
- 左方向の検索が必要、または古いExcelを使っている → INDEX+MATCHが最適
- Microsoft 365 / Excel 2021を使っている → XLOOKUP関数が最も便利
INDEX+MATCHは対応バージョンの広さと柔軟性が最大の強みです。XLOOKUPが使えない環境では、INDEX+MATCHを覚えておけば困ることはありません。
まとめ
MATCH関数は、範囲内の値の位置番号を取得する関数です。この記事で紹介したポイントを振り返ります。
- 照合の型 0（完全一致） を基本にする。省略すると 1 になるので明示的に指定する
- INDEX+MATCHの組み合わせ で、VLOOKUPの左方向検索制限を解消できる
- #N/A エラー が出たら、全角半角・スペース混入・並び順を確認する
- XLOOKUPが使えない環境では INDEX+MATCH が最強の検索パターン
まずは照合の型 0 の完全一致検索から試してみてください。INDEX+MATCHの組み合わせに慣れると、データ検索の幅がぐっと広がりますよ。
関連記事
関数一覧
Excel関数の一覧は下記の記事でまとめています。
エラー値について
Excelのエラー値の種類と対処法は、下記の記事で詳しく解説しています。
