VBA ユーザ定義関数による複数条件のVlookup

VBAを使った複数条件のVlookup

次のようなデータテーブルを考えてみましょう。

Excelの標準的なVlookup関数は、次のような形式です。

VLOOKUP(“”Mark”, B6:G12″,2,FALSE)

とすると、”Brown “が返されます。

しかし、2つ以上の条件、例えば上記のテーブルの姓、名、年齢について調べたい場合はどうでしょうか？次のようなUDF(ユーザ定義関数)を使えば、これが可能になります。

Function ThreeParameterVlookup(Data_Range As Range, Col As Integer, Parameter1 As Variant, Parameter2 As Variant, Parameter3 As Variant) As Variant
'変数の宣言
Dim Cell
Dim Current_Row As Integer
Dim No_Of_Rows_in_Range As Integer
Dim No_of_Cols_in_Range As Integer
Dim Matching_Row As Integer

'デフォルトの回答をN/Aに設定する
ThreeParameterVlookup = CVErr(xlErrNA)
Matching_Row = 0
Current_Row = 1

No_Of_Rows_in_Range = Data_Range.Rows.Count
No_of_Cols_in_Range = Data_Range.Columns.Count

'Colが範囲内の列数より大きいかどうかを確認する

If (Col > No_of_Cols_in_Range) Then
ThreeParameterVlookup = CVErr(xlErrRef)
End If
If (Col <= No_of_Cols_in_Range) Then
Do
If ((Data_Range.Cells(Current_Row, 1).Value = Parameter1) And _
(Data_Range.Cells(Current_Row, 2).Value = Parameter2) And _
(Data_Range.Cells(Current_Row, 3).Value = Parameter3)) Then
Matching_Row = Current_Row 
End If
Current_Row  = Current_Row  + 1
Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0))

If Matching_Row <> 0 Then 
ThreeParameterVlookup = Data_Range.Cells(Matching_Row, Col)
End If

End If

End Function

以下のような構文になります。

ThreeParameterVlookup(Data_Range, Col , Parameter1, Parameter2 , Parameter3 )

ここで

– Data_Range は、データの範囲です。
– Col は、必要な列の整数値です。
– Parameter1、Parameter2、Parameter3 は、それぞれ最初の 3 つの列の値です。

となります。つまり、

=ThreeParameterVlookup(B6:G12,6, “Mark”, “Brown”,7) は、”Mark”, “Brown”, 7という3つの条件と一致する行の 6 列目を参照し、 “Tolworth” を返します。

なお、この関数は動的な名前付き範囲でも動作します。

=ThreeParameterVlookup(named_range,6, “Adrian”, “White”,7) は、名前付き範囲 “Named_Range” を設定すると、 “Chessington” を返します。

もしExcelが一致するものを見つけられない場合は、デフォルトで “N/A “が返されます。実際、この関数は最初からN/Aを想定しており、完全に一致するものを見つけたときだけ値を変更します。

また、Colの値が列の数を超えている場合は、参照エラーが発生します。 このチュートリアルの .XLSM ファイルをダウンロードするには、ここをクリックしてください。

<<VBAのサンプルに戻る