Excel上級者が教えるMATCH関数の基本と使える応用ワザ+エラー対処術

スポンサードリンク
excel match エクセル
「MATCH関数がどんな関数か分からない…」
「INDEX+MATCH関数の使い方を覚えたい!」

エクセル関数の一つ「MATCH関数」。
ほとんど単体で使われることはないのですが、他の関数と組み合わせるととても便利なものに変わります。

ここではそんな便利なMATCH関数の、基本的な使い方からINDEX関数との組み合わせ方、そしてトラブル対処法を詳しく解説します。

これを覚えれば表から自在にデータを抜き出せるようになりますよ!

スポンサーリンク

1.MATCH関数の使い方

では始めに、MATCH関数の使い方を確認しましょう。
まずはMATCH関数の概要と指定する引数について解説します。

1-1.MATCH関数ってどんな関数?

MATCH関数とは「検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返す」関数です。

では、具体的な例を見てみましょう。

excel match

MATCH関数で指定した範囲「B1:B11」において、検査値「田中 一」さんの名前がある相対的な位置「3(番目)」が返ってきています。

このようにMATCH関数は、「指定した範囲の中で検索値が何番目にあるのか」を返してくれるのです。

1-2.MATCH関数の構文

MATCH関数の構文は次の通りです。

MATCH(検査値,検査範囲,照合の種類)
検査値 検索する値を指定
検査範囲 検索する範囲を1列指定
照合の種類 検索方法を指定
1:検査値以下の最大の値を検索(昇順並替必要)
0:完全一致する値のみを検索
-1:検査値以下の最小の値を検索(昇順並替必要)

数式は、次の手順で作りましょう。

(1)数式バーに「=MATCH(」と入力し「fx」ボタンを押す
excel match

(2)関数の引数ウィンドウで検査値、検査範囲、照合の種類を指定する
excel match

(3)完了
excel match

このようにすれば、MATCH関数を使うことができます。

スポンサーリンク

2.MATCH関数応用編!便利なINDEX+MATCH関数の使い方を知ろう!

次に、覚えておくと便利な「INDEX+MATCH関数」の使い方を確認しましょう。

2-1.INDEX+MATCH関数の使い方

INDEX関数は、「指定した行と列が交差する位置にあるセルの値を返す」関数で、構文は次の通りです。

INDEX(配列(参照),行番号,列番号,領域番号)
配列(参照) データの範囲を指定
行番号 値を返す行を数値で指定
列番号 値を返す列を数値で指定
領域番号 (複数の領域を指定した場合のみ)
領域の番号を数値で指定

INDEX関数とMATCH関数を組み合わせると、「検査値に合うデータを抜き出す」ことができます。

では、具体的な例を見てみましょう。

excel match

上の例は、元の表から検査範囲を「A列」に、検査値を「4」にし、№4の「牧田 槇一」さんのデータを抜き出したものです。

ちなみにセルA15を「6」とすると№6の「佐藤 幸三」さんのデータに変わります。

excel match

列に対して検索をかける場合、数式は
「=INDEX(配列,MATCH(検索値,検査範囲,0),列番号)」となります。

また、行に対して検索をかける場合、数式は
「=INDEX(配列,行番号, MATCH(検索値,検査範囲,0))」となります。

なお、同じような動きをする関数としてVLOOKUP関数がありますが、VLOOKUP関数は検査範囲が必ず左端でなければなりません。
INDEX+MATCH関数にはその縛りがないので、数式の入力は面倒ですが、様々な場面で使える組み合わせです。

覚えておくととても便利なので、ぜひ使い方を身に付けましょう。

2-2.複数列への条件指定の仕方

次に複数列への条件指定の仕方について見ていきます。

例えば、下の表から「出身が東京で受講料が2,200円の人」を15行目に抜き出してみたいと思います。

excel match

セルA15に入力する数式は、「{=INDEX($A$1:$E$11,MATCH(“東京”&”2200”,$C$1:$C$11&$E$1:$E$11,0),1)}」となります。

excel match

数式のポイントは2つあり

  • 1つ目は「MATCH関数の検査値と検査範囲を&でつなげる」こと
  • 2つ目は「配列数式にする」ことです。

「MATCH関数の検査値と検査範囲を&でつなげる」と、条件を4つ5つと増やすことも可能です。

そして「配列数式にする」点については、数式入力後に「Ctrl」+「Shift」+「Enter」キーを押すことで数式の両端に「{}」が付き、配列数式に変わります。
配列数式に変えないと「#VALUE!」エラーになりますので注意してください。

2-3.文字列部分一致の条件指定の仕方

続いて「この文字列を含む」など、文字列部分一致の条件指定の仕方です。

下の表から氏名に「御」の文字を含む人のデータを抜き出してみましょう。

excel match

セルA15に入力する数式は、「=INDEX($A$1:$E$11,MATCH(“*御*”,$B$1:$B$11,0),1)」となります。

excel match

数式のポイントは「検査値に「*(アスタリスク)を付ける」ことで、前後に付けると「この文字列を含む」、後ろだけに付けると「頭がこの文字列」、前だけに付けると「末尾がこの文字列」という指定の仕方になります。

これも覚えておくと便利なので活用してください。

スポンサーリンク

3.MATCH関数のエラー原因と対処法

最後はMATCH関数のエラー原因と対処法についてです。
MATCH関数は使い方を誤ると#N/Aエラーを返してしまいます。

excel match

上の表では、MATCH関数が#N/Aエラーを返しています。
検査値が「田中 十」、検査範囲がB列となっていますが、なぜでしょうか?

結論から言えば「検査値が検査範囲内にない」ことが原因です。
よく見ると、B列には「田中 十」という名前はないことが分かります。

また、似たような原因で「検査範囲の指定を間違えている」というミスも起こりがちです。
検査範囲を別の範囲で指定したり、検査範囲を複数列で指定した場合にも#N/Aエラーが起こります。

#N/Aエラーの際には
「検査値が検査範囲内にあるか?」「検査範囲の指定が間違っていないか?」
を確認しましょう。

スポンサーリンク

4.まとめ

Excelのmatch関数、INDEX+MATCH関数の使い方、MATCH関数のエラーの原因と対処法について解説しました。

「表からデータを抜き出す」という作業は、エクセルを使っていると頻繁にあります。

その際にMATCH関数、そしてINDEX+MATCH関数の使い方を覚えていれば、作業時間を短縮できるでしょう。
ぜひこのページの内容を参考に、関数を使いこなしてみてください!

スポンサードリンク

コメント