【関数】指定した値が対象範囲の何行/何列目に位置するかを数値で返すことができる「MATCH」の使い方
AさんAさん

VLOOKUPを覚えてデータ転記は便利になりましたが、同じ主キーで複数列を転記したい場合に、いちいち列番号を手修正するのが面倒です。。
こんな場合、もっと楽にできる方法はありませんかね?

森田森田

その場合は、関数のMATCHを活用すると良いですよ!
では、MATCHの使い方について解説していきますね。

はじめに

この記事は関数の概要とVLOOKUPの詳細を把握していることが前提です。

参考記事

関数の概要とVLOOKUPの使い方の詳細は以下の記事をご参照ください。


表の行番号/列番号を自動計算したい場合は「MATCH」が有効

データ転記作業において、表の行番号や列番号を自動計算できると、より便利になるケースがあります。

たとえば、VLOOKUPを使い、同じ主キーで複数列を転記する場合です。

VLOOKUPの引数「列番号」を定数で指定していると、列ごとにVLOOKUPの数式の列番号を手修正する必要があり、列数が多いほど手間がかかってしまいます。

こんな場合、関数のMATCHを使い、列番号を自動計算しましょう(行番号も計算可能)。
MATCHは「マッチ」と読む。

MATCHを使うことで、指定した値が対象範囲の何行/何列目に位置するかを数値で返すことが可能です。

参考記事

VLOOKUP+MATCHの組み合わせテクニックの詳細は以下の記事をご参照ください。

MATCHの構文

MATCHの構文は以下の通りです。

=MATCH(検査値,検査範囲,[照合の種類])
指定された照合の種類に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。

引数名 必須 データ型 説明
検査値 すべて 「検査範囲」の範囲内で検索したい値を示す単一セルを指定します。
検査範囲 数値 検索する対象のセル範囲(1行または1列)を指定します。
照合の種類 数値 2種類の「近似一致」と「完全一致」のいずれで検索するか指定します。
1:近似一致(「検査値」以下の最大値)、
0:完全一致(「検査値」と等しい最初の値)、
-1:近似一致(「検査値」以上の最小値)

MATCHの各引数はVLOOKUPの引数と類似(以下、左がMATCH、右がVLOOKUP)。
※検査値=検索値、検査範囲=範囲、照合の種類=検索方法

引数「照合の種類」を省略した場合、「1」と同じ近似一致(「検査値」以下の最大値)で検索される。
MATCHでは英字の大文字と小文字で区別されない。
※引数「検査値」と引数「検査範囲」で大文字↔小文字の表記ゆれがあっても同一文字扱いとなる。

引数「検査値」に指定した値が引数「検査範囲」にない場合、エラー値「#N/A」が表示。
引数「検査範囲」に指定したセル範囲が2行以上または2列以上の場合、エラー値「#N/A」が表示。
引数「照合の種類」が「0」で、かつ引数「検査値」に指定する値が文字列の場合、引数「検査値」にワイルドカード文字(*?)を使用し、あいまい検索が可能。
※ワイルドカード文字(*?)を通常の文字として検索したい場合は、その文字の前に半角のチルダ(~)を付けること(例:~*)。

【参考】引数「検索方法」は「完全一致」が基本

前述の通り、MATCHの引数「照合の種類」は省略できますが、実務では「完全一致」で検索することが大原則です。

よって、この引数は省略せず、「0」を指定すると覚えておきましょう。

なお、実務では近似一致で検索するケースはほぼありませんが、どんな動作になるかは以下の表をご覧ください。

照合の種類 検索方法 動作
1または省略 近似一致 引数「検査値」の値以下のものから最大の値を検索します。
引数「検査範囲」に指定した行・列の中の値は昇順である必要があります。
例)1→9A→Z、あん、FALSE→TRUE
0 完全一致 引数「検査値」の値と等しい最初の値を検索します。
引数「検査範囲」に指定した行・列の中の値は任意の並びで問題ありません。
-1 近似一致 引数「検査値」の値以上のものから最小の値を検索します。
引数「検査範囲」に指定した行・列の中の値は降順である必要があります。
例)9→1Z→A、んあ、TRUE→FALSE

いずれのケースも、引数「検査範囲」が1行の場合は左から、1列の場合は上から検索される。

【参考】MATCHは「検索/行列関数」

あくまで参考情報となりますが、MATCHはリボン「数式」タブの関数ライブラリの「検索/行列」に分類されています。

実際にMATCHを活用する際は、以下で解説しているように直接入力で挿入していきましょう。

MATCHの使用結果イメージ

MATCHを使い、列番号を返すイメージは以下の通りです。

今回は「商品名」・「単価」列が「商品マスタ」シート上で何列目にあるか、計算結果を注文テーブルのD1E1セルへ表示しました。

MATCHを複数セルへ使う場合、1セルにつき1つの関数を使います。

ベースの数式をセットしたら、他のセルへペーストしましょう。

後は、MATCHの戻り値をVLOOKUPの引数「列番号」で参照すれば、同じVLOOKUPの数式で複数列の転記が可能となります。

なお、今回はD1セルの数式をD2セルへコピペで使いまわせるように引数「検査値」は複合参照(行のみ絶対参照)にしています。

また、引数「検査範囲」はどのMATCHの数式でも同じセル範囲(A1~C1セル)で参照を固定するため、絶対参照にしています。

参考記事

絶対参照/相対参照の詳細については以下の記事をご参照ください。

【参考】「行番号」は主キーを基準にすることが一般的

先述の通り、列番号を計算したい場合はMATCHの引数「検査値」にフィールド名を指定します。

一方、行番号を計算したい場合は、主キーを指定することが一般的です。

MATCHで行番号を計算するケースは、INDEXとの組み合わせ時が多いです。

参考記事

INDEXの詳細については以下の記事をご参照ください。

MATCHの数式の挿入手順

上記の結果を得るための手順は以下の通りです。

  1. 関数を挿入するセルを選択
    ※今回はD1セル
  2. =mat」等と入力
  3. サジェストから「MATCH」を選択し、「Tab」キーで確定
  4. 検査値にしたいセルを選択
    ※今回はD2セル(行のみ絶対参照)
  5. コンマ(,)を入力
  6. 検索対象のシートへ移動
    ※今回は「商品マスタ」シート
  7. 検索対象のセル範囲を選択
    ※今回はA1~C1セル(絶対参照)
  8. コンマ(,)を入力
  9. 0」を選択 or 入力
  10. Enter」キーで確定
  11. ベースの数式をコピーし、以降のセルへペースト
    ※今回はE1セルへペースト

手順②の際にIMEを半角英数モードにすること。
手順④は、行番号を計算したい場合は主キー、列番号を計算したい場合はフィールド名を指定するケースが一般的。
手順④で選択したセルがテーブルで構造化参照になってしまう場合、複合参照(D$2等)で直接入力すること(手順⑪でコピペしたセルの引数「検査値」がスライドしないため)。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_MATCH.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ブックを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 関数を挿入するセルを選択
    ※今回はD1セル
  2. =mat」等と入力
  3. サジェストから「MATCH」を選択し、「Tab」キーで確定
  4. 検査値にしたいセルを選択
    ※今回はD2セル(行のみ絶対参照)
  5. コンマ(,)を入力
  6. 検索対象のシートへ移動
    ※今回は「商品マスタ」シート
  7. 検索対象のセル範囲を選択
    ※今回はA1~C1セル(絶対参照)
  8. コンマ(,)を入力
  9. 0」を選択 or 入力
  10. Enter」キーで確定
  11. ベースの数式をコピーし、以降のセルへペースト
    ※今回はE1セルへペースト

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

MATCHは表の行番号/列番号を自動計算する際に役立つ関数です。

VLOOKUPINDEX等、他の検索/行列関数と組み合わせ、より高度なデータ転記作業を行うことが可能になるため、ぜひ覚えていきましょう!

なお、MATCH以外にもExcel関数の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。



ご参考になれば幸いですm(_ _)m

森田森田

MATCHとセットで覚えた方が良い関数は、行番号/列番号を基準にデータ転記を行う「VLOOKUP」と「INDEX」です。
これらとMATCHを組み合わせることで、MATCHが自動計算した行番号/列番号を使って、より柔軟なデータ転記を実現できますよ!