概要
F_XLookUp_2Colは、Excel VBAで二次元配列を検索し、2つの条件に一致した行から指定列の値を返す汎用関数です。
Excel標準のXLOOKUPは便利ですが、VBA内で「列1がこの値、列2がこの値」という条件を配列上で扱いたい場面では、毎回ループを書くことになりがちです。この関数を使うと、検索対象の配列、返したい列、検索値と検索列を渡すだけで、2条件検索を1行に近い形で呼び出せます。
使う場面
- マスタ表を配列に読み込んでから、2つのキーで値を取得したい
- 顧客コードと商品コード、日付と区分など、1列だけでは特定できないデータを検索したい
- セルを1件ずつ読みに行かず、配列上で高速に検索したい
- 数字が文字列として入っている場合でも、できるだけ同じ値として判定したい
使用例
下の例では、MasterシートのA1:D100を配列に読み込み、1列目が"東京"、2列目が"A001"に一致する行から、4列目の値を取得します。
Dim Data As Variant
Dim Result As Variant
Data = Worksheets("Master").Range("A1:D100").Value
Result = F_XLookUp_2Col(Data, 4, "東京", 1, "A001", 2)
If IsEmpty(Result) = False Then
MsgBox Result
End Ifコピー可能な実装コード
' === Module: ModWSFunction ===
Public Function F_XLookUp_2Col(ByRef Array2D As Variant, _
ByRef ReturnCol As Long, _
ByRef Value1 As Variant, _
ByRef SearchCol1 As Long, _
ByRef Value2 As Variant, _
ByRef SearchCol2 As Long) _
As Variant
'Xlookup関数の応用で、検索列を2つ指定する
'20250410
'引数
'Array2D ・・・対象の二次元配列
'ReturnCol ・・・値を取得対象の列
'Value1 ・・・検索する値1
'SearchCol1・・・検索対象列番号1
'Value2 ・・・検索する値2
'SearchCol2・・・検索対象列番号2
Dim Output As Variant
Dim I As Long
Dim N As Long: N = UBound(Array2D, 1)
Dim Judge1 As Boolean
Dim Judge2 As Boolean
For I = 1 To N
'検索対象列1の判定
Judge1 = False
If Array2D(I, SearchCol1) = Value1 Then
Judge1 = True
ElseIf IsNumeric(Array2D(I, SearchCol1)) = True And IsNumeric(Value1) = True Then
If Val(Array2D(I, SearchCol1)) = Val(Value1) Then
'両方数値型で表される場合は、数値型に変換も行って判定
Judge1 = True
End If
End If
'検索対象列2の判定
Judge2 = False
If Array2D(I, SearchCol2) = Value2 Then
Judge2 = True
ElseIf IsNumeric(Array2D(I, SearchCol2)) = True And IsNumeric(Value2) = True Then
If Val(Array2D(I, SearchCol2)) = Val(Value2) Then
'両方数値型で表される場合は、数値型に変換も行って判定
Judge2 = True
End If
End If
'判定結果をもとに処理
If Judge1 = True And Judge2 = True Then
Output = Array2D(I, ReturnCol)
Exit For
End If
Next
F_XLookUp_2Col = Output
End Function初心者向けコード解説
Array2D As Variantは、検索対象の表データを受け取る引数です。ワークシート範囲を.Valueで取得すると、VBAでは二次元配列として扱えます。
ReturnColは、条件に一致した行から何列目の値を返すかを指定します。SearchCol1とSearchCol2は、1つ目と2つ目の検索対象列です。
For I = 1 To Nで配列の行を上から順に調べています。N = UBound(Array2D, 1)は、配列の1次元目、つまり行方向の最後の番号を取得しています。
Judge1とJudge2は、それぞれ1つ目の条件、2つ目の条件が一致したかを入れるBoolean変数です。両方がTrueになった行だけ、検索結果として扱います。
IsNumericとValを使っている部分では、セル側と検索値側がどちらも数値として読める場合に、数値へ変換して比較しています。これにより、"1"と1のような表記差があっても一致させやすくなります。
最後に、If Judge1 = True And Judge2 = True Thenで2条件がそろった行を見つけ、Output = Array2D(I, ReturnCol)で返したい列の値を取り出しています。
注意点
- 配列の列番号は、読み込んだ範囲内での列番号です。ワークシート全体の列番号と混同しないようにします。
- この関数は最初に一致した行の値を返します。同じ条件に複数行が一致する場合、2件目以降は返しません。
- 見つからなかった場合は、初期値のまま
Empty相当になります。呼び出し側でIsEmptyなどを使って判定します。 - 見出し行を含む範囲を渡す場合、見出し行も検索対象になります。必要に応じてデータ行だけの範囲を渡します。
まとめ
F_XLookUp_2Colを用意しておくと、VBA内で2条件検索を書くたびに同じループ処理を作る必要がなくなります。
マスタ参照、設定表の検索、複数キーによる値取得など、Excel業務マクロで繰り返し使いやすい汎用プロシージャです。