Excel MATCH関数の使い方の基本と応用！VBAの一括設定も！

ExcelでMATCH関数を見かけ、何の用途で使っているか、MATCH関数がどのように機能しているのかわからないときはないでしょうか。

例えば前任者から引き継いだ資料の関数にMATCH関数が使用されどのような処理をしているか把握したいときなどです。

けど、そんな中で悩むことは、

・そもそもMATCH関数がどのような機能であるかわからない
・MATCH関数の使い方を知りたいが説明がよくわからない
・VBAを使いMATCH関数を一括設定したいが方法がわからない

ですよね。

今回はそんなお悩みを解決する、

・Excel MATCH関数の使い方を基本から応用
・VBAによるMATCH関数一括設定の方法

についてまとめます！

ExcelでMATCH関数を使用するイメージ

ExcelでMATCH関数を使用するイメージについて説明をします。

MATCH関数とは指定範囲の表や配列の中から検索して一致した場所の位置を数字で返す処理をする関数です。

例えばこんな表があり、

キーワードを行方向や列方向に検索し一致した場所の番号を取得していきます。

結果

結果

またINDEX関数と組み合わせて2つ目以降の複数該当する結果を取得することもできます。

さらにVBAでMATCH関数を一括設定することもできます。

説明だけですと理解しにくいので、

実際に使用しながら理解していきましょう！

MATCH関数を使用する表を用意する

MATCH関数を使用する表を用意しましょう。

内容はなんでもよいですが、表の値はすべてことなる値にしてください。

サンプルは以下のようにしました。

列（縦）方向を検索範囲に指定

行（横）方向を検索範囲に指定

MATCH関数の書式/機能/引数について

MATCH関数の詳細について説明をします。
MATCH関数の書式と機能については以下の通りです。

関数名	MATCH
機能	照合の型に従って参照または配列に含まれる値を検索し、検査値と一致する要素の相対的な位置を数値で返します。
書式	=MATCH(検査値, 検査範囲, ［照合の型］)
引数	検査値：検査範囲の中で照合したいキーワードを指定します。検査値は数値、文字列、または論理値を指定できます。検索範囲：検索するセルの範囲を指定します。照合の型：省略可能でその場合は1になります。 -1：検査値以下の最大の値を検索します。 0：検査値と完全一致のみ検索します。 1 ：検査値以上の最小の値を検索します。
エラー値	#N/A

参考：https://support.microsoft.com/ja-jp/office/match-%E9%96%A2%E6%95%B0-e8dffd45-c762-47d6-bf89-533f4a37673a

タカヒロ

MATCH関数を一言でいうと、検索値が指定した範囲のどの位置にあるか数字で教えてくれる関数ということですね。

MATCH関数で1列目の値をすべて取得する

列（縦）方向を検索範囲に指定

MATCH関数で1列目の位置番号を取得してみましょう。

まずは、セル参照先の番地をA1形式で指定してみます。

以下のように入力します。

=MATCH(F1,$A1:$A5,1)

J列までドラッグしていきます。

はい、参照範囲の1列目の値がすべてとれましたね。

行（横）方向を検索範囲に指定

MATCH関数で1行目の位置番号をすべて取得してみましょう。

以下のように入力します。

=MATCH(F1,$A1:$C1,1)

同じくH列までセルをドラッグします。

こちらも、参照範囲の1行目の位置番号がとれましたね。

MATCH関数で「#N/A」エラーとなった場合の原因と回避方法

MATCH関数で「#N/A」エラーとなった場合の原因と回避方法について説明をします。

MATCH関数「#N/A」エラーの原因

該当する検査値がないか、検査値の形式が異なる場合にエラーとなります。

MATCH関数「#N/A」エラーの回避方法

該当する検査値を指定するようにしましょう。

エラーであっても「#N/A」を表示させないようにするには以下のようにIFERROR関数を使います。

=IFERROR(MATCH(I1,$A1:$C1,1),"該当なし")

なお、第三引数の照合の型を1や-1に指定した場合、該当する文字列がない場合は近似値の結果が返りますのでエラーとはなりません。

タカヒロ

近似値ではなく完全一致にしたい場合のみ結果を出したい場合は第三引数を0に指定するようにしましょう。

応用：MATCH関数とMATCH関数でVLOOKUP関数の2つ目の結果を出す

関数の説明

MATCH関数とMATCH関数でVLOOKUP関数の2つ目の結果を出すこともできます。

サンプルデータは以下の通り、A列に都道府県、B列に名前が入力されています。

E2セルに入力した都道府県の「千葉県」に該当する名前すべてを抽出しG列へ出力していきます。

H列にMATCH関数の出力結果である検索位置、I列に検索値があるセルの番号を出力する値を入力しますので、空欄のセルを用意します。

1行目の項目名は「検索位置」、「セル番号」とします。

1つ目の検索結果を出力する

用意ができましたら、おなじみの一つ目の検索結果を出力していきます。
G2のセルへ以下を入力します。

=MATCH($E$2,A$1:A$13,0)

E2の「千葉県」をキーにA列を検索し、一つ目の位置番号を出力します。

結果は1行目から3つ目を意味する3となりました。

続いてI2セルへ以下を入力します。

=H2

最後にG2セルへ以下を入力します。

=INDEX(B$1:B$13,I2,1)

はい、検索結果が表示されましたね。

2つ目以降の検索結果を出力する

次に今回のメインの2つ目以降の検索結果を出力していきましょう。

H3セルへ以下を入力します。

=MATCH($E$2,INDIRECT("A"& I2+1 &":A13"),0)

MATCH関数の検索開始位置を1つ目のセル番号+1番目から開始して2つ目を探していくというわけです。

I3セルへ以下を入力します。

=SUM(H$2:H3)

1番目と2番目の検索位置を合計し、2番目の値が存在する行番号を算出します。

G3セルへ以下を入力します。

=INDEX(B$1:B$13,I3,1)

G2セルからドラッグしてコピーしてもよいでしょう。

2番目の結果が表示されましたね。

最後の仕上げです。

2番目のGHIセルを選択し、6番目までドラッグし、数式をコピーします。

「千葉県」に該当する名前がすべて抽出されましたね！

タカヒロ

詳細は別ページにまとめていますので、続きは以下をご覧ください。

VLOOKUP関数の複数該当の値をすべて取得する方法！VBAで同じ処理も可！

VBAでMATCH関数を一括挿入する

これまで関数を手入力する必要がありましたが、次はVBAを使い、関数を挿入する処理や計算結果を挿入する処理を一括で行っていきましょう。

タカヒロ

ここからこの記事のメインとなります。

FormulaでMATCH関数を数式のまま挿入する

Formulaを使い、数式のままで挿入をしてみましょう。

Formulaはその名の通り数式を入れるプロパティとなります。
値として受け渡す情報は文字情報でしかないですが、Formulaを使うことにより数式としてセルへ代入をしてくれます。

列（縦）方向を検索範囲に指定

列（縦）方向をMATCH関数の検索範囲に指定してみましょう。

データは関数の時と同様となります。

VBAは以下の通りです。

Sub MATCH関数を一括挿入する1_1()
    For i = 1 To 5
        Cells(2, 5 + i).Formula = "=MATCH(" & Cells(1, i + 5).Address & ",A1:A5,1)"
    Next
End Sub

For文で5回処理を繰り返します。

For i = 1 To 5

FからJ列まで数式を代入します。

Cells(2, 5 + i).Formula = "=MATCH(" & Cells(1, i + 5).Address & ",A1:A5,1)"

CellsオブジェクトのAddressプロパティで参照先セルの位置情報を取得しています。またMATCH関数の検索範囲はA1:A5としています。

VBAを実行してみましょう。

はい、できましたね。

第一引数の検査値をアドレスではなく、値にすることもできます。
AddressプロパティをValueプロパティに変更します。

Sub MATCH関数を一括挿入する1_2()
    For i = 1 To 5
        Cells(2, 5 + i).Formula = "=MATCH(""" & Cells(1, i + 5).Value & """,A1:A5,1)"
    Next
End Sub

検査値は文字列になりましたが、アドレス指定と同じ結果になりましたね。

行（横）方向を検索範囲に指定

行（横）方向をMATCH関数の検索範囲に指定してみましょう。

VBAは以下の通りです。

Sub MATCH関数を一括挿入する2_1()
    For i = 1 To 3
        Cells(2, 5 + i).Formula = "=MATCH(" & Cells(1, i + 5).Address & ",A1:C1,1)"
    Next
End Sub

For文で3回処理を繰り返します。

For i = 1 To 3

FからJ列まで数式を代入します。

Cells(2, 5 + i).Formula = "=MATCH(" & Cells(1, i + 5).Address & ",A1:C1,1)"

CellsオブジェクトのAddressプロパティで参照先セルの位置情報を取得しています。またMATCH関数の検索範囲はA1:C1としています。

VBAを実行してみましょう。

はい、できましたね。

第一引数の検査値をアドレスではなく、値にすることもできます。
AddressプロパティをValueプロパティに変更します。

Sub MATCH関数を一括挿入する2_2()
    For i = 1 To 3
        Cells(2, 5 + i).Formula = "=MATCH(""" & Cells(1, i + 5).Value & """,A1:C1,1)"
    Next
End Sub

検査値は文字列になりましたが、アドレス指定と同じ結果になりましたね。

MATCH関数の結果を挿入する

MATCH関数の数式ではなく、MATCH関数の結果を直接挿入するようにしてみましょう。

ワークシート関数をVBAで扱うにはWorksheetFunctionを使います。

列（縦）方向を検索範囲に指定

列（縦）方向をMATCH関数の検索範囲に指定してみましょう。
VBAは以下の通りです。

Sub MATCH関数の結果を一括挿入する1_1()
    For i = 1 To 5
        Cells(2, 5 + i).Value = WorksheetFunction.Match(Cells(1, i + 5).Value, Range("A1:A5"), 1)
    Next
End Sub

RangeオブジェクトとCellsオブジェクトを使い、指定セルの値を出力します。

Cells(2, 5 + i).Value = WorksheetFunction.Match(Cells(1, i + 5).Value, Range("A1:A5"), 1)

はい、数式ではなくMATCH関数の結果が入力されましたね。

行（横）方向を検索範囲に指定

行（横）方向をMATCH関数の検索範囲に指定してみましょう。
VBAは以下の通りです。

Sub MATCH関数の結果を一括挿入する1_2()
    For i = 1 To 3
        Cells(2, 5 + i).Value = WorksheetFunction.Match(Cells(1, i + 5).Value, Range("A1:C1"), 1)
    Next
End Sub

はい、数式ではなくMATCH関数の結果が入力されましたね。