Excel(エクセル)の関数の中で、MATCH関数(マッチ関数)は認知度が低めのマイナーな関数という印象もあります。
しかし、エクセルを熟知している人にとっては、よく利用される重要な関数の一つです。
MATCH関数は、探したい値が表の中でどの位置にあるかを知りたい時に役立つ関数です。
この記事では、MATCH関数の基本的な使い方から、VLOOKUP関数など別の関数と組みわせた応用編の使い方まで幅広く解説します。
MATCH関数(マッチ関数）とは？
MATCH関数(マッチ関数）は、指定した検索範囲内で、数値や文字列などの検索値が、どの位置にあるかを数値で返す関数です。
MATCH関数を使用することで、探したい値が表の何行目(何列目）にあるのかを調べることが出来ます。
例えば、下の表の「A、B、C、D」という文字の「B」の位置を調べるために、MATCH関数を使うと、左から２番目にあるので、「２」という結果が得られます。
このように、表の特定の位置にある文字を抽出したりする際に役立つ関数です。特に、VLOOKUP関数やINDEX関数と組み合わせてよく利用されます。
MATCH関数の書式と引数
MATCH関数の書式は以下のとおりで、３つの引数(※)で構成されています。
＝MATCH(検査値,検査範囲,[照合の型])
※引数(ひきすう)とは、エクセルで関数を使用する際に必要な情報のことをいいます。関数が正しい結果を算出するための入力項目だと考えてください。関数名の後のカッコ内に引数を入力します。また、引数には、入力必須のものと、そうでないものがあります。
先述のとおり、MATCH関数は、指定した検索範囲内(検査範囲内)で、数値や文字列などの検索値(検査値)が、相対的にどの位置にあるかを数値で返す関数です。
構文は、「＝MATCH(検査値,検査範囲,[照合の型])」となっていて、MATCH関数では引数を３つ指定する必要があります。
それぞれの引数について詳しく解説します。
MATCH関数/第1引数：検査値(検索値)
１番目の引数「検査値」は「検索値」のことで、どの文字を探したいかを指定します。
この引数は必ず設定が必要で、検索した値やセルを参照して指定します。文字列などの値を指定する時は、”〇〇〇”というようにダブルクォーテーションで囲って使用します。
検索値は２番目の引数「検査範囲(検索範囲)」の中から検索されるので、この範囲内に探したい文字が含まれている必要があります。
３番目の引数「照合の型」が「0」の場合は、「検査値」に疑問符(？)やアスタリスク(*)などのワイルドカードも使用することが出来ます。
MATCH関数/第2引数：検査範囲(検索範囲)
２番目の引数「検査範囲」は「検索範囲」のことで、どのセル範囲から検査値を探したいかを指定します。
検索範囲の中から、検索値の相対的な位置を返す関数であるため、検索範囲は、「B2:B9」や「B2:E2」というように、１行もしくは１列で指定する必要があります。
MATCH関数/第3引数：照合の型
３番目の引数は「照合の型」で「検索値の探し方」のことです。
「照合の型」は、「-1」「0」「1」のいずれかを指定し、「0」であれば引数の指定は省略できます。
「照合の型」は、基本的に「0」(もしくは省略)で利用されることが非常に多いと覚えておきましょう。
| 照合の型 | 検索値の探し方 |
| 1 | 検索値以下の中から最大値を検索します。 そのために検索範囲を昇順に並び替えが必要です。 |
| 0 | 検索値と完全一致する最初の値を検索します。 |
| -1 | 検索値以上の最小値を検索します。 そのために検索範囲を降順に並び替えが必要です。 |
【基本編】MATCH関数の使い方
上の表を使って、MATCH関数の基本的な使い方を解説します。
この表の中から、特定の文字が何行目にあるか、何列目にあるかを求めます。この見出しで引数の使い方を覚えましょう。
事例１：検索値が「何列目」かを調べる
下のG3セルにMATCH関数を挿入して、「鳥取県」が表の一番上から何行目にあるかを調べてみましょう。
G3セルに、以下のMATCH関数の数式を挿入します。
=MATCH(F3,C3:C8,0)
１つ目の引数「検査値」⇒「鳥取県」の位置を探したいので「F3」セルを指定
２つ目の引数「検査範囲」⇒「鳥取県」を含んだ「地域」列の「C3:C8」を指定
３つ目の引数「照合の型」⇒「鳥取県」と完全に一致するセルの位置を探すため「0」を指定(省略も可)
その結果、MATCH関数で返された数値は「4」となり、検索範囲の中で上から４行目の位置にあることが分かりました。
事例２：検索値が「何列目」かを調べる
今度は、MATCH関数を使って、行見出しの「地域」という文字が表の何列目の位置にあるかを調べてみます。
G5セルに、以下のMATCH関数を挿入します。
=MATCH(F5,B2:D2,0)
１つ目の引数「検査値」⇒「地域」の位置を探したいので「F5」セルを指定
２つ目の引数「検査範囲」⇒「地域」を含んだ「項目」行の「B2:D2」を指定
３つ目の引数「照合の型」⇒「地域」と完全一致するセルの位置を探すため「0」を指定
MATCH関数で返された数値は「2」となり、検索範囲の中で左から２列目の位置にあることが分かりました。
注意点：検索範囲に同じ値が複数存在する場合
検索範囲に検索値が複数存在する場合、MATCH関数を使う際に注意が必要です。
例えば、下の表のC列には「和歌山県」という文字がC5セルとC8セルの２つにあります。このように、検索範囲に検索値が複数存在する場合があります。
試しに、「和歌山県」の相対的な位置を調べるために、MATCH関数「=MATCH(F7,C3:C8,0)」を挿入してみます。
そうすると、MATCH関数の結果は「３」で返されました。
つまり、MATCH関数は複数の検索値が存在する場合、上(もしくは左)のセルから優先的に検索値を返します。
表の最下行の「和歌山県」の位置を調査したいときは、以下の見出しにジャンプしてください。
MATCH関数でエラー値「#N/A」が発生する原因
MATCH関数を使用すると、エラー値「#N/A」が表示されることがあります。
「#N/A」が発生する理由は引数の使い方が間違っている場合など、主に２つの原因があります。
検索範囲に検索値が無い場合
１つ目の原因は、MATCH関数の引数「検索値」が引数「検索範囲」の中に存在しない場合です。
上の図のように、G3セルには「=MATCH(F3,B3:B8,0)」が挿入されています。
しかし、検索したい「鳥取県」という文字は検索範囲「B3:B8」の中には無く、C6セルに存在しています。こんな場合は、「#N/A」が発生します。
検索範囲が複数行＆複数列の場合
２つ目の原因は、MATCH関数の引数「検索範囲」が複数行(複数列)を指定している場合です。
上の図のように、G3セルには「=MATCH(F3,C3:D8,0)」が挿入されていて、エラー値「#N/A」が表示されています。
検索値したい「鳥取県」の文字は検索範囲「C3:D8」の中に含まれていますが、検索範囲は必ず1列(もしくは1行）で指定しなけばいけません。
検索範囲「C3:D8」のような複数行＆複数列で範囲を指定すると、MATCH関数が何行目で返すのか、それとも何列目で返すのかを判断出来ないということです。
【応用編】MATCH関数を使った事例
続いて、MATCH関数の応用編の使い方を紹介します。
特に、MATCH関数はVLOOKUP関数やINDEX関数と一緒に利用すると非常に役立ちます。ぜひ実務に取り入れてください。
事例１．MATCH関数×VLOOKUP関数で列番号を自動化
VLOOKUP関数は表から検索値に該当する値を抽出する関数です。
しかし、VLOOKUP関数を複数のセルで利用する際、引数「列番号」の数値をいちいち手入力で変更していくのは面倒です。
そんな時、下の図のように、VLOOKUP関数の引数「列番号」にMATCH関数を組み込むと、列番号の入力を自動で行うことが出来ます。
例えば、下の表から商品番号「25」の商品カテゴリを調べるために、以下のVLOOKUP関数を挿入します。
=VLOOKUP($G$3,$B$3:$E$11,MATCH(H2,$B$2:$E$2,0),FALSE)
第２引数「列番号」には「MATCH(H2,$B$2:$E$2,0)」と入力されていて、H2セルの「商品番号」という見出しがB2セルからE2セルのセル範囲の中で何番目にあるかを調べているため、VLOOKUP関数の結果は正しく「薬」と表示されます。
このVLOOKUP関数をI3セル、Jセルにコピペしても、列番号はMATCH関数によって自動で取得してくれるので、商品番号25の「商品名」や「値段」が正しく抽出されます。
VLOOKUP関数の引数「列番号」にMATCH関数を組み合わせる手順は、下の記事で紹介していますので、興味のある方はぜひ利用してみて下さい。
また、同様にHLOOKUP関数もMATCH関数と組み合わせることができます。HLOOKUP関数に使用したい場合は、以下の記事を参考にしてください。
事例２．MATCH関数×INDEX関数で複数条件で検索
INDEX関数とMATCH関数を組み合わせて利用することで、表の中から任意の値を複数条件で抽出することができます。
INDEX関数は、指定した行と列が交差するセルの値を抽出する関数です。
INDEX関数が『VLOOKUP関数の代わりに使う関数』で、『MATCH関数が検索範囲から抽出する位置を指定する役割』を果たします。
例えば、下の商品リストから、『B店』の『シャンプー』の値段を抽出する場合、G9セルに以下の数式をすると、表の３行目と３列目が交差するセルの値「720」円を抽出できます。
=INDEX(B4:E10, MATCH(G4,B4:B10,0), MATCH(H4,B3:E3,0))
INDEX関数とMATCH関数を組み合わせる手順は、下の記事で紹介していますので、興味のある方はぜひ利用してみて下さい。
事例３．「検索の型」:「-1」の使い方
事例の３つ目は、MATCH関数の第３引数「検索の型」を「-１」で利用する方法です。
第３引数「検索の型」を「-1」にすると、『検索値以上の中から最小値のセルの位置を検索』します。
例えば、下の表のように、D列が降順に並べ替えされている数値に対して、「850」を検索値にしてMATCH関数を使用します。
=MATCH(F3,D3:D8,-1)
そうすると、検索値「850」はD列にはありませんが、850以上で最小の値はD6セルの「900」なので、MATCH関数の結果は「4」と返されました。
事例４．「検索の型」:「1」の使い方
事例の４つ目は、MATCH関数の第３引数「検索の型」を「１」で利用する方法です。
第３引数「検索の型」を「1」にすると、『検索値以下の中から最大値のセルの位置を検索』します。
例えば、下の表のように、D列が昇順に並べ替えされている数値に対して、「1,100」を検索値にしてMATCH関数を使用します。
=MATCH(F3,D3:D8,1)
そうすると、検索値「1,100」はD列にはありませんが、1,100以下で最大の値はD6セルの「1,000」なので、MATCH関数の結果は「4」と返されました。
事例５．ワイルドカードを使った検索方法
MATCH関数で「検索の型」:「0」を使用する場合は、１番目の引数「検索値」にワイルドカードを使用することができます。
ワイルドカード文字には下の３つがありますが、”*”（アスタリスク）と”？”(疑問符)の２つだけ覚えればOKです。
| ワイルドカード文字 | 説明 |
| *（アスタリスク） | 0文字以上の任意の文字列 |
| ？(疑問符) | 任意の１文字 |
| ~(チルダ） | 次に続くワールドカード文字を文字として扱う |
例えば、『県、近県、兵庫県、神奈川県、県境、県立、県議会』の７つの文字列からワイルドカードを使って検索した場合、検索でヒットする文字は以下のようになります。
| 使用例 | 意味 | 検索でヒットするデータ |
| *県 | 「県」で終わる文字列 | 県、近県、兵庫県、神奈川県 |
| ？？県 | ２文字＋「県」の文字列 | 兵庫県 |
| 県* | 「県」で始まる文字列 | 県、県境、県立、県議会 |
| 県？ | 「県」+1文字の文字列 | 県境、県立 |
| *県* | 「県」を含む文字列 | 県、近県、兵庫県、神奈川県 県境、県立、県議会 |
それでは実際に、MATCH関数にワイルドカードを使って、下の表から「ん」で終わる文字の位置を検索します。
以下のように、MATCH関数の第１引数に「”*ん”」と指定します。
=MATCH("*ん",B3:B8,0)
そうすると、「ん」で終わる文字列は、B5セルの「みかん」なので、MATCH関数の結果は「3」と表示されます。
”*（アスタリスク）”を使って、検索値を「"*ん"」とするのは、「ん」で終わる文字列の位置を探すという意味です。
このようにワイルドカードを使うと、「～で終わる文字列」という使い方だけでなく、「～で始まる文字列」「～を含む文字列」など高度な検索が可能となります。
ワイルドカードのいろいろな使い方はこちらの記事で紹介していますので、ぜひ参考にしてみてください。
事例６．複数条件での検索方法
上記で紹介しましたが、MATCH関数の検索範囲内に複数の検索値がある場合は、上側(もしくは左側 )の位置が表示されます。
例えば、下の表の「地域」列には”和歌山県”が２つ存在しています。このような表に対してMATCH関数を使うと、上側の”みかん”の”和歌山県”の位置が優先的に返されます。
では、表の最下行の”桃”の”和歌山県”の行の位置を把握したい場合は、どのように対応すればいいか紹介します。
まず、下の図のように、「地域」列の右横に１列挿入して、「果物」列と「地域」列の文字を”＆”で結合してください。
=B3&C3
次に、検索値「和歌山県」の左横のセルにもう一つの検索値「桃」を入力してください。
最後に以下のMATCH関数を挿入します。検索値は、「桃」と「和歌山県」を検索条件にするために、G3セルとH3セルを”＆”で結合してください。
=MATCH(G3&H3,D3:D8,0)
そうすると、MATCH関数で返される値は「6」となり、正しく「桃」と「和歌山県」が含まれた表の最下行の位置が返されました。