ExcelなどでMATCH関数を使うとき、「照合の種類（match_type）」の指定が重要になります。正しい照合の種類を選ばないと、意図しない結果になったりエラーが出たりする可能性があります。この記事ではMATCH関数における照合の種類の意味と使い分けを丁寧に解説します。完全一致・近似一致・昇順・降順など、検索目的に応じた正しい使い方を理解できるようになります。
MATCH関数 照合の種類の基本と種類
MATCH関数は、指定した範囲内から検索値を探し、その検索値が範囲内で何番目にあるかを返す関数ですが、第三引数「照合の種類」が検索の挙動を左右します。照合の種類には主に三種類があります。指定できる値は 1、0、‐1 の三つです。省略した場合は既定で 1 が設定されます。照合の種類によって検索範囲が昇順または降順になっている必要があったり、完全一致のみを探したりするなどの条件が変わります。
照合の種類＝1（昇順／以下の最大値）
照合の種類に「1」を指定すると、検索値以下の最大の値（つまり検索値と一致しないときはそれより小さい中で最大の値）の位置を返します。ただし、この動作を利用するには検索対象の範囲（lookup_array）が昇順（小さい→大きい、またはアルファベットであればA→Zなど）で並んでいる必要があります。昇順でない範囲に対して1を使うと誤った結果を返すことがあります。
例えば売上金額ごとに設定された割引率を昇順にリストしておき、購入額が該当する割引率を探したい場合にこのモードが役立ちます。購入額が閾値の間にある場合、閾値以下の最大値に対応する割引率を取得できます。
照合の種類＝0（完全一致）
照合の種類に「0」を指定すると、検索値とする最初の値を探します。このとき検索範囲の並び順は問われません。また、テキスト型の場合にはワイルドカード文字（? や *）を使って部分一致検索をすることも可能です（ただし、ワイルドカードの文字自体を検索したい場合には、その前にチルダ記号を付ける必要があります）。検索値が見つからない場合はエラー（#N/A）が返されます。
データのIDや名前など、一致しなければ意味がない検索にはこのモードを使います。誤って近似一致（1や‐1）を使ってしまうと、意図しない値を返すことがあるため、完全一致が必要な場合は必ず「0」を指定します。
照合の種類＝‐1（降順／以上の最小値）
照合の種類に「‐1」を指定すると、検索値以上の最小の値（検索値と一致しないときはそれより大きい中で最小の値）の位置を返します。ただし、この動作を使うには検索範囲が降順（大きい→小さい）である必要があります。降順に並べられていない場合、結果は正しくないかエラーになることがあります。
例えばある基準より上の最小値を探したい場面、例えばある評価得点に対して超える最小の評価を見つけたいときなどに有効です。ただし、このモードを使うケースは比較的少なく、昇順・検索値‐閾値の扱いを誤ると結果が予想外になるため注意が必要です。
MATCH関数 照合の種類を使い分ける実践例
照合の種類「1」「0」「‐1」の違いを適切に使い分けることは、正しいデータ抽出を行うために不可欠です。ここでは実際の用途別にどの照合の種類を選べばよいかを具体的に示します。検索目的に応じて照合の種類を選択できるようになります。
料金区分や範囲判定で使うなら「1」
料金区分やグレード判定など、検索値に近い但し超えない「下限」の値によって区分を決めるような場面では、「1」が有効です。例えば「売上額に応じて10,000以上で20％割引」「5,000以上で10％割引」といった区分設定で、購入額が 6,000 のときに「5,000以上」区分を選びたい場合、「1」を指定した MATCH を使います。
このとき、区分値のリストは昇順に並べておくことが条件です。昇順でないと予期しない位置が返ってもエラーとはならず誤った値を返すので、テストが必要です。
一致しなければ無効にしたいなら「0」
ID やコード、完全一致の名前など、正確な一致がないと意味がない検索対象を扱う場面では「0」を指定します。曖昧な一致は避けたいとき、違うものが混じる可能性があるときに使用されます。テキストの前後に余分な空白が入っていないか、型が文字列型か数値型かを確認することが重要です。
ワイルドカードを使えば「部分一致」も可能ですが、それは完全一致モード中の例外的な機能です。基本的には見つかるか見つからないかが明確な場面で使うモードです。
上限に近い値を探したいなら「‐1」
「‐1」は検索値以上の最小値を探すため、しきい値を超える最小のカテゴリーを割り当てたいときなどに使います。例えば「80点以上はA」、「70点以上はB」などと判定するが、基準を超える者のみ最上のグレードを与えるような場面で使えます。
ただし最高得点以上の値が検索範囲に存在しなければエラーになります。また検索範囲は降順で整列しておく必要があり、データの順序が揺れていると結果が予想外になります。
MATCH関数とXMATCH関数の違い／互換性
Excel の新しいバージョンには MATCH に似た XMATCH 関数があり、照合や検索のモードに関する追加機能があります。XMATCH は既定で完全一致モードが使われる点や、検索モードを使って先頭から／末尾から／二分探索などを指定できる点で柔軟性が高いです。既存の MATCH を使い続ける場合は、XMATCH で同等の動きをする引数を把握しておくとよいです。
XMATCH が持つ追加の一致モード
XMATCH 関数では一致モードとして「完全一致」「ワイルドカード一致」「floor／ceiling（近似一致）」などがあり、MATCH よりオプションが豊富です。たとえば一定の範囲内で「その値以上の最小値」「以下の最大値」を探すなど、MATCH で‐1／1を使うような使い分けに加えて、模範的な動作が明確に決められています。
MATCH と XMATCH の照合パラメータの対応関係
MATCH の「1」は昇順での「以下の最大値(floor)」、XMATCH では一致モードの中でfloorに対応するモードを使います。MATCH の「‐1」は降順での「以上の最小値(ceiling)」に対応します。ただし XMATCH では既定が完全一致であり、MATCH の既定とは異なるため、単純置き換えが不適切な場合があります。
MATCH を使い続ける場合の注意点
既存のブックで MATCH 関数が使われている場面では、上記の照合の種類がどれであるかを明確に把握しておくことが重要です。特に「照合の種類」が省略されていた場合は既定で「1」が設定されるため、完全一致を期待していたのに「以下の最大値」が返る可能性があります。データの並び順にも注意が必要です。
MATCH関数 照合の種類でよくある間違いとその対処方法
MATCH関数を使う際にはよくある誤りがいくつかあり、それらを回避しないと結果が期待と異なることになります。ここでは典型的な失敗ケースと、その回避策を紹介します。これを理解すればトラブルを未然に防ぐことができます。
照合の種類を省略して誤るケース
MATCH関数の第三引数を省略すると、照合の種類は 既定で「1」が設定されます。つまり完全一致ではなく「検索値以下の最大値」が検索されます。検索範囲が昇順でないと誤った値を返すことがあり、たとえ完全一致を期待していても予期しない近似一致が返ることがあります。
検索範囲の並び順が不適切なケース
照合の種類が「1」の場合は検索範囲を昇順に、「‐1」の場合は降順に整えておく必要があります。これら順序が正しくないと、MATCH 関数はエラーを出すか、間違った位置を返します。特にデータが混合型（数値と文字列など）である場合、並び順が見た目と異なることがあるため、注意が必要です。
型の不一致やテキストのスペースなどのケース
検索値と検索範囲内の値で、数値か文字列か、また余分なスペースが含まれていないかを確認してください。たとえば「123」が文字列、「123」が数値の型を持つときは一致しません。完全一致モード（0）を使う場合、特にこのような型の違いや余分なスペースにより #N/A エラーになることがあります。
ワイルドカード使用時の注意点
ワイルドカード（? や *）は完全一致モード（0）でのみ機能します。他の照合の種類ではワイルドカードはリテラル文字扱いになるか、使えません。また、ワイルドカード自体を検索文字として扱いたい場合には、ワイルドカードの前にチルダ記号をつける必要があります。
表で比較：照合の種類による動作の違い
以下の表は、照合の種類 1、0、‐1 の動作・条件・エラーが起きるケースを整理したものです。用途ごとにどの種類を使えばよいかが一目で分かります。
| 照合の種類 | 目的 | 検索範囲の並び | 返される値の意味 | 検索値が範囲外の時 |
|---|---|---|---|---|
| 1 | 検索値以下の最大値を見つけたい時（料金区分・階級など） | 昇順に整列 | 完全一致があればその位置、なければ検索値より小さい値で最も近い位置 | 検索値より小さい値がないときエラーになる |
| 0 | 完全一致のみ必要な場合（ID・コード・名前など） | 任意の順序で良い | 最初に一致する位置 | 見つからない → #N/A エラー |
| ‐1 | 検索値以上の最小値を見つけたい時 | 降順に整列 | 完全一致があればその位置、なければ検索値より大きい最小の値の位置 | 検索値より大きい値がないときエラーになる |
MATCH関数 照合の種類の使い方Tipsとパフォーマンスへの影響
照合の種類を正しく使うことだけでなく、使い方の細かなコツを抑えておくとMATCH関数をより便利に活用できます。またデータ量が多い場合の速度やExcelのバージョンによる挙動の違いも理解しておくとよいです。
パフォーマンスを意識した範囲指定
検索範囲（lookup_array）は可能な限り必要最低限のセルに絞ることが望ましいです。範囲が長い、または大規模な表を対象にすると探索に時間がかかることがあります。特に「近似一致」モード（1 や ‐1）では、内部的な二分探索的アルゴリズムが使われることがあり、範囲の並びやデータ型の不一致が原因で想定外の動作や遅延が発生する可能性があります。
Excelのバージョン差と既存ファイルの対応
最新バージョンの Excel では XMATCH 関数が使えるようになっており、MATCH よりも柔軟に一致モードと検索モードを制御できます。既存のファイルで MATCH を使っている場合には、照合の種類が何になっているか、またそれに対応する並び順が正しいかを確認した上で、必要なら XMATCH に置き換えることを検討するとよいでしょう。
エラー処理と結果の検証
MATCH 関数で一致しない場合は #N/A エラーが返ります。IFNA 関数などと組み合わせて「該当なし」などの処理を入れると実務で使いやすくなります。また結果が正しいかどうかは、テストデータで意図的に境界値（閾値直前・直後・一致する・一致しない）を用意して確認することをおすすめします。
ワイルドカード／テキスト扱いの注意
照合の種類 0 を使う場合に限り、ワイルドカードの ? や * を使って部分一致検索があります。ただしワイルドカード文字を検索対象とする場合は、前にチルダ記号を付ける必要があります。またテキスト型と数値型の混在や文字列内の余分なスペースが思いがけない #N/A エラーを引き起こすことがあります。
実際に使うならこのように記述する
MATCH 関数の構文は次のようになります。検査値（lookup_value）、検査範囲（lookup_array）、照合の種類（match_type）の三つの引数を使います。ただし照合の種類は省略可能で、省略すると「1」が既定となります。
=MATCH(検査値, 検査範囲, 照合の種類)
例１：完全一致を探す場合
=MATCH(“商品A”, A2:A10, 0)
例２：検索値以下の最大値を探す（昇順）
=MATCH(6000, B2:B10, 1)
例３：検索値以上の最小値を探す（降順）
=MATCH(80, C2:C10, ‐1)
まとめ
MATCH関数の「照合の種類」は、検索値をどのような基準で比較するかを決定する非常に重要な要素です。完全一致が必要な場面では「0」、区分・階級などのしきい値判定には「1」または「‐1」を使い分けることがポイントになります。検索範囲の並び順やデータ型・空白・ワイルドカードの取り扱いに注意すれば、意図した結果を得ることができるでしょう。
また Excel の最新機能である XMATCH は MATCH に比べて柔軟で使いやすいため、新規ファイルや新しいバージョンでは XMATCH の利用を検討する価値があります。今後も MATCH 関数を使いこなして、検索の目的に合わせた正しい使い方を身につけてください。
コメント