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でもっとも事故が多いのが、第三引数の省略です。この引数は照合の種類を指定するもので、省略すると近似一致になります。近似一致は、範囲が昇順に並んでいることを前提に、指定した値以下の最大値の位置を返します。
問題は、範囲が昇順に並んでいない場合です。この場合でもエラーにはならず、もっともらしい数字が返ります。何番目かという答えが返ってくるので、一見すると正しく動いているように見えます。しかし実際には、まったく関係のない行の位置が返っていることがあります。
INDEXと組み合わせている場合、この誤りは連鎖します。MATCHが誤った位置を返し、INDEXがその位置の値を返すので、結果として無関係な値が表示されます。それでもエラーは出ません。検算をしていなければ、間違いに気づかないまま使い続けることになります。
対処は単純で、完全一致を意味するゼロを必ず書くことです。書き忘れを防ぐには、MATCHを書くときは常に三つの引数を書くと決めてしまうのが確実です。近似一致が本当に必要な場面、たとえば料金区分の判定などでは、意図して指定すればよいだけです。
逆順に並んだ範囲に対して、指定した値以上の最小値を探すモードもあります。使う場面は限られますが、降順の表を扱うときには必要になります。
探せない原因を切り分ける
MATCHが該当なしのエラーを返すとき、原因は限られています。順に確かめれば必ず見つかります。
最初に疑うのは、値の型です。見た目が同じでも、片方が文字列で片方が数値なら一致しません。他のシステムから貼り付けたデータでは頻繁に起こります。表示が左寄せなら文字列、右寄せなら数値というのが既定の挙動なので、まずそこを見てください。
次に疑うのは、前後の空白です。末尾に半角スペースが入っているだけで一致しなくなります。人の目にはまったく同じに見えるため、これがもっとも気づきにくい原因です。文字数を数えれば一目で分かります。
三つ目は、改行の混入です。フォームの回答やメールから貼り付けたデータには、セルの中に改行が入っていることがあります。セルの高さが妙に高い場合は疑ってください。
四つ目は、範囲の指定です。探したい値が範囲の外にあれば、当然見つかりません。範囲を絶対参照で固定し忘れていて、下にコピーするうちに範囲がずれている、というのもよくあるパターンです。
五つ目は、第三引数の指定漏れです。完全一致を指定していないために、意図しない挙動になっている可能性があります。
この五つを順に潰せば、原因は特定できます。当てずっぽうで数式を書き換えるより、確実に早く終わります。
列番号を動的にする
MATCHの実務的な使い道として、VLOOKUPの列番号を自動化する方法があります。VLOOKUPの第三引数に固定の数字を書いていると、表の列を挿入したときに参照先がずれます。ここをMATCHで置き換えると、見出しの名前で列を探すようになるため、列の位置が変わっても壊れません。
具体的には、見出し行を範囲としてMATCHで探し、返ってきた位置をVLOOKUPの列番号に渡します。数式は長くなりますが、保守の手間は明らかに減ります。マスタの列構成が変わる可能性があるなら、最初からこの形で書いておく価値があります。
同じ考え方で、INDEXの行番号と列番号を両方MATCHで求めれば、二次元の検索ができます。行の見出しと列の見出しから、交差する位置の値を取り出す形です。集計表から特定の値を引っ張ってくるときの定番になっています。
さらに応用として、複数の列のうち条件に合うものを選ぶ、という使い方もできます。月ごとの列が並んだ表から、指定した月の列だけを対象に集計する、といった処理です。
重複を調べるのに使う
MATCHは、値が存在するかどうかの判定にも使えます。該当があれば数字が返り、なければエラーが返るという性質を利用します。
ISNUMBER関数で包めば、存在するかどうかが真偽値で返ります。これをIFの条件にすれば、リストに含まれているかどうかで処理を分けられます。二つのリストを突き合わせて、片方にしかない項目を洗い出す、という作業がこれでできます。
COUNTIFでも同じことができますが、違いがあります。COUNTIFは件数を数えるので、範囲全体を走査します。MATCHは最初に見つかった時点で止まります。行数が非常に多い場合、MATCHのほうが速くなることがあります。
一方で、何件あるかを知りたい場合はCOUNTIFが適しています。存在の有無だけならMATCH、件数まで必要ならCOUNTIF、という使い分けになります。
なお、MATCHは最初に見つかった位置しか返しません。同じ値が複数ある場合、二件目以降の位置は分かりません。全件の位置を知りたい場合は、別の方法を考える必要があります。
二つのリストを突き合わせる
実務でよく発生するのが、二つの一覧を突き合わせて差分を見つける作業です。名簿の照合、在庫と台帳の確認、送付先の重複チェック。位置を求める関数がそのまま使えます。
考え方は単純で、片方の一覧の各行について、もう片方に存在するかを判定します。存在すれば位置が返り、なければ該当なしのエラーが返ります。この性質をそのまま利用します。
エラーかどうかを判定する関数で包めば、真偽値になります。これを作業列に出せば、片方にしかない項目が一目で分かります。
両方向で確認してください。Aにあってbにないもの、Bにあってaにないもの。片方だけ見ても、全体の差分は分かりません。
突き合わせの精度は、キーの整備で決まります。前後の空白、全角と半角、表記のゆれ。これらが揃っていないと、実際には同じものが別扱いになります。突き合わせる前に、両方の一覧を整形してください。
複数の項目で照合する場合は、それらを連結したキーを作ります。氏名だけでは同姓同名が区別できませんが、氏名と生年月日を連結すれば精度が上がります。
差分が見つかったら、どちらが正しいかを判断する必要があります。この判断は業務のルールによるので、機械的には決められません。差分の一覧を出すところまでが数式の役割です。
検索の位置を再利用する
同じ検索を複数の列で繰り返している場合、位置を一度求めて使い回すほうが効率的です。
位置を求める列を一本作り、各行でその位置を求めます。以降の列は、その位置を使って値を取り出すだけです。検索の処理が一回で済むので、行数が多い表では速度に差が出ます。
読みやすさの面でも利点があります。検索の部分と取り出しの部分が分かれるので、どこで失敗しているかが分かります。位置の列がエラーになっていれば検索の問題、位置は正しいのに値がおかしければ取り出しの問題です。
位置の列は非表示にできるので、表の見た目を損ないません。
同じ考え方で、条件の判定も一度だけ行って使い回せます。複数の場所で同じ条件を書いているなら、作業列にまとめてください。条件が変わったときの修正も一箇所で済みます。
処理を分けて中間結果を持つ、という発想は、表計算全般で有効です。一本の長い数式より、短い数式を段階的に並べたほうが、速く、読みやすく、直しやすくなります。
よくある質問
Q. 返ってくる数字は行番号ですか
シートの行番号ではなく、指定した範囲の中での順番です。範囲が二行目から始まるなら、一が返れば二行目という意味になります。
Q. 大文字と小文字は区別されますか
区別されません。区別したい場合はEXACT関数と組み合わせる必要があります。
Q. 部分一致で探せますか
第三引数を完全一致にした場合、ワイルドカードが使えます。前方一致や部分一致で位置を探すことができます。
Q. 該当なしのエラーを消したいのですが
IFERRORで包むか、IFNAで包みます。IFNAは該当なしのエラーだけを対象にするので、他のエラーは隠れません。
Q. 範囲は複数列でもいいですか
MATCHの範囲は一行または一列である必要があります。複数行かつ複数列の範囲を渡すとエラーになります。
Q. 逆から探せますか
標準ではできません。範囲を逆順にした作業列を作るか、他の関数と組み合わせる必要があります。
Q. INDEXと組み合わせると重くなりますか
範囲を適切に絞っていれば問題ありません。列全体を指定していると、行数の多いシートでは負荷がかかります。
Q. XMATCHという関数があると聞きました
MATCHの後継にあたる関数で、逆方向の検索やワイルドカードの指定がしやすくなっています。使える環境なら、そちらのほうが書きやすくなります。
まとめ
- 書式は
=MATCH(検索キー, 検索範囲, 0) - 返るのは値ではなく範囲の中での位置
- 第3引数の
0は必ず書く INDEX + MATCHで柔軟な検索が組める- VLOOKUPの列番号を動的にするのにも使える