Excel VBA HLOOKUP関数で行を挿入して複数条件で検索する

ExcelVBA-基礎編

2019.10.03

VBAでワークシート関数のHLOOKUP関数を使用して、複数条件で検索する方法をご説明します。

「VLOOKUP関数」に非常によく似ている名前ですが、「VLOOKUP関数」は垂直方向(行)で検索するのに対して、「HLOOKUP関数」は水平方向(列)に対して検索をする関数です。

HLOOKUP関数を複数条件で使用するには、条件に指定する行を結合するために作業行を追加する必要があります。

作業行の追加方法も併せてご説明したいと思います。

基本的な使い方については「HLOOKUP関数の使い方と、エラー処理とループ方法」をご覧ください。

1.行を挿入して作業行を作る方法
2.作業行で検索値を結合する方法
3.行を挿入して複数条件でHLOOKUPで検索する方法

1.行を挿入して作業行を作る方法

HLOOKUP関数を複数条件で処理するためには、検索値を結合する行と検索範囲の検索行の結合するための行を追加する必要があります。

検索値については変数に格納する際に結合する場合は、行を追加せずに処理が可能です。

行の操作については「行の選択、取得、追加(挿入)、削除」をご覧ください。

行の操作は「Rows」プロパティか、「Range」プロパティです。

行の追加は「Insert」メソッドを使用します。

行を選択するにはRows(行番号)で選択するか、Rangeを使用する場合は「Range.EntireRow」で指定します。

行を挿入するサンプルコード

たとえば５行目に挿入する場合は次のように記述します。

「Rows」の場合

Sub Sample1()

Rows(5).Insert

End Sub

「Range.EntireRow」の場合

Sub Sample2()

Range("A5").EntireRow.Insert

End Sub

2.作業行で検索値を結合する方法

作業行を追加した場合に、次は検索値となる行を作業行で結合する方法です。

こちらは単純に「＆」を使用して結合するか、区切り文字を使用する場合は「Join関数」を使用して結合します。

次のようなデータを使用して説明します。

1行目と2行目を結合するために、赤線で囲われた3行目に1行追加して1行目と2行目を結合します。

行を追加して結合するサンプルコード

Sub Sample3()

Dim i As Long

Rows(3).Insert

For i = 2 To 7

    Cells(3, i) = Cells(1, i) & Cells(2, i)

Next i

End Sub

「Rows(3).Insert」で3行目に行を挿入して、「For~Next」で7列目までループしています。

ループについて不安な方は「「For~Next」「For Each In Next」「Do~Loop」ループの使い方」をご覧ください。

「Cells(3, i) = Cells(1, i) & Cells(2, i)」で追加した3行目に1行目と2行目を「＆」で結合しています。

3.行を挿入して複数条件でHLOOKUPで検索する方法

行の挿入、結合と順に説明してきましたが、それらを組み合わせて複数条件のHLOOKUP関数での検索をします。

先ほどと同じデータを使用して、「1行目と2行目」のリストから2つの条件に「7行目と8行目」が一致した結果を行目に入力します。

この場合3行目に1行追加して、1行目と2行目を結合します。

7行目と8行目に関しては変数を使用する事で行を追加しなくても対応できます。

気を付ける事は、1行追加すると以降の行がずれるため、指定する行数は追加後の行数を指定しなければいけません。

Sub Sample4()

Dim SearchWord  As String '検索値
Dim SearchRange As Range '検索範囲
Dim WordMaxCol  As Long '検索値最終列
Dim RangeMaxCol As Long '検索範囲最終列
Dim i           As Long

WordMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column  '検索値最終列
RangeMaxCol = Cells(8, Columns.Count).End(xlToLeft).Column '検索範囲最終列

Rows(3).Insert '行挿入

'文字列を結合
For i = 2 To WordMaxCol

    Cells(3, i) = Cells(1, i) & Cells(2, i) '1行目と2行目を結合

Next i

Set SearchRange = Range(Cells(3, 2), Cells(4, RangeMaxCol))  '検索範囲格納

For i = 2 To RangeMaxCol '検索値数分ループ

    SearchWord = Cells(8, i) & Cells(9, i) '8行目と9行目の結合した検索値格納
    
    Cells(10, i) = Application.WorksheetFunction.HLookup _
                (SearchWord, SearchRange, 2, False) '検索
                
Next i

End Sub