【Excel小ネタ】ExcelのMATCH関数は実は２つある？

ExcelのMATCH関数が実は２種類存在し、それぞれ動作が異なっていたので、記事でまとめてみました。

一般的なMATCH関数について

ExcelのMatch関数について少しおさらいをします。

上記表のようは商品一覧から、入力した商品名が何番目に存在するか検索したい場合に使用する関数が、MATCH関数です。

「目当ての商品名（E2セル）」に商品名リストに存在する商品名を入力すると…

「何番目にある？（D3セル）」に右側の商品リストから、入力した「リンゴ」の順番を取得しその番号を表示します。ここでは商品リストにリンゴという商品がリストの１番目に存在するので（項目名は除く）、１が表示されています。

Excelシート上で動作するMATCH関数の内容としては、

（数式） =MATCH(検査値, 検査結果, [照合の種類])
（指定） =MATCH($D$2, $A$2:$A$5, 0)
（訳） =MATCH(“リンゴ”の順番を, “商品リスト”から, “一致するものの順番を返して”)

となります。「目当ての商品名」を指定するセルがD2で、商品リストがA2からA5まで（A2:A5）で、最後の照合の種類はどのようなものを探せばいいのか指定をします。

「照合の種類」を１を指定した場合です。この場合、検査値53に対して商品リストの個数以下のうち最大の値が何番目かを検索することになるので、一番近く最大値である「いちご 50」は３番目なので３が結果となります。

VBA上のMATCH関数について

本題に入り、たとえば「何番目？」ボタンをクリックした場合、下記のキャプチャーのように目当ての商品のものがリストから何番目かメッセージで表示する機能を作成するとします。

その場合、「何番目」ボタンに下記のようなVBAコードを作成するわけですが、

Sub onClickBtnMatch()
    Dim index As Integer

    index = WorksheetFunction.match(Range("D2"), Range("A2:A5"), 0)

    MsgBox index & "番目です"
End Sub

上記のキャプチャーのように、リストに存在しないものを指定した場合はエラーになってしまいます。
なので、存在しない商品名を指定した場合は「該当商品は存在しません」と表示するように変更する機能をつけたいです。ですが、このWorksheetFunction.Matchは値が見つからなかった場合に強制的にデバッグメッセージを表示する動作になっており、少し面倒な書き方が必要になります。

Sub onClickBtnMatch()

    Dim index As Integer
    On Error Resume Next
    index = WorksheetFunction.match(Range("D2"), Range("A2:A5"), 0)
    Err.Clear 
    On Error GoTo 0

    Select Case index
        Case 0
            MsgBox "指定した商品が見つかりませんでした"
        Case Else
            MsgBox index & "番目です"
    End Select

End Sub

On Error Resume Nextや On Error Goto 0などを追加する形になります。
ですがこれらは、エラーが発生しても無条件で進む動作になっているためデバッグ作業が難しくなり、もし不具合があっても漏れてしまう恐れがあるので私個人的にはあまり好まない書き方です。

謎のApplication.Match関数

それで、もっといい方法があるかGoogle先生に聞いてみたところ「Application.Match」というWorksheetFunctionの方ではない同名のMatchがあるらしいです。

WorksheetFunction.Matchとは異なり、エラーになった場合はちゃんとError型の戻り値を返してくれます。

Sub onClickBtnMatch() 
    Dim index = Application.match(Range("D2"), Range("A2:A5"), 0) 
    
    If IsError(index) Then 
        MsgBox "指定した商品が見つかりませんでした" 
    Else 
        MsgBox index & "番目です" 
    End If 
End Sub

面倒なOn Error Resume Nextが不要になり、戻り値をIsErrorに入れることをで判定できるようになりました。

なぜMatch関数が２種類存在するかは不明で、Excelのライブラリの方にも、Microsoftのドキュメントの方にも載っていないものです。

参考にしたページ

MicrosoftのMatch関数ドキュメント
https://support.microsoft.com/ja-jp/office/match-%E9%96%A2%E6%95%B0-e8dffd45-c762-47d6-bf89-533f4a37673a