Excel VBAで複数項目でvlookupする的なもの

前提

単純なものに関しては当然vlookupなどで対応できますが、複雑なものは色々頭をひねる必要が出てきます。
それはそれで楽しいのですが、VBAで検索するならループ回すだけなので簡単です。

ちなみにあんまりVBA書いたことありません。
DB関数でできるんじゃないかという説もありますけどどうするんでしょうか。

vlookupとは

一応、説明します。下記のようなワークシートがあるとして

-	A	B
1	緑のたぬき	東洋水産
2	赤いきつね	東洋水産
3	緑のどん兵衛	日清食品
4	赤いどん兵衛	日清食品
5	力うどん	東洋水産

=VLOOKUP(B9,A1:B5,2)

と書いて、B9に「緑のたぬき」と書くと「東洋水産」が返ってくるような関数です。

• 引数1、検索したい文字列（SQLでいうとWHILEの col = ?の?の部分）
• 引数2、検索対象の範囲。左端のカラムから引数1を検索する。
• 引数3、表示したいデータのカラム位置。引数2の範囲の中で、左端から始まります。数字は1から始まります。

何が問題か

まず検索条件が1つしか設定できません。なので下記のようなテーブルから、商品名とサイズを指定して値段を得るのができません。

-	A	B	C	D
1	商品名	メーカ	内容量	値段
2	緑のたぬき	東洋水産	50	100
3	緑のたぬき	東洋水産	100	200
4	赤いきつね	東洋水産	50	100
5	赤いきつね	東洋水産	100	200
6	緑のどん兵衛	日清食品	100	200
7	赤いどん兵衛	日清食品	100	200
8	力うどん	東洋水産	100	200

次に、検索対象が左端のカラム固定になるので、検索対象より左側のカラム値は検索できません。

VBA例

表A（マスタ）と表B（コピー先）を比較して、値段が違っていたら表Bのカラム色を変える、ということをやっています。DB設計書と詳細設計書の突合（もしくは自動的に穴埋め）するために作りました。SIバンザイ。

表A(Sheet2)

-	A	B	C	D
1	商品名	メーカ	内容量	値段
2	緑のたぬき	東洋水産	50	100
3	緑のたぬき	東洋水産	100	200
4	赤いきつね	東洋水産	50	100
5	赤いきつね	東洋水産	100	200
6	緑のどん兵衛	日清食品	100	200
7	赤いどん兵衛	日清食品	100	200
5	力うどん	東洋水産	100	200

表B(Sheet1)

-	A	B	C
1	商品名	内容量	値段
2	緑のたぬき	100	200
3	赤いきつね	50	100
4	赤いきつね	100	200
5	力うどん	100	200

Sub CheckButton_Click()
  For Each v In Sheet1.Range("C1:C8")
    name = v.Offset(0, -2).Text
    contents = v.Offset(0, -1).Text

    '先に背景色をリセット
    v.Interior.ColorIndex = 0

    If Len(contents) <> 0 Then
        '一致判定用フラグ
        found = False

        '値段が一致しなかったら色を変える
        For Each t In Sheet2.Range("A2:A8")
          If t.Text = name And t.Offset(0, 2).Text = contents Then

            found = True
            If t.Offset(0, 3).Text <> v.Text Then
              v.Interior.ColorIndex = 8

            End If

          End If
        Next

        '商品が見つからなかったら色を変える
        If found = False Then
          v.Interior.ColorIndex = 9
        End If

    End If
  Next
End Sub

感想

補完効くので意外と楽。ヘルプは充実しているけど、オブジェクトの扱いが特殊だしユースケースがないのでAPI調べるのがめんどくさい。Google頼み。