Excel VBAでVLookup関数を使う

2020年3月21日2022年1月23日

VLookup関数は指定した範囲の中から検索条件に一致したデータを検索し、取り出してくれる関数です。通常はExcelのセルの中に式を書いて、その式を下のセルにコピーしてデータの突き合せを行いますが、VBAを使うと式をコピーすることなく サンプルアプリのように「VLookup検索」ボタン を押すだけで検索条件に一致したデータを取り出すことができます。

VLookupメソッドを使ったサンプルアプリの説明

■ サンプルコードの処理内容
　「VLookup検索」ボタンを押すと、黄色い表のNo を検索して、該当する氏名を赤枠内にセットします。

サンプルアプリのソースコード　　コードの作成手順はこちら

Sub ボタン1_Click()

    'ワークシートを指定する
    Dim Sh1 As Worksheet
    Set Sh1 = ThisWorkbook.Worksheets("Sheet1")

    '検索範囲を指定する
    Dim Rng1 As Range
    Set Rng1 = Range(Sh1.Cells(6, 6), Sh1.Cells(20, 7))

   '変数を定義する
    Dim workEndR1, workTmpR1 As Long, tmpStr1 As String
   'データの最終行を取得する
    workEndR1 = Sh1.Cells(Rows.Count, 2).End(xlUp).Row
    
    '6行目からデータの最終行までループ
    For workTmpR1 = 6 To workEndR1
    
        '検索キーをセット
        tmpStr1 = Sh1.Cells(workTmpR1, 2).Value
        
        '検索にヒットしなかった場合はエラー処理へ
        On Error Resume Next
        
        '検索キーで検索範囲を検索してヒットした場合は3列目にセット
        Sh1.Cells(workTmpR1, 3).Value = Application.WorksheetFunction.VLookup(tmpStr1, Rng1, 2, False) '検索
        
        '検索にヒットしなかった場合は3列目に「ERROR」をセット
        If Err <> 0 Then
            Sh1.Cells(workTmpR1, 3).Value = "ERROR"
            Err.Clear
        End If
        
    Next
       
End Sub

VLookupメソッドの説明

VLookup関数は指定された範囲の一番左端の列を検索値で検索し、ヒットした値がある同じ行の指定した列番号にある値を返す関数です。
　Application.WorksheetFunction.VLookup( 検索値, 範囲, 列番号, 検索方法)
　 検索方法
　 FALSE：完全一致
　 TRUE ：検索キーワードに近い値も検索

例えば以下の例で説明すると
　Sub ボタン1_Click()
　　Range(“C6") = WorksheetFunction.VLookup(Range(“B6"), Range(“F6:G20"), 2, False)
　End Sub
セルＢ6にある検索値「001」で、検索範囲「F6:G20」の一番左端の列を完全一致で検索します。
検索範囲のＦ11セルにある「001」がヒットします。
列番号に「2」が設定されていますので「001」の隣にある2列目の「佐藤太郎」が返されて
Ｃ6セルにセットされます。