MATCHは、指定した値が範囲の何番目にあるかを返す関数です。値そのものではなく「位置」を返すのが特徴です。
単体で使うことは少なく、INDEX や VLOOKUP と組み合わせて力を発揮します。
書式
=MATCH(検索キー, 検索範囲, [検索の型])| 引数 | 内容 |
|---|---|
| 検索キー | 探したい値 |
| 検索範囲 | 探しに行く範囲。1列または1行だけ |
| 検索の型 | 0＝完全一致 / 1＝以下の最大値 / -1＝以上の最小値 |
第3引数には必ず 0 を書いてください。 省略すると 1（近似一致）になり、事故のもとになります。理由は後述します。
基本の使い方
| A | |
|---|---|
| 1 | 東京 |
| 2 | 大阪 |
| 3 | 名古屋 |
| 4 | 福岡 |
=MATCH("名古屋", A1:A4, 0) → 3「名古屋」は範囲の上から3番目にあるので 3 が返ります。A3セルという意味ではなく、範囲の中での順番である点に注意してください。範囲を A2:A4 にすれば、同じ「名古屋」でも結果は 2 になります。
横方向の範囲でも同じように動きます。
=MATCH("5月", A1:M1, 0) → 6INDEXと組み合わせる
MATCHの最も一般的な使い道です。
=INDEX(B2:B10, MATCH("B-201", A2:A10, 0))MATCHで「A列の何番目か」を求め、INDEXでB列の同じ位置を取り出します。これで VLOOKUP と同じ検索ができ、しかも左方向にも検索できます。詳しくは INDEX のページで解説しています。
VLOOKUPの列番号を動的にする
VLOOKUPの列番号を数字で直接書くと、列を挿入したときに壊れます。MATCHで求めれば壊れません。
=VLOOKUP($E2, $A:$D, MATCH(F$1, $A$1:$D$1, 0), FALSE)見出し行から「取り出したい列が何番目か」を計算しているため、列の順番が変わっても追従します。横方向にコピーして複数項目を一度に引くときに特に有効です。
第3引数を省略してはいけない理由
省略すると 1（検索キー以下の最大値を探す）になります。このモードは範囲が昇順に並んでいる前提で動きます。
先ほどの都市名リストで =MATCH("札幌", A1:A4) とすると、「札幌」は存在しないのにエラーにならず、何らかの数字が返ります。並び順が昇順でないため結果は予測できません。間違いに気づけないのが最大の問題です。
近似一致には「点数から評価ランクを求める」といった正当な用途もありますが、意図して使う場合以外は 0 を明示してください。
重複チェックに使う
MATCHは最初に見つかった1件の位置を返します。この性質を使うと、重複の検出ができます。
=IF(MATCH(A2, A:A, 0) = ROW(A2), "初出", "重複")「自分の行番号」と「最初に見つかった位置」が一致していれば初出、ずれていれば2件目以降＝重複、という判定です。列全体を範囲にしているので、位置と行番号が一致する点を利用しています。
単に重複の有無を数えるだけなら COUNTIF のほうが簡単です。
=IF(COUNTIF(A:A, A2) > 1, "重複あり", "")エラーの対処
#N/A が出る
見つからなかった、という意味です。原因はほぼ次のどれかです。
表記ゆれ・余分なスペース
「東京」と「東京 」は別物です。疑わしいときは TRIM で囲みます。
=MATCH(TRIM(E2), A:A, 0)数値と文字列の食い違い
101（数値）と "101"（文字列）は一致しません。片方が左寄せ、片方が右寄せになっていたらこれです。
第3引数を省略している
昇順でない範囲で近似一致モードが働き、期待した結果になっていません。
見つからないケースを許容するなら IFERROR で包みます。
=IFERROR(MATCH(E2, A:A, 0), "")結果がINDEXと1つずれる
MATCHの範囲とINDEXの範囲で、開始行が揃っていないのが原因です。MATCHを A1:A10、INDEXを B2:B10 にすると必ず1つずれます。両方を同じ行から始めてください。
まとめ
- – 書式は
=MATCH(検索キー, 検索範囲, 0) - – 返るのは値ではなく範囲の中での位置
- – 第3引数の
0は必ず書く - –
INDEX + MATCHで柔軟な検索が組める - – VLOOKUPの列番号を動的にするのにも使える
コメントを残す