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

いつもの仕事を楽に
Sponsored Link

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

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

  • VBAでも値によってセル範囲を取得したい
  • ワークシート関数MATCHなら使えるけどVBAではやり方がわからない
  • VBAでもワークシート関数MATCHを使う方法が知りたい!

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

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

Sponsored Link

VBAでのMATCH関数の使い方

MATCH関数の基本的な使い方

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

  1. 基本構文
WorksheetFunction.Match(検索値, 検索範囲, 検索方法)

  1. コード例
WorksheetFunction.Match(5, ActiveSheet.Range("A1:A10"), 0)

アクティブシート上「A1:A10」の範囲から、整数「5」で完全一致した値を返しています。

検索方法の「0」は完全一致で検索することを意味しています。


画像のように上から5番目のA5セルに「5」があるので、コード例の数式は「5」を返します。
VBAMATCH関数検索イメージ
このように、指定した範囲で見つかった値を返すことができます。

また、検索方法には完全一致以外にもいくつかあるので、詳しく解説しますね!


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

検索方法には3つの値を指定できます。

それぞれの意味について、簡単に解説しますね!

  • 0 → 検索範囲を「完全一致」で検索
  • 1 → 検索範囲を「近似値以下最大値」で検索
  • -1 → 検索範囲を「近似値以下最小値」で検索


言葉だけでは伝わりづらいと思うので、検索範囲を「A1:A10」セルとした場合の具体的な例を順に紹介します。

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

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

  • 引数の値
  • 検索値:5
  • 検索範囲:ActiveSheet.Range(“A1:A10”)
  • 検索方法:0

  • コード
WorksheetFunction.Match(5, ActiveSheet.Range("A1:A10"), 0)


「完全一致」はセル範囲に完全に一致する値の番号を返します。

検索範囲のデータは、昇順や降順で並んでいなくても問題ありません。

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

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

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

  • 引数の値
  • 検索値:3.5
  • 検索範囲:ActiveSheet.Range(“A1:A10”)
  • 検索方法:1

  • コード
WorksheetFunction.Match(3.5, ActiveSheet.Range("A1:A10"), 1)


「近似値以下最大値」はセル範囲内で近似値以下の最大値のセル番号を返します。

また、検索範囲のデータが昇順になっている必要があります。


検索値「3.5」はA3セルの「3」より大きく、A4セルの「4」より小さいです。

近似値以下の最大値が「3」になるため、検索範囲3番目のセルA3セルの値「3」が返ります。
VBAMATCH関数近似値以下最大

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

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


  • 引数の値
  • 検索値:3.5
  • 検索範囲:ActiveSheet.Range(“A1:A10”)
  • 検索方法:-1

  • コード
WorksheetFunction.Match(3.5, ActiveSheet.Range("A1:A10"), -1)


「近似値以上最小値」はセル範囲内で近似値以上の最小値のセル番号を返します。

また、検索範囲データは降順になっている必要があります。


検索値「3.5」はA8セルの「3」より大きく、A7セルの「4」より小さいです。

近似値以上の最小値が「4」になるため、検索範囲7番目のセルA7セルの値「7」が返ります。
VBAMATCH関数近似値以上最小

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

MATCH関数のエラー値

便利なMATCH関数ですが、気を付けなければいけないこともあります。

それはエラーが起き、マクロ処理が中断されることです。

どういう時にエラーがでるのか、例を挙げます。

  • 検索値と一致する値が検索範囲にない
  • 検索方法「1」・データ降順
  • 検索方法「-1」・データ昇順

すべて「Matchプロパティを取得できません。」というエラーとなります。
VBAMATCH関数エラー
エラーはつきものですので、次に解説する回避方法も覚えてしまいましょう!


MATCH関数エラー回避方法

検索方法とデータ順の間違いについては、次の2点を確認すればいいので、気づきやすいかもしれません。

  • 検索方法「1」なら、データが昇順か
  • 検索方法「-1」なら、データが降順か

しかし実務では、検索値がデータにあるかを確認したい場合も多いでしょう。

検索結果の有無で処理を分けるには、次のようなコードで対応できます。

'エラーが起きたら次のステップへ進むよう設定
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(範囲, 行番号, 列番号)

  1. コード例
WorksheetFunction.Index(ActiveSheet.Range("A1:B5"), 4, 1)

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

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

ここまでの基本をおさえた上で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)
VBAINDEXをVLOOKUP関数として使う

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

'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関数の値にはそれぞれ次の値を設定しています。
  • 範囲:「indexRng」(=「A1:B5」)
  • 行:MATCH関数で求めた値を定義した「matchRow」(「いちご」の行=4)
  • 列:「価格」列を表す「2」

これでVLOOKUP関数のように、値を求めることができます。

また、INDEX関数でも検索結果がない場合は、MATCH関数と同様に「Indexプロパティを取得できません。」というエラーが発生します。

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


VBAでのMATCH関数まとめ

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

MATCH関数を使えば、任意の値からセル範囲を調べられるので、使いこなせば非常に便利です。

また、今回ご紹介したエラー回避方法や、応用として解説したINDEX関数との組み合わせも理解しておくと、応用が効くのでおすすめです。

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