【エクセル】VLOOKUPをVBAで使用する方法の完全ガイド

ExcelのVLOOKUP関数はVBAでも使用できます。VBAでVLOOKUP関数を使用するにはApplication.WorksheetFunctionを使います。もしくはセルに直接数式を埋め込む方法もあります。どちらの方法も引数は従来のVLOOKUP関数と同じままです。ここではVBAで使うVLOOKUP関数の使用例と細かいテクニックについて解説していきます。

VBAを使用するために「開発タブ」を表示させておく必要があります。デフォルトでは表示されていないため、まだ表示されていない方は開発タブの表示方法を参照してください。

関連リンク

VLOOKUP関数の構文

VBAでもVLOOKUP関数の設定項目は同じです。確認用のVLOOKUP関数の構文は下記

• 検索値：範囲の最初の列で検索する値をここで指定します。
• 範囲：目的のデータが含まれるテーブルをセル範囲で指定します。
• 列番号：取得したい値が範囲内で何列目にあるかの列番号を指定します。範囲の先頭列は１となります。
• 検索方法：検索値と完全に一致する値だけを検索する場合は　FALSE、近似値を含めて検索する場合はTRUEを指定します。省略するとTRUEが設定されます。0か1で設定することもできます。

使用例１：WorksheetFunctionを使ったシンプルなVLOOKUPの使用

まずはWorksheetFunctionを使って、VLOOKUPを行い、返される値をメッセージボックスで表示する例です。

Sub vlookup1()
    Dim id As String
    Dim marks As Long
    id = "A004"　'検索値を指定
    Set myrange = Range(" A1：C6")　'範囲を指定
    marks = Application.WorksheetFunction.VLookup(id, myrange, 3, False)　
    'marksに結果を代入
    MsgBox "ID：" & id & "の人は" & marks & "点を獲得しました" 'メッセージボックスに結果を表示
End Sub

コード解説

まず検索値を代入する変数「id」を文字列型として定義しておきます。

Dim id As String

最終的な返却値となる点数を入れる変数「marks」を長整数型として定義します。長整数型とは-2147483648～2147483647の整数を指します。

Dim marks As Long

idに値を代入します。ここで代入する値が検索値となります。

id = "A004"

次に第2引数用の目的のデータが含まれるテーブルのセル範囲を定義します。データはセルA1：C6に存在するため、範囲を次のように定義します。

Set myrange = Range(" A1：C6")

変数のWorksheet関数を使用してVLOOKUP関数を次のように記述します。VLOOKUP関数によって返される値がmarksに代入されます。

marks = Application.WorksheetFunction.VLookup(id, myrange, 3, False)

最後にVLOOKUPで取得したmarksをメッセージボックスで表示させる記述をします。

MsgBox "ID：" & id & "の人は" & marks & "点を獲得しました"

使用例２：WorksheetFunctionを使ってセルの値を取得しVLOOKUPを実施

生徒の名前とテストの点数に関するデータがあるとします。セルD2に名前を指定すると、生徒の点数がセルE2に返されるように、VBAでVLOOKUPのコードを記述する方法について解説します。

Sub vlookup2()
    Set Name = Range("D2")　'検索値を指定
    Set myrange = Range("A：B")　'範囲を指定
    Set result = Range("E2")　'結果を出力するセルを定義
    result.value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
End Sub

コード解説

検索値としての「D2」の値を取得し定義します。

Set Name = Range("D2")

列Aから列Bを範囲として定義します。

Set myrange = Range("A：B")

結果を出力するセルを定義します。

Set result = Range("E2")

最後にVLOOKUPで指定した名前で検索し、結果をE2セルに返すコードを記述します。

result.value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)

使用例３：ダイヤログ入力情報からVLOOKUPを実施（エラー処理あり）

生徒の名前とテストの点数に関するデータがあるとします。入力ボックス付きダイアログに指名を入力すると、その人の点数がメッセージボックスに表示されるVBAの作成方法について解説します。もしリストにない名前が入力された場合は「指定した名前は見つかりませんでした」と表示するようにします。

Sub vlookup3（）
    Dim Name As String '検索値用の変数「Name」を文字列で定義
    Dim result As Long '結果用の変数「result」を長整数型として定義
    Set myrange = Range("A：B") '範囲を指定
    Name = InputBox（“名前を入力してください"） 'メッセージボックスで入力される名前を変数「Name」に代入

    on Error GoTo Message 'エラー処理
        check:
            result = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
　　　　　　　　　　　　　　　　　　　　　　　　 '結果を変数「result」に代入
            MsgBox Name & "さんは" & result & "点を獲得しました"
　　　　　　　　　　　　　　　　　　　　　　　　 'メッセージボックスに結果を表示

        Message:
            If Err.Number = 1004 Then
                MsgBox（“指定した名前は見つかりませんでした”）
            End If
End Sub

コード解説

列Aと列Bを範囲として定義します。

Set myrange = Range("A：B")

名前を入力ボックス付きダイアログで検索値を取得します。

Name = InputBox（“名前を入力してください"）

VLOOKUPで入力した名前の点数を取得します。

result = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)

メッセージボックスに結果を表示します。

MsgBox Name & "さんは" & result & "点を獲得しました"

「on Error GoTo Message」を使って、名前がみつからなかった際にアラートがでるように記述します。

on Error GoTo Message
check:
result = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
MsgBox Name & "さんは" & result & "点を獲得しました"

Message:
If Err.Number = 1004 Then
MsgBox（“指定した名前は見つかりませんでした”）

使用例４：直接VLOOKUPの数式を埋め込んで使用

VBAではExcelで使う数式をそのまま埋め込むことが可能です。この例では直接数式を埋め込んで、結果が出たあとに数式を値に変換しています。

Sub vlookup4()
    
    Range("E2") = "=VLOOKUP(D2,A2:B6,2,FALSE)" '直接数式を挿入

    Range("E2").Value = Range("E2").Value '数式を値に変換

End Sub

VBAのVLOOKUP関数のエラー回避方法

WorksheetFunctionによるVLOOKUP関数を使用する際、検索値がない場合などに「プロパティが取得できません」とエラーが出る場合があります。
これらのエラーを回避するには下記のように「On Error Resume Next」ステートメントを使用します。このステートメントはマクロ実行時にエラーが発生しても中断せずに次のステートメントから処理を続けることができます。

コード例

WorksheetFunctionによるVLOOKUP関数を使用している場合は「On Error Resume Next」でエラーを回避します。下記コードでは検索値をセルから取得してVLOOKUPで検索する際にエラーが出た場合、「該当なし」と表示させる例です。

Sub vlookupError()
    Set Name = Range("D2")  '検索値を指定
    Set myrange = Range("A：B")  '範囲を指定
    Set result = Range("E2")  '結果を出力するセルを定義

    On Error Resume Next 'エラー回避のステートメント
        result.Value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
        If Err.Number <> 0 Then　'エラーナンバーが０以外の場合
            result.Value = "該当なし"　'結果のセルに「該当なし」と表示
            On Error GoTo 0　'エラー回避を解除
        End If
        
End Sub

詳細な解説は下記のリンクから参照してください。

VBAのVLOOKUP関数で別シートを参照する方法

VBAで別シートのセルを参照する場合は「Worksheets」を使います。
例えば「Worksheets(“Sheet1”).Range(“A1:B10”)」というようにWorksheetsでシート名を指定し、Rangeオブジェクトに「.（ドット）」でつなげて使います。

別シートのセルの取得する書式

Worksheets("シート名").Range(セル番号) 

コード例

検索値には「入力シート」の「A2」を指定し、範囲には「マスタデータ」シートの「A2:D51」を指定しています。後半の「 On Error Resume Next」以降はエラー回避のための記述となっています。検索値が見つからない場合などは「該当なし」と結果を表示するようにしています。

Sub vlookupAnotherSheet()
    Set Name = Worksheets("入力").Range("A2")  '検索値を指定
    Set myrange = Worksheets("マスタデータ").Range("A2:D51")  '範囲を指定
    Set result = Worksheets("入力").Range("B2")  '結果を出力するセルを定義

    On Error Resume Next 'エラー回避のステートメント
        result.Value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
        If Err.Number <> 0 Then  'エラーナンバーが０以外の場合
            result.Value = "該当なし"  '結果のセルに「該当なし」と表示
            On Error GoTo 0  'エラー回避を解除
        End If
End Sub

詳細な解説は下記のリンクを参照してください。

VBAのVLOOKUPを最終行まで繰り返し検索する方法

ExcelのVBAで必要な行数だけ繰り返しVLOOKUPで検索を行いたい場合は下記コードのように最終行を取得して、FOR文で繰り返し処理をします。

    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row 
        With Cells(i, "A")
            .Offset(0, 1) = WorksheetFunction.vlookup(.Value, Range("D2:G51"), 2, False)
        End With
    Next

詳細な解説は下記のリンクを参照してください。

VBAのVLOOKUPを高速化する方法

ExcelのVBAで大量のデータをVLOOKUP関数で取得する際に高速で検索する方法で一番おすすめなのが下記のようにセルに直接VLOOKUP関数の数式を埋め込む方法です。

コード例

セルに直接VLOOKUP関数の数式を代入して計算するサンプルコードです。処理にかかった秒数としては2.9秒で完了しています。エラー処理などの必要がないのでかなり楽です。

Sub vlookupSpeed()

    '処理速度計測用
    Dim startTime
    startTime = Timer
    
    'VLOOKUP関数の数式をセルに一括入力
    Range("B2:B10000") = "=VLOOKUP(A2,マスタデータ!$A$2:$C$100001,3,FALSE)"
    
    '数式を値に変換
    Range("B2:B10000").Value = Range("B2:B10000").Value

    'かかった時間を表示
    MsgBox Timer - startTime & " sec"

End Sub

より詳しい解説と、さらに早く計算する方法については下記のリンクを参照してください。

関連リンク

VLOOKUP関数を複数条件で検索する方法
VLOOKUP関数で別シートから参照する方法
VLOOKUPをVBAで使用する方法
VLOOKUP関数で#N/Aエラーでうまくいかない時の解決方法
VLOOKUP関数とIF関数を組み合わせて使う使用例まとめ
VLOOKUP関数の列番号とは？自動で列番号を変更する方法も
VLOOKUP関数で「0」やエラーを空白にする方法
INDEX関数とMATCH関数で対応値を検索する方法
VLOOKUP関数で「#NAME?」と表示された時の対処方法
VLOOKUP関数で合計、集計は不可能（SUMIFで条件指定し合計値を出す方法）
XLOOKUP関数の使い方(VLOOKUPの後継関数)
VLOOKUP関数で部分一致（〜含む）検索する方法

おすすめの解説動画

当記事とは違う書き方でVLOOKUPの関数をVBAで記述しています。シート内のセルをクリックしたときに、プログラムが動くようになっており、より便利な記述の仕方を解説しているのでおすすめです。

ショートカットキーの練習できます

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。