【Excel】MATCH関数(マッチ関数)の使い方！検索値の相対的な位置を調べる

Excel(エクセル)の関数の中で、MATCH関数(マッチ関数)は認知度が低めのマイナーな関数という印象もありますが、エクセルを熟知している人にとっては、よく利用される関数の一つです。

MATCH関数は、欲しい値が表の中でどの位置にあるかを知る時に役立ちます。

引数が複雑と感じる方が多いかもしれませんが、MATCH関数の使い方の基本編から応用編までを解説します。

MATCH関数(マッチ関数）とは？

MATCH関数(マッチ関数）は、指定した検索範囲内(検査範囲内)で、数値や文字列などの検索値(検査値)が、どの位置にあるかを数値(〇〇列目、▲▲行目）で返す関数です。

MATCH関数を使用することで、探したい値が表の何行目(何列目）にあるのかを調べることが出来ます。

MATCH関数の構文は、以下のとおり３つの引数(※)で構成されています。

＝MATCH(検査値,検査範囲,[照合の型])

引数の詳しい説明は次の見出しを見てね。

私

※引数(ひきすう)とは、エクセルで関数を使用する際に必要な情報のことをいいます。関数が正しい結果を算出するための入力項目だと考えてください。関数名の後のカッコ内に引数を入力します。また、引数には、入力必須のものと、そうでないものがあります。

MATCH関数の引数

先述のとおり、MATCH関数(マッチ関数)は、指定した検索範囲内(検査範囲内)で、数値や文字列などの検索値(検査値)が、どの位置にあるかを数値(〇〇列目、▲▲行目）で返す関数です。

構文は、「＝MATCH(検査値,検査範囲,[照合の型])」となっていて、MATCH関数では引数を３つ指定する必要があります。

それぞれの引数について詳しく解説します。

第１の引数：検査値(検索値)

１番目の引数「検査値」は「検索値」のことです。この引数は必ず設定が必要で、検索した値やセルを参照して指定します。文字列などの値を指定する時は、”〇〇〇”というようにダブルクォーテーションで囲って使用します。

検索値は２番目の引数「検査範囲(検索範囲)」の中から検索されるので、この範囲内に含まれている必要があります。

３番目の引数「照合の型」が「0」の場合は、「検査値」に疑問符(？)やアスタリスク(*)などのワイルドカードも使用することが出来ます。

第２の引数：検査範囲(検索範囲)

２番目の引数「検査範囲」は「検索範囲」のことです。この引数も第１の引数と同じく必須です。検索するセルの範囲を指定します。

検索範囲の中から、検索値の相対的な位置を返す関数であるため、検索範囲は、「B2:B9」や「B2:E2」というように、１行(１列)で指定する必要があります。

第3の引数：照合の型

３番目の引数は「照合の型」で「検索値の探し方」のことです。

「照合の型」は、「-1」「0」「1」のいずれかを指定しますが、「0」であれば引数の指定は省略できます。

基本的に「照合の型」は、「0」で利用されることが圧倒的に多いと覚えておきましょう。

MATCH関数の使い方(基本編)

実際に、上の『地域別の果物の消費量』表を使って、MATCH関数の使い方を解説します。

この表の中から、特定の文字が何行目にあるか、何列目にあるかを求めます。この見出しで引数の使い方を覚えましょう。

縦方向の相対的な位置を返す

表の中から、「鳥取県」が何行目の位置にあるかを調べるために、MATCH関数で以下の数式を挿入します。

=MATCH(F3,C3:C8,0)

１つ目の引数「検査値」⇒「鳥取県」の位置を探したいので「F3」セルを指定
２つ目の引数「検査範囲」⇒「鳥取県」を含んだ「地域」列の「C3:C8」を指定
３つ目の引数「照合の型」⇒「鳥取県」と完全に一致するセルの位置を探すため「0」を指定

MATCH関数で返された数値は「4」となり、検索範囲の中で上から４行目の位置にあることが分かりました。

横方向の相対的な位置を返す

表の中から、「地域」が表の何列目の位置にあるかを調べるために、MATCH関数で以下の数式を挿入します。

=MATCH(F5,B2:D2,0)

１つ目の引数「検査値」⇒「地域」の位置を探したいので「F5」セルを指定
２つ目の引数「検査範囲」⇒「地域」を含んだ「項目」行の「B2:D2」を指定
３つ目の引数「照合の型」⇒「地域」と完全一致するセルの位置を探すため「0」を指定

MATCH関数で返された数値は「2」となり、検索範囲の中で左から２列目の位置にあることが分かりました。

検索範囲に同じ値が複数存在する場合

MATCH関数の検索範囲に検索値が複数存在する場合について紹介します。

下の表のように、「和歌山県」は２つあります。検索範囲に検索値が必ず１つではない場合があります。

「和歌山県」の相対的な位置を返すために、MATCH関数「=MATCH(F7,C3:C8,0)」を挿入してみます。

そうすると、MATCH関数は「6」ではなく、「３」で返されました。MATCH関数は複数の検索値が存在する場合、上のセルから優先的に検索値を返します。

表の最下行の「桃」で「和歌山県」というように、複数の条件で位置を見つけたい場合は、応用編の【複数条件で検索】の見出しで方法を紹介していますので、参考にしてみて下さい。

エラー値「#N/A」が発生する場合

MATCH関数を使用すると、エラー値「#N/A」が表示されることがあります。「#N/A」が発生する理由は引数の使い方が間違っている場合が多いです。

この見出しでは、MATCH関数で「#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関数の応用編の使い方を紹介します。利用頻度は少ないかもしれませんが、ちょっとした時に役立つテクニックです。

「検索の型」:「-1」の使い方

３番目の引数「検索の型」は「0」で使われることが多いですが、「-1」の使い方を紹介します。

「-1」は、『検索値以上の最小値を検索』します。 そのためには検索範囲を降順に並び替えしておく必要があります。

「消費量」列の中から650以上で最小の値の相対的な位置を調べるために、セルG3には、「=MATCH(F3,D3:D8,-1)」が挿入されています。

650以上で最小の値は、D7セルの「800」なので、MATCH関数の結果は「5」が返されました。

「検索の型」:「1」の使い方

３番目の引数「検索の型」が、「1」の場合の使い方も紹介します。

「1」は、『検索値以下の最大値を検索』します。 そのためには検索範囲を昇順に並び替えしておく必要があります。

「消費量」列の中から850以下で最大の値の相対的な位置を調べるために、セルG3には、「=MATCH(F3,D3:D8,1)」が挿入されています。

850以上で最大の値は、D4セルの「800」なので、MATCH関数の結果は「2」が返されました。

ワイルドカードを使った検索方法

MATCH関数で「検索の型」:「0」を使用する場合は、１番目の引数「検索値」にワイルドカードを使用することが可能です。

このワイルドカード文字とは、トランプのジョーカーがあらゆる札の代用に出来るのと同様に、ワイルドカード文字はあらゆる文字の代用として指定出来ます。

ワイルドカード文字には下の３つがありますが、”*（アスタリスク）”と”？(疑問符)”の２つだけ覚えればOKです。

例えば、『県、近県、兵庫県、神奈川県、県境、県立、県議会』の７つの文字列からワイルドカードを使って検索した場合、検索でヒットする文字は以下のようになります。

それでは実際に、このワイルドカードをMATCH関数に使ってみましょう。

以下のMATCH関数を挿入しています。”*（アスタリスク）”を使って、検索値を「"*ん"」としています。「ん」で終わる文字列の位置を探すという意味です。

=MATCH("*ん",B3:B8,0)

「ん」で終わる文字列は、B5セルの「みかん」なので、MATCH関数の結果は「3」が返されました。

このように「～で終わる文字列」という使い方だけでなく、「～で始まる文字列」「～を含む文字列」などの相対的な位置をMATCH関数とワイルドカードを組み合わせて利用することで、調べることが出来ます。

ワイルドカードのいろいろな使い方はこちらの記事で紹介していますので、ぜひ参考にしてみてください。

私

複数条件での検索方法

上の表は”和歌山県”が「地域」列に２つ存在しています。このような表に対してMATCH関数を使うと、上側の”みかん”の”和歌山県”の位置が優先的に返されます。

表の最下行の”桃”の”和歌山県”の行の位置を把握したい場合は、どのように対応すればいいか紹介します。

まず、上の図のように、「地域」列の右横に１列挿入して、「果物」列と「地域」列の文字を”＆”で結合してください。

=B3&C3

次に検索値「和歌山県」の左横のセルにもう一つの検索値「桃」を追加してください。

最後に以下のMATCH関数を挿入します。

=MATCH(G3&H3,D3:D8,0)

検索値は、「桃」と「和歌山県」を検索条件にするために、G3セルとH3セルを”＆”で結合してください。

そうすると、MATCH関数で返される値は「6」となり、正しく「桃」と「和歌山県」が含まれた表の最下行の位置が返されました。

MATCH関数とVLOOKUP関数を組み合わせて列番号を自動化

VLOOKUP関数は表から検索値に該当する値を抽出する関数です。

しかし、VLOOKUP関数を複数のセルで利用する際、引数「列番号」をいちいち手入力で変更していくのは面倒です。

そんな時、下の図のように、VLOOKUP関数の引数「列番号」にMATCH関数を組み込むと、列番号の入力を自動で行うことが出来ます。

=VLOOKUP($G$4,$B$4:$E$15,MATCH(C3,$B$3:$E$3,0),FALSE)

VLOOKUP関数の引数「列番号」にMATCH関数を組み合わせる手順は、下の記事で紹介していますので、興味のある方はぜひ利用してみて下さい。

MATCH関数とINDEX関数を組み合わせて検索値を抽出

VLOOKUP関数を利用せず、INDEX関数とMATCH関数を組み合わせて利用することで、表の中から任意の値を複数条件で抽出することが可能です。

INDEX関数が『VLOOKUP関数の代わりに使う関数』で、『MATCH関数が検索範囲から抽出する位置を指定する役割』を果たします。

下の表の商品リストから、『B店』の『ブラシ』を抽出する場合、以下の数式を挿入すると、値段『600』を抽出できます。

=INDEX(B4:D11,MATCH(F4&G4,B4:B11&C4:C11,0),3）

INDEX関数とMATCH関数を組み合わせる手順は、下の記事で紹介していますので、興味のある方はぜひ利用してみて下さい。