ExcelでVLOOK関数がいまいち使いにくく、いちいちINDEX関数とMATCH関数で値を引くのが面倒なので、マクロで実装した

領域やテーブルから値を引っ張ってくるのはVLOOKUP関数が一般的。最近ではXLOOKUP関数なるものがあるようだが、会社で支給されているPCのMS-OfficeのライセンスはOffice2019なのでXLOOKUP関数はない。なので、XLOOKUP関数を使ったこともない。

VLOOKUP関数は便利なのだが以下の難点がある。

キー値が必ず領域/テーブルの一番左の列にある必要がある
マッチした行の値を引いてくるのに数値で何列目と指定する必要がある
- 横の列にコピペでず、列指定値をいちいちハンドで書き直す必要がある
- 何より何列目かを数えるのが面倒
- VLOOKUPを書くときだけR1C1形式表示にオプション指定して、また戻すのも面倒

文句ばかり言っていても埒が明かないので、INDEX(配列,MATCH(...),MATCH(...)) で書いていた表現を関数化してみた。

使い方

以下のような表 (セルB1が X、セルB2が AX と読んでくだされ) を使う。

	A	B	C	D
1		X	Y	Z
2	A	AX	AY	AZ
3	B	BX	BY	BZ
4	C	CX	CY	CZ

INDEX関数とMATCH関数で Y 列 B 行を検索する式は、=INDEX($A$1:$D$4,MATCH("B",$A$1:$A$4,0),MATCH("Y",$A$1:$D$1,0)) のようになる。
これを今回作成したmyLookUP関数で表すと =myLookUp($A$1:$D$4,"Y","B") となる。だいぶ簡略化できた。
行と列の指定順がINDEX関数と逆になっているのは、値を返す列をOptionalで指定するときに近くにあったほうがわかりやすいためで、例えば1列目ではなくX列をキーとしたい場合には =myLookUp($A$1:$D$4,"Y","BX","X") と指定する。

例では列のキー X や行キー B や BX をリテラルで指定しているが、表の列見出し・行見出しを検索キーとしてセル参照式で指定して、コピペする場合に強みを発揮する。

ソースコード

以下のコードをマクロとしてインポートする。関数名は適宜調整する。

myLookUp.bas

' INDEX関数とMATCH関数で配列にアクセスするのが面倒なので関数化
' オプションを指定するとキーを調べる列を変更できる
' WorksheetFunction.Matchを使いたくなかったのでFindメソッドで実装
Public Function myLookUp(InspectionArea As Range, ColumnKey As String, RowKey As String, Optional SearchHeader As String = "") As Variant
    Dim Row As Long, Column As Long, SearchColumn As Long
    Dim Area As Range, FoundCell As Range
    
    Dim Table As ListObject
    Set Table = InspectionArea.ListObject

    ' 範囲指定がテーブルの場合にはテーブル全体を参照する
    If Not Table Is Nothing Then
        Set Area = Table.Range
    Else
        Set Area = InspectionArea
    End If

    ' 調べる列を決める
    If SearchHeader = "" Then
        SearchColumn = 1
    Else
        Set FoundCell = Area.Rows(1).Find(SearchHeader, LookIn:=xlValues, LookAt:=xlWhole)
        If FoundCell Is Nothing Then
            ' 見つからない場合はエラーメッセージを表示して終了
            ' MsgBox "指定されたヘッダーが見つかりません。"

            myLookUp = CVErr(xlErrNA)
            Exit Function
        End If
        SearchColumn = FoundCell.Column - Area.Column + 1
    End If

    ' 範囲のキー列を調べる
    Set FoundCell = Area.Columns(SearchColumn).Find(RowKey, LookIn:=xlValues, LookAt:=xlWhole)
    If FoundCell Is Nothing Then
        ' 見つからない場合はエラーメッセージを表示して終了
        ' MsgBox "指定された行タイトルが見つかりません。"

        myLookUp = CVErr(xlErrNA)
        Exit Function
    End If
    Row = FoundCell.Row - Area.Row + 1

    ' 範囲の1行目を調べる
    Set FoundCell = Area.Rows(1).Find(ColumnKey, LookIn:=xlValues, LookAt:=xlWhole)
    If FoundCell Is Nothing Then
        ' 見つからない場合はエラーメッセージを表示して終了
        ' MsgBox "指定された列タイトルが見つかりません。"

        myLookUp = CVErr(xlErrNA)
        Exit Function
    End If
    Column = FoundCell.Column - Area.Column + 1

    myLookUp = Area.Cells(Row, Column)

End Function

Synopsis

myLookUp(InspectionArea, ColumnTitle, RowTitle [, SearchHeader])

引数	説明
InspectionArea	範囲 or テーブル
ColumnKey	値を返す列のキー (表題)
RowKey	検索値の行のキー (表題)
SearchHeader	RowKeyの検索対象が1列目ではない場合に検索する列の表題

戻り値

検索した値

行や列見出しに指定したキーがInspectionAreaに存在しない場合には#N/Aエラーとなる。

余談

はじめはINDEX関数とMATCH関数に倣ってWorksheetFunction.Matchメソッドを使用して実装した。それはそれでスッキリとしていてよかったのだが、エラーが発生するとマクロがいきなり中断してデバッグがままならない。Application.Matchというものものあるようなのだか、ネットで調べても謎仕様らしい。それにWorksheetFunctionは何だか気持ちが悪いので、RangeオブジェクトのFindメソッドで実装した。思ったより冗長になってしまった。

VBAのではオブジェクトの代入には明示的に Set が必要となる。久々にコードを触ったら忘れていて、うまく動かなくて悩んだ。