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

# 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` をリテラルで指定しているが、表の列見出し・行見出しを検索キーとしてセル参照式で指定して、コピペする場合に強みを発揮する。

## ソースコード

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

```vb: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` が必要となる。久々にコードを触ったら忘れていて、うまく動かなくて悩んだ。