MATCH関数は、指定した値が一列または一行の範囲で何番目にあるかという「相対位置」を返します。基本形は=MATCH(検索値,検索範囲,照合の種類)です。商品コードなどの完全一致は、第三引数に0を明示して=MATCH(E2,A2:A100,0)とします。第三引数を省略すると1になり、昇順に並べた範囲で近似一致を行うため、未整列データではもっともらしい誤結果になる危険があります。MATCHは値そのものではなく位置を返すので、対応する値を取り出すときはINDEXと組み合わせます。新しいExcelでは完全一致が既定のXMATCHも選べますが、配布先の版を確認してください。
MATCHが返すのは値ではなく相対位置
=MATCH("商品B",A2:A10,0)で商品BがA2:A10の三番目にあれば、結果は3です。ワークシート上の行番号4ではありません。検索範囲の先頭を1として数えた相対位置です。この結果をINDEXの行番号引数へ渡すと、同じ位置にある別列の値を取り出せます。
検索範囲lookup_arrayは、基本的に一行または一列で指定します。表全体を渡して二次元位置を一度に求める関数ではありません。行見出しと列見出しを別々のMATCHで検索すれば、INDEXの行番号と列番号として二方向検索に使えます。まず単独のMATCHで期待位置を確認してから組み合わせます。
完全一致では第三引数0を明示する
社員番号、商品コード、メールアドレスなど一意キーは、=MATCH(F2,$A$2:$A$100,0)とします。0は完全一致を探し、範囲の並び順を問いません。見つからない場合は#N/Aを返します。数式をコピーするなら検索範囲を絶対参照へ固定し、検索値セルだけを行ごとに動かします。
第三引数を省略すると既定値は1です。完全一致を意図して省略すると、該当値がないときに近い別位置が返る可能性があります。古い例にMATCH(E2,A:A)があっても目的を確認し、完全一致なら0を追加します。#N/Aを無条件で空白にせず、未登録なのかデータ不整合なのかを区別します。
近似一致1は昇順の範囲で使う
match_typeが1または省略の場合、MATCHは検索値以下の最大値を探します。lookup_arrayは小さい順、AからZ、FALSEからTRUEなどの昇順に並べる必要があります。料率表や点数帯で、下限値0、50、80、90のように昇順へ置き、該当する段階の位置を返す用途に向きます。
未整列の範囲へ1を使うと、エラーではなく誤った位置を返すことがあります。並べ替えが利用者操作で崩れる表では、近似一致を使わず完全一致や専用の検索表を検討します。検索表へ重複する境界や空白を置かず、最小値未満の入力がどうなるかをテストします。
近似一致-1は降順の範囲で使う
match_typeが-1の場合、MATCHは検索値以上の最小値を探し、lookup_arrayは大きい順、ZからAなどの降順が必要です。上限値を基準に段階を探す設計で使えますが、1と-1は意味と整列方向を混同しやすいため、検索表の見出しへ「下限・昇順」「上限・降順」と明記します。
同じ業務ルールを昇順と降順の二種類で作らず、組織内で一つの方式へ統一すると保守しやすくなります。境界ちょうど、境界の直前・直後、範囲外の最小・最大をテストします。並べ替え後に数式だけが残っても意図が分かるよう、match_typeを省略しません。
重複があると最初の一致位置を返す
完全一致の範囲に同じ値が複数ある場合、MATCHは最初に見つかった相対位置を返します。「最新の注文」「最後の履歴」を探す式としてそのまま使うと、最古の行など意図しない値になる場合があります。キーが一意であるべき表ではCOUNTIFで重複を検査し、重複原因を修正します。
重複が正当で最後の一致が必要なら、新しいExcelのXMATCHで逆方向検索を指定する、FILTERですべて返す、日付を含む複合条件を設計するなど、要件に合う方法を選びます。検索範囲を逆順へ並べ替える回避策は他の利用者へ影響するため、元データを無断で変更しません。
文字列の比較とワイルドカード
MATCHは通常、文字列の大文字と小文字を区別しません。完全一致のmatch_type 0では、検索値に*と?のワイルドカードを使えます。*は任意の文字列、?は任意の一文字です。実際の*または?を探すには~を前へ付けます。コード検索ではワイルドカードが偶然含まれないよう、入力値を確認します。
見た目が同じでも、全角・半角、先頭・末尾空白、改行、非表示文字、数値と数値文字列の違いで一致しない場合があります。LEN、TRIM、CLEAN、ISTEXT、ISNUMBERなどで切り分け、正規化は補助列で行います。大文字・小文字を厳密に区別する要件では、EXACTなど別の比較方法を組み込みます。
INDEXと組み合わせて対応値を返す
A列の商品コードを探し、C列の価格を返す例は=INDEX(C2:C100,MATCH(F2,A2:A100,0))です。MATCHがA2:A100内の相対位置を返し、INDEXがC2:C100の同じ位置から値を返します。二つの範囲は同じ行から始まり、同じ行数であることを確認します。
行の挿入や列の移動に比較的強い組み合わせですが、範囲ずれ、重複キー、未登録、外部リンクには注意が必要です。数式を一度に作らず、MATCH単独で位置、INDEXへ固定番号を入れて値、最後に組み合わせる順で検証します。IFERRORで空白へ隠す前に#N/Aの件数を管理します。
二方向検索では行と列を別々に探す
縦に担当者、横に月が並ぶ表では、=INDEX(C3:N20,MATCH(Q2,B3:B20,0),MATCH(Q3,C2:N2,0))のように、行見出しと列見出しを別のMATCHで検索します。INDEXの配列範囲と、行・列の見出し範囲の開始位置を正しくそろえます。
見出しに同じ月名や担当者名が重複すると最初の位置が選ばれます。日付を「7月」と表示していても内部値が年ごとに違う、逆に文字列の7月が重複する場合があります。年月の実値や一意IDを使い、交点の代表値を手作業で照合します。
XMATCHを使う場合の違い
XMATCHも値の相対位置を返しますが、完全一致が既定で、検索方向やワイルドカード、近似一致を引数で指定できます。末尾から検索でき、二分探索モードもあります。二分探索は指定方向に正しく並べた範囲が前提で、未整列では無効な結果になるとMicrosoft公式資料が注意しています。
XMATCHはMicrosoft 365や比較的新しい買い切り版で利用できますが、古いExcelでは#NAME?になる場合があります。社外配布や長期保管では利用者の版を確認し、必要ならMATCHへ戻します。新関数を使う理由、match_mode、search_modeを数式の説明へ残します。
#N/Aと誤位置を分けて検証する
#N/Aは見つからないことを示します。検索値の空白、型、スペル、範囲、外部データ更新を確認します。一方、近似一致で誤位置が返るとエラーが出ないため、より危険です。完全一致を期待する用途で第三引数が0か、近似一致の検索表が正しく整列しているかを優先して点検します。
テストには先頭、中央、末尾、未登録、重複、空白、数値文字列、境界値を含めます。返った位置へINDEXを適用した値だけでなく、元範囲の該当セルを確認します。検索表の更新者、更新日、並べ替えルールを記録し、結果を値貼り付けして検索根拠を消さないようにします。
確認チェックリスト
- MATCHが検索値ではなく範囲内の相対位置を返すと理解する
- 完全一致ではmatch_type 0を省略せず指定する
- 近似一致1は昇順、-1は降順の整列を確認する
- 重複時は最初の一致になることを確認する
- INDEXの戻り範囲とMATCHの検索範囲を同じ行位置にそろえる
- XMATCHを使う場合は配布先のExcel版と検索モードを確認する
コメント