Excel(エクセル)のMATCH関数の使い方|検索して条件に合うセルの位置を求める
使用関数:MATCH
MATCH関数とは?
MATCHは「合う」「一致する」を意味します。MATCH(マッチ)関数は検索したい値(検査値)を指定した範囲(検査範囲)内で探して、一致する値がどこにあるかを教えてくれます。そのシンプルさ故に使いやすく、とても重宝。単独で使うことはあまりなく、INDEX関数やOFFSET関数など他の関数と組み合わせることで真価を発揮します。
MATCH関数の引数と書式
「数式」→「関数ライブラリ」→「検索/行列」からMATCHを選択します。
MATCH関数の引数は3つ。「検査値」「検査範囲」「照合の種類」です。
「検査値」は検索のキーで、照合する元の値です。
「検査範囲」は同一行か同一列の連続したセル範囲で、その先頭のセルを「1」として検索値とマッチする値の位置までを数えた数字が返されます。
「照合の種類」は検査値の照合方法です。「0:検査値と完全一致」「1:検査値以下の近似値」「-1:検査値以上の近似値」から選びます。
書式の構成はこうなります。
MATCH関数の使い方
簡単な使用例を挙げて、MATCH関数の設定方法を説明します。
得点の高い順に氏名と点数を並べた表があります。その中の誰か一人の順位をMATCH関数で求めてみます。
第一引数「検査値」
順位を知りたい氏名の一つを検査値に指定します。
第二引数「検査範囲」
検査値を検索する氏名のデータ範囲をドラッグで指定します。
第三引数「照合の種類」
検査値と検査範囲内の値をどう照合するかを選びます。
照合方法は3種類です。
- 「0」・・・完全一致の値を検索します。※ワイルドカードを使えば部分一致で検索可。
- 「1」・・・昇順一致。「検索値」以下で最大値を検索。検索範囲のデータを昇順に並べ替えておく必要があります。
- 「-1」・・・降順一致。「検索値」以上で最小値を検索。検索範囲のデータを降順に並べ替えておく必要があります。
この例では完全一致の「0」を入力します。
検査値に指定した氏名の順位が取得できました。
MATCH関数でワイルドカードを使った、あいまい検索
MATCH関数では文字列のあいまい検索が可能です。ワイルドカードを使って部分一致の文字列を指定して検索できます。使えるワイルドカードは「*」と「?」です。
検査値に部分一致の文字列を指定する
MATCH関数の検査値に“三*子”と指定して、三で始まり、子で終わる氏名の順位を抽出します。アスタリスク*は任意の文字列を表すワイルドカードです。
任意の1文字を表す半角疑問符?を使って、“?????子”としても同じ氏名の抽出ができます。文字列の中の空白も1文字として数えます。
MATCH関数の応用
MATCH関数はINDEX関数・OFFSET関数・VLOOKUP関数など他の関数と組み合わせて使うことが多く、いろいろなシーンで登場します。
MATCH関数とINDEX関数を組み合わせて複数条件で抽出
MATCH関数とINDEX関数は相性がよく、使用頻度の高い組み合わせです。
この使用例では、INDEX関数の2つの引数「行番号」「列番号」にMATCH関数をネストして、「品名」と「サイズ」の複数条件に応じた金額をメニューから引き出します。
最初にINDEX関数の引数「配列」にメニューの金額のデータ範囲を指定し、F4キーで絶対参照にしておきます。
INDEX関数の「行番号」に入れるMATCH関数
INDEX関数の「行番号」に入れ子するMATCH関数で1つ目の条件を指定します。
「検査値」に注文品名の入ったセルをクリックで指定、「検査範囲」にメニューの品名の一覧をドラッグで指定し、F4キーで絶対参照にします。「照合の種類」は完全一致の「0」を入力します。
数式は「MATCH(F2,$A$2:$A$7,0)」となります。
「照り焼きチキンと完全にマッチする文字列がメニュー一覧の上から何番目にあるか」という意味の数式です。この答えは「2」で、INDEX関数の「行番号」に「2」と指定したことになります。
INDEX関数の「列番号」に入れるMATCH関数
INDEX関数の「列番号」に入れ子するMATCH関数で2つ目の条件を指定します。
「検査値」に注文サイズの入ったセルをクリックで指定、「検査範囲」にメニューのサイズの一覧をドラッグで指定し、F4キーで絶対参照にします。「照合の種類」は完全一致の「0」を入力します。
数式は「MATCH(H2,$B$1:$D$1,0)」となります。
「Lと完全にマッチする文字列がサイズ一覧の左から何番目にあるか」という意味の数式です。この答えは「3」で、INDEX関数の「列番号」に「3」と指定したことになります。
INDEX関数の最終的な組み立ては以下のようになります。
注文には個数のセルもあるので、この数式に個数のセルを掛け算します。
数式「=INDEX($B$2:$D$7,MATCH(F2,$A$2:$A$7,0),MATCH(H2,$B$1:$D$1,0))*G2」
他の注文のセルにフィルコピーして完了です。注文の総計金額にはSUM関数「=SUM($I$2,$I$3)」が入っています。
MATCH関数とOFFSET関数の組み合わせ
前章の注文に応じた金額をメニューから引き出す使用例はOFFSET関数とMATCH関数との組み合わせでも同じ結果を取得することができます。
OFFSET関数とINDEX関数の一番大きい違いは第一引数で、INDEXでは「配列」に金額の全データ範囲を指定しましたが、OFFSETの「参照」には行方向・列方向に動く起点となるセル(この例では$A$1)を指定します。OFFSETの「参照」は「0」位置を示します。
第二引数「行数」・第三引数「列数」にはINDEX関数の「行番号」「列番号」と同じMATCH関数の数式をそれぞれ入力します。
「高さ」と「幅」には、一つのセルを取得するということで数値で「1」と入れます。
数式は「=OFFSET($A$1,MATCH(F2,$A$2:$A$7,0),MATCH(H2,$B$1:$D$1,0),1,1)」となります。
「メニュー表の左上端セルから行方向に2つ目、列方向に3つ目のセル一つの値を表示する」という意味の数式です。
その他の操作はINDEXとMATCH関数の組み合わせと同じです。
MATCH関数の検索値・範囲を複数で指定する方法
MATCH関数の検索値や検索範囲に複数の指定を行うには、セル番地やセル範囲を「&」でつなげる方法を採ります。
この例では「配送先」「個数」「配送区域」の3つを条件に、MATCH関数で伝票番号を求めています。
「配送先」「個数」「配送区域」の3つの検査値を&でつなげて第一引数に入れ、同様に第二引数「検査範囲」にもそれぞれの列範囲を&でつなげて入れています。
数式は「=MATCH(F1&G1&I1,B2:B8&C2:C8&D2:D8,0)」となります。
MATCH関数のエラーの要因と対処法
MATCH関数でのエラーは、照合の種類を「1(検査値以下の近似値)」か「-1(検査値以上の近似値)」にしたケースで起きやすいかもしれません。
多くの場合、表データの並び順の不備が要因となって#N/Aエラーが返ります。
下図はMATCH関数をINDEX関数の引数に入れ子して配送料を求めようとした例ですが、#N/Aエラーが返されています。
このエラーは「行番号」に入れたMATCH関数「MATCH(C8,F2:F6,-1)」で起こっています。
照合の種類を「-1」にする時は、第二引数の「検査範囲」が降順(大きい値から小さい値へ)になっている必要があります。
この例の検査範囲のデータが昇順になっているのがエラーの原因でした。データを降順に作り変えればエラーは消えて求める値が返されます。
逆に、照合の種類を「1」にする時は、第二引数の「検査範囲」を昇順(小さい値から大きい値へ)にしておきましょう。
- MATCH関数は「検査値」とマッチする値を「検査範囲」から探し、範囲の先頭セルから何番目に位置するかを求めます
- 「照合の種類」は「0:検査値と完全一致」「1:検査値以下の近似値」「-1:検査値以上の近似値」の3種
- 「0」とした場合、部分一致であいまい検索ができるワイルドカードが使えます
- 「1」とした場合、検査範囲を昇順に、「-1」とした場合は降順にします。この並び順が不備だと#N/Aエラーが返されます
- MATCH関数はINDEX関数やOFFSET関数と組み合わせてよく使われます