【ExcelVBA】MATCH関数の使い方とは？よくあるエラーの対策も解説！

こんにちは、VBAエンジニアのやすこれです。

みなさんは、VBAでMATCH関数を使ったことがありますか？

という方もいるのではないでしょうか。

そこで今回は、VBA上で動作するMATCH関数をご紹介します。

VBAでのMATCH関数の使い方

MATCH関数の基本的な使い方

MATCH関数は、任意の値で指定範囲を検索し、値が一致した位置を数字で返す関数です。

1. 基本構文

WorksheetFunction.Match(検索値, 検索範囲, 検索方法)

2. コード例

WorksheetFunction.Match(5, ActiveSheet.Range("A1:A10"), 0)

アクティブシート上「A1:A10」の範囲から、整数「5」で完全一致した値を返しています。検索方法の「0」は完全一致で検索することを意味しています。

画像のように上から5番目のA5セルに「5」があるので、コード例の数式は「5」を返します。

このように、指定した範囲で見つかった値を返すことができます。また、検索方法には完全一致以外にもいくつかあるので、詳しく解説しますね！

MATCH関数の「検索方法」について

検索方法には3つの値を指定できます。それぞれの意味について、簡単に解説しますね！
言葉だけでは伝わりづらいと思うので、検索範囲を「A1:A10」セルとした場合の具体的な例を順に紹介します。

検索方法「0」（完全一致）、検索値は「5」の場合

まず「完全一致で検索する場合」について解説します。

• 引数の値

• コード

WorksheetFunction.Match(5, ActiveSheet.Range("A1:A10"), 0)

「完全一致」はセル範囲に完全に一致する値の番号を返します。検索範囲のデータは、昇順や降順で並んでいなくても問題ありません。

検索値「5」と完全に一致する「A5」セルは検索範囲5番目のセルなので、返り値は「5」となります。

検索方法「1」（近似値以下最大値）、検索値は「3.5」の場合

次に検索方法「1」の「近似値以下最大値の検索」について解説していきます。

• 引数の値

• コード

WorksheetFunction.Match(3.5, ActiveSheet.Range("A1:A10"), 1)

「近似値以下最大値」はセル範囲内で近似値以下の最大値のセル番号を返します。また、検索範囲のデータが昇順になっている必要があります。

検索値「3.5」はA3セルの「3」より大きく、A4セルの「4」より小さいです。近似値以下の最大値が「3」になるため、検索範囲3番目のセルA3セルの値「3」が返ります。

検索方法「-1」（近似値以上最小値）、検索値は「3.5」の場合

最後に検索方法「-1」の「近似値以上最小値の検索」について解説していきます。

• 引数の値

• コード

WorksheetFunction.Match(3.5, ActiveSheet.Range("A1:A10"), -1)

「近似値以上最小値」はセル範囲内で近似値以上の最小値のセル番号を返します。また、検索範囲データは降順になっている必要があります。

検索値「3.5」はA8セルの「3」より大きく、A7セルの「4」より小さいです。近似値以上の最小値が「4」になるため、検索範囲7番目のセルA7セルの値「7」が返ります。

MATCH関数のエラー値と回避方法

MATCH関数のエラー値

便利なMATCH関数ですが、気を付けなければいけないこともあります。それはエラーが起き、マクロ処理が中断されることです。

どういう時にエラーがでるのか、例を挙げます。
すべて「Matchプロパティを取得できません。」というエラーとなります。

エラーはつきものですので、次に解説する回避方法も覚えてしまいましょう！

MATCH関数エラー回避方法

検索方法とデータ順の間違いについては、次の2点を確認すればいいので、気づきやすいかもしれません。
しかし実務では、検索値がデータにあるかを確認したい場合も多いでしょう。検索結果の有無で処理を分けるには、次のようなコードで対応できます。

'エラーが起きたら次のステップへ進むよう設定
On Error Resume Next
'MATCH関数の結果を変数に代入
Dim matchAns As Long
matchAns = WorksheetFunction.Match(3, ActiveSheet.Range("A1:A10"), 1)
'検索結果がない（エラーが発生した）場合はErr.Numberは「0」以外になる
If Err.Number <> 0 Then
     
    'エラー時の処理をこの位置に記載
    'Err.Numberを初期値に戻す
    Err.Number = 0
End If
'エラー時の処理を初期値へ戻す
On Error GoTo 0

2行目の「On Error Resume Next」でエラーが起きても次の処理へ進むよう設定しているのがポイントです。

何かエラーが起きた場合は「Err.Number」に0以外の値が設定されるので、9行目の「If」文の条件に「Err.Number <> 0」を指定しています。

エラーが起きても次の処理へ進む指定は、ピンポイントでないと異常処理へつながりかねないので、Err.Numberとともに初期値へ戻すことを忘れないようにしましょう。

INDEX関数と組み合わせてVLOOKUP関数のように使う

検索範囲から任意の値でセルを探せるMATCH関数ですが、実際の処理では「それだけでは足りない！」と感じることも多いでしょう。

特に複数列の表から、特定の値で検索、特定列の値を取り出したいという場面が多そうですね。そんな時に重宝するのはVLOOKUP関数です。
VBAでも、VLOOKUP関数は使えます。しかしVLOOKUP関数で検索できるのは、検索範囲の左端のみで、少々使い勝手が悪い面もあります。

そこで今回は、検索範囲のどの行・列からでも検索ができ、より自由度の高いINDEX関数とMATCH関数を組み合わせた方法をご紹介します。

まず、INDEX関数から解説していきます。

1. 基本構文

WorksheetFunction.Index(範囲, 行番号, 列番号)

2. コード例

WorksheetFunction.Index(ActiveSheet.Range("A1:B5"), 4, 1)

アクティブシート上のセル範囲「A1:B5」4行目・1列目の値を返します。

画像のようにセル範囲にデータがある場合、4行目・1列目の「いちご」を返すコードとなります。

ここまでの基本をおさえた上でVLOOKUP関数のように使う方法を確認していきましょう。

VLOOKUP関数は検索値を指定して、該当行の指定列を返す関数です。VBAでもINDEX関数の行指定をMATCH関数で行い、列を指定することで、VLOOKUP関数のように使えます。

次のコード例では「いちご」の「価格」を求めています。

'MATCH関数で使う検索範囲を変数「matchRng」に格納
Dim matchRng As Range
Set matchRng = ActiveSheet.Range("A1:A5")

'MATCH関数で「いちご」の行を取得して変数「matchRow」に格納
Dim matchRow As Long
matchRow = WorksheetFunction.Match("いちご", matchRng, 0)

'INDEX関数で使う検索範囲を変数に格納
Dim indexRng As Range
Set indexRng = ActiveSheet.Range("A1:B5")

'INDEX関数で先ほど取得した「いちご」の行（matchRow）と価格の列「2」を指定
Dim trgtPrice As Long
trgtPrice = WorksheetFunction.Index(indexRng, matchRow, 2)

少しコードが複雑になってきたので、順を追って解説します。

'MATCH関数で使う検索範囲を変数「matchRng」に格納
Dim matchRng As Range
Set matchRng = ActiveSheet.Range("A1:A5")

まず、変数「matchRng」にMATCH関数で検索したい範囲「A1:A5」を定義しています。

'MATCH関数で「いちご」の行を取得して変数「matchRow」に格納
Dim matchRow As Long
matchRow = WorksheetFunction.Match("いちご", matchRng, 0)

次に、MATCH関数を使って「いちご」が入力されているセルを検索し、変数「matchRow」に値を入れています。この時、MATCH関数の検索範囲「A1:A5」は、先ほど定義した変数「matchRng」を使って設定しています。

'INDEX関数で使う検索範囲を変数に格納
Dim indexRng As Range
Set indexRng = ActiveSheet.Range("A1:B5")

変数「indexRng」にINDEX関数で検索したい範囲「A1:B5」を定義しています。

'INDEX関数で先ほど取得した「いちご」の行（matchRow）と価格の列「2」を指定
Dim trgtPrice As Long
trgtPrice = WorksheetFunction.Index(indexRng, matchRow, 2)

変数「trgtPrice」に求めたい「いちご」の「価格」を定義しています。INDEX関数の値にはそれぞれ次の値を設定しています。
これでVLOOKUP関数のように、値を求めることができます。また、INDEX関数でも検索結果がない場合は、MATCH関数と同様に「Indexプロパティを取得できません。」というエラーが発生します。

MATCH関数の時と同じ方法で、エラー時の処理も対応しておきましょう。

VBAでのMATCH関数まとめ

今回はVBAでのMATCH関数の使い方をご紹介いたしました。

MATCH関数を使えば、任意の値からセル範囲を調べられるので、使いこなせば非常に便利です。また、今回ご紹介したエラー回避方法や、応用として解説したINDEX関数との組み合わせも理解しておくと、応用が効くのでおすすめです。

ぜひ、使ってみてくださいね！