VLOOKUPを再現する

Excel の VLOOKUP 関数は、特定の値を検索して関連するデータを取得するのに非常に便利なツールです。 しかし、VBA（Visual Basic for Applications）を使用することで、さらに柔軟に、かつ自動化されたデータ検索が可能になります。 本記事では、VBA を使って VLOOKUP に近い操作を実現する方法について詳しく解説します。

VLOOKUP 関数の基本構造

まず、Excel の VLOOKUP 関数のおさらいです。

=VLOOKUP(検索値, 範囲, 列番号, [検索の型])

例えば、セル A1 にある値を範囲 B1:C10 から検索し、見つかった場合にその行の 2 列目（範囲 C 列）の値を返すには次のようにします。

=VLOOKUP(A1, B1:C10, 2, FALSE)

VBA の場合

全ての Excel 関数は VBA でも利用することが可能なため、Excel 上で実行されている VLOOKUP 関数を VBA から呼び出すことは可能です。

Application.WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, False)

WorksheetFunctionクラスに定義されているVlookup関数を呼び出すことで、VBA から VLOOKUP 関数を利用できます。

しかし、より柔軟にデータ検索を行いたい場合、VBA で独自の関数を作成することが有用です。次のセクションでは、VBA で VLOOKUP 関数に似た機能を持つカスタム関数を作成する方法を紹介します。

VBA で VLOOKUP を再現するコード

次に、VBA で同じ操作を行う方法を紹介します。

Function CustomVlookup(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If cell.Value = lookup_value Then
            CustomVlookup = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookup = "Not Found"
End Function

上記のコードは、VBA で VLOOKUP 関数に似た機能を持つカスタム関数 CustomVlookup を定義しています。使い方は以下の通りです。

Sub TestCustomVlookup()
    Dim result As Variant
    result = CustomVlookup("検索値", Sheets("Sheet1").Range("B1:C10"), 2)
    MsgBox result
End Sub

応用テクニック

ここまででは、わざわざ独自の関数を作る必要はありませんが、VBA を使うことで様々な応用テクニックが可能になります。

部分一致検索

標準の VLOOKUP では完全一致しかサポートしていませんが、VBA を使えば部分一致検索も可能です。

Function CustomVlookupPartial(lookup_value As String, table_array As Range, col_index_num As Integer) As Variant
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If InStr(1, cell.Value, lookup_value) > 0 Then
            CustomVlookupPartial = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookupPartial = "Not Found"
End Function

完全一致と部分一致の使い分け

必要に応じて、完全一致と部分一致を使い分けられるようにすることも重要です。例えば、ユーザーが引数で指定する方法があります。

Function CustomVlookupFlexible(lookup_value As Variant, table_array As Range, col_index_num As Integer, Optional partial_match As Boolean = False) As Variant
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If (partial_match And InStr(1, cell.Value, lookup_value) > 0) Or (Not partial_match And cell.Value = lookup_value) Then
            CustomVlookupFlexible = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookupFlexible = "Not Found"
End Function

エラーハンドリング

データが見つからなかった場合にエラーが発生しないようにするため、適切なエラーハンドリングも行います。

Function CustomVlookupWithErrorHandling(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
    On Error GoTo ErrorHandler
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If cell.Value = lookup_value Then
            CustomVlookupWithErrorHandling = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookupWithErrorHandling = "Not Found"
    Exit Function

ErrorHandler:
    CustomVlookupWithErrorHandling = "Error: " & Err.Description
End Function

実際の使用例

データベース検索

たとえば、社員 ID を基に社員の情報を検索する場合です。

Sub SearchEmployeeInfo()
    Dim employeeID As String
    Dim result As Variant
    employeeID = InputBox("社員IDを入力してください:")
    result = CustomVlookup(employeeID, Sheets("社員データ").Range("A1:D100"), 2)

    If result = "Not Found" Then
        MsgBox "社員情報が見つかりませんでした。"
    Else
        MsgBox "社員名: " & result
    End If
End Sub

商品情報の自動入力

商品コードを入力すると、その情報が自動でセルに入力されるようにする場合です。

Sub AutoFillProductInfo()
    Dim productCode As String
    Dim productName As Variant
    Dim productPrice As Variant

    productCode = Cells(2, 1).Value ' A2セルに商品コードが入力されていると仮定
    productName = CustomVlookup(productCode, Sheets("商品データ").Range("A1:C100"), 2)
    productPrice = CustomVlookup(productCode, Sheets("商品データ").Range("A1:C100"), 3)

    If productName = "Not Found" Or productPrice = "Not Found" Then
        MsgBox "商品情報が見つかりませんでした。"
    Else
        Cells(2, 2).Value = productName ' B2セルに商品名を入力
        Cells(2, 3).Value = productPrice ' C2セルに価格を入力
    End If
End Sub

まとめ

VBA を使えば、Excel の VLOOKUP 関数に制限されない柔軟なデータ検索が可能になります。 これらを組み合わせてカスタム関数を作ることで、より業務に合った処理をカンタンに実行可能になります。 ぜひ、自分のプロジェクトに取り入れてみてください。