MATCH関数について

ここから少し趣の違う関数を扱います。

これまで学んできた検索系の関数(VLOOKUP / XLOOKUP)は「値そのものを取ってくる」のが仕事でした。

今回の MATCH関数(マッチ関数) は、ちょっと変わっています。値ではなく「位置」を返す 関数なのです。

「営業部」は、この列の 何番目 にあるか?
「のぞみ」は、見出し行の 何列目 にあるか?
「P003」は、商品コードリストの 上から何番目 か?

最初は「位置だけ返されても、何に使うの?」と思うかもしれません。

実はこの「位置を返す」性質が、他の関数と組み合わせたときに 化ける のです。今回の終盤でその実用例をご紹介します。

MATCH関数の構文

=MATCH(検索値, 検索範囲, 照合の種類)

検索値:何を探すか
検索範囲:どの範囲から探すか(1行か1列の細長い範囲を指定)
照合の種類:0=完全一致 / 1=次に小さい値 / -1=次に大きい値

検索範囲は、1行または1列の細長い範囲 だけが指定できます。表全体(複数列・複数行)は指定できないので注意してください。

照合の種類は、まずは 0(完全一致) だけ覚えればOKです。

具体例で見てみる

商品コードリストがあって、「P003」が上から何番目にあるかを調べたいとき。

:::excel
A1=商品コード
A2=P001
A3=P002
A4=P003
A5=P004
A6=P005
:::

=MATCH("P003", A2:A6, 0)

検索値:"P003"
検索範囲:A2:A6(商品コードの列)
照合の種類:0(完全一致)

結果は 3 が返ります。

「P003 は、A2:A6 の中で 3番目 にある」という意味です。値ではなく、位置(=何番目か)が返るのが MATCH の特徴です。

横向きにも使える

検索範囲を「1行」にすれば、横向きにも使えます。たとえば、見出し行から「単価」が何列目かを探すケース。

:::excel
A1=商品コード
B1=商品名
C1=単価
D1=在庫
:::

=MATCH("単価", A1:D1, 0)

結果:3

「単価」は A1:D1 の中で 3番目(=C列)にある、ということが分かります。

VLOOKUP の「列番号」を頭の中で数えていたあの作業、これを MATCH がやってくれます ── という話につながっていきますが、それは後ほど。

見つからないと #N/A

存在しないものを探すと、#N/A エラーになります。

=MATCH("P999", A2:A6, 0)   ← P999 はリストにない

→ #N/A

これも、必要なら IFERROR で包んで対処できます。

=IFERROR(MATCH("P999", A2:A6, 0), "リストにありません")

単独で使う:重複チェック

MATCH は単独でも使い道があります。代表的なのが 重複チェック です。

「このリスト、同じ商品コードが複数入っていないか?」を確認したいとき、MATCH は「最初に見つかった位置」を返します。同じ値が複数あっても、返ってくるのは最初の1つだけ。

これだけだと重複は見つけられませんが、レッスン034で学んだ COUNTIF と組み合わせると、こうなります。

=IF(COUNTIF(A:A, A2)>1, "重複あり", "")

ここでは COUNTIF のほうが直接的なので、重複検出には COUNTIF が定番です。MATCH 単独の使いどころとしては、次の例のほうが分かりやすいかもしれません。

単独で使う:リストの並び順チェック

「このリストは昇順に並んでいるか?」を確認するのに、MATCH の照合の種類 1(次に小さい値) を使うテクニックがあります。

ただ、これも少し玄人向けの使い方。MATCH の真価は、やはり 他の関数の引数として使う ところにあります。

ここからが、今回のレッスンの本題です。

VLOOKUP × MATCH:新幹線料金表

ここで、MATCH の真骨頂を見ていただきます。

東京から各駅までの新幹線料金表を考えます。停車駅の種類によって料金が違うので、こんな表になっています。

:::excel
A1=駅
B1=のぞみ
C1=ひかり
D1=こだま
A2=新横浜
B2=1760
C2=1530
D2=1300
A3=名古屋
B3=11300
C3=10560
D3=9870
A4=京都
B4=14170
C4=13320
D4=12640
A5=新大阪
B5=14720
C5=13870
D5=13190
:::

「名古屋までの、ひかりの料金は?」を取り出したいとします。

VLOOKUP で書くなら、こうですね。

=VLOOKUP("名古屋", A:D, 3, FALSE)

「列番号は3」と数えています。これでも動きますが、もし表に新しい列(たとえば「グリーン車」など)が追加されたら、列番号がズレてしまいます。

そこで、列番号の部分を MATCH に置き換え ます。

=VLOOKUP("名古屋", A:D, MATCH("ひかり", A1:D1, 0), FALSE)

VLOOKUP で「名古屋」の行を探す
列番号は、MATCH が「ひかり」を見出し行から探して、その列の位置を返す

結果:10560

「列番号は3」と固定で書く代わりに、「ひかり」という列の名前で動的に位置を取る。これが MATCH の使いどころです。

さらに便利にする:検索値もセル参照に

実務では、検索値を直接書かず、セル参照にします。

F1:駅名を入力(例「名古屋」)
F2:列車種別を入力(例「ひかり」)
F3:料金を表示

F3 のセル: =VLOOKUP(F1, A:D, MATCH(F2, A1:D1, 0), FALSE)

F1 と F2 を変えるだけで、F3 の料金が自動で切り替わります。

F1="京都", F2="のぞみ" → 14170
F1="新大阪", F2="こだま" → 13190
F1="新横浜", F2="ひかり" → 1530

駅も列車も自由に切り替えできる「料金検索ツール」 が、関数2つで完成しました。

XLOOKUP と比べると?

「あれ、XLOOKUP なら列番号を数えなくてよかったよね?」と思った方、鋭いです。

XLOOKUP で同じことを書くなら、こうなります。

=XLOOKUP(F1, A:A, XLOOKUP(F2, A1:D1, A:D))

XLOOKUP を入れ子にすれば、確かに同じことができます。ただ、入れ子になると少し読みにくいですね。

VLOOKUP × MATCH の組み合わせは、XLOOKUP 登場前から使われてきた「列を動的に決める」定番テクニック です。

古くからのExcelファイルでよく見かける書き方なので、「こういう書き方もあるんだな」と知っておくと、他人の作ったファイルを読むときに役立ちます。

新規で書くなら、XLOOKUP のほうが素直です。ただ、VLOOKUP × MATCH のほうが「何をしているか」が言葉で説明しやすい、という利点もあります。

まとめ

MATCH関数は、値ではなく「位置(何番目か)」を返す
構文:=MATCH(検索値, 検索範囲, 照合の種類)
検索範囲は 1行または1列の細長い範囲 だけ
照合の種類は、まずは 0(完全一致) を基本に
単独でも使えるが、他の関数の引数として組み合わせる のが本領
VLOOKUP の 列番号を MATCH で動的に決める のが定番テク

次回は、MATCH の相棒ともいえる INDEX関数 です。MATCH が「位置」を返すのに対して、INDEX は「位置から値を取り出す」。

2つを並べて学ぶと、Excel の検索系関数の全体像が、ぐっと立体的に見えてきます。

ショートカット

※このレッスンに対応するショートカットはありません。