【Excel VBA】VLOOKUPで別シートの参照（最終行まで繰り返し・ループ・変数・ワークシート関数・引数の書き方）

エクセルVBAでVLOOKUPを使う際、参照先のデータが別シートにある場合の書き方がわからずに困った経験はないでしょうか。

通常のセル数式でのVLOOKUPとVBAで記述するVLOOKUPでは、別シートの参照方法に違いがあります。

正しい書き方を覚えておくことで、シートをまたいだ柔軟な検索処理をVBAで自在に実装できるようになります。

この記事では【Excel VBA】VLOOKUPで別シートを参照する方法（ワークシート関数・シートをまたいだ検索・引数の書き方）について解説していきます。

ポイントは

です。

それでは詳しく見ていきましょう。

VBAでVLOOKUPを使う基本【WorksheetFunction.VLookup】

VBAでエクセルのワークシート関数を呼び出すには、WorksheetFunctionオブジェクトを経由する必要があります。

VLOOKUPも同様で、「WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法)」という形式で記述します。

セル数式のVLOOKUPとほぼ同じ引数構成ですが、範囲の指定方法がVBAのRange記述に変わる点が大きな違いです。

今回解説で使用するサンプルデータを確認しましょう。

「マスタ」シートにはA列に商品コード、B列に商品名、C列に価格が入力されています。

「入力」シートのA列に商品コードを入力すると、VBAがマスタシートを参照してB列に商品名・C列に価格を自動表示する仕組みを実装します。

1行目がヘッダー、2行目以降にデータが入力されているこのマスタシートを元に解説します。

WorksheetFunction.VLookupの引数の書き方

WorksheetFunction.VLookupの引数は4つあり、それぞれの役割を正確に理解しておくことが重要です。

第1引数は検索値で、マスタデータの中から探したい値またはその値が入ったセルを指定します。

第2引数は検索範囲で、マスタデータ全体のRange指定をここに記述します。

別シートを参照する場合はここにWorksheets(“シート名”).Rangeという形式を用います。

第3引数は返す値の列番号で、範囲の左端列を1として数えた番号を整数で指定します。

第4引数は検索方法で、完全一致を行う場合はFalse（または0）を指定します。

同一シート内のVLOOKUPの基本形を確認する

まず比較のために、同一シート内でVLOOKUPを使う基本形を確認します。

Sub VlookupSameSheet()
    Dim ws As Worksheet
    Dim result As Variant
    Set ws = ThisWorkbook.Sheets("マスタ")
    
    result = WorksheetFunction.VLookup(1002, ws.Range("A2:C6"), 2, False)
    MsgBox result
End Sub

このマクロの動作を詳しく説明します。

「Dim result As Variant」でVariant型の変数resultを宣言しています。

VLOOKUPの戻り値は検索結果によって文字列にも数値にもなり得るため、どの型でも格納できるVariant型が適しています。

「WorksheetFunction.VLookup(1002, ws.Range(“A2:C6”), 2, False)」では商品コード1002をマスタシートのA2:C6の範囲で検索し、左から2列目（B列：商品名）の値を取得しています。

最後の引数FalseはFALSE（完全一致）を意味し、完全に一致するコードが見つかった場合のみ値を返します。

このマクロを実行すると「カツオ」という文字列がMsgBoxに表示された状態になります。

商品コード1002に対応するB列の値「カツオ」が正しく取得できていることが確認できます。

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

同一シート内での使い方が確認できたところで、本題の別シート参照に移ります。

VBAで別シートのデータを範囲として指定するには、Worksheets(“シート名”).Range(“セル範囲”)という形式を使います。

これをVLOOKUPの第2引数にそのまま渡すことで、別シートのデータを検索範囲として指定できます。

別シートを参照するVLOOKUPのコード例

「入力」シートのA2セルに入力された商品コードを元に、「マスタ」シートから商品名と価格を取得してB2・C2に書き込むコードを実装します。

Sub VlookupOtherSheet()
    Dim wsInput As Worksheet
    Dim wsMaster As Worksheet
    Dim searchCode As Variant
    Dim resultName As Variant
    Dim resultPrice As Variant
    
    Set wsInput = ThisWorkbook.Sheets("入力")
    Set wsMaster = ThisWorkbook.Sheets("マスタ")
    
    searchCode = wsInput.Range("A2").Value
    
    resultName = WorksheetFunction.VLookup( _
        searchCode, wsMaster.Range("A2:C6"), 2, False)
    resultPrice = WorksheetFunction.VLookup( _
        searchCode, wsMaster.Range("A2:C6"), 3, False)
    
    wsInput.Range("B2").Value = resultName
    wsInput.Range("C2").Value = resultPrice
End Sub

このマクロの動作を順を追って説明します。

「Set wsInput = ThisWorkbook.Sheets(“入力”)」と「Set wsMaster = ThisWorkbook.Sheets(“マスタ”)」でそれぞれのシートを変数にセットしています。

シートを変数に格納しておくことで、コードの中で何度もシート名を書く必要がなくなり、シート名が変わった場合もここを1箇所修正するだけで済みます。

「searchCode = wsInput.Range(“A2”).Value」で入力シートのA2セルの値（商品コード）を変数に格納しています。

「WorksheetFunction.VLookup(searchCode, wsMaster.Range(“A2:C6”), 2, False)」では第2引数にwsMaster.Range(“A2:C6”)を指定することで、マスタシートのA2:C6を検索範囲としています。

列番号2でB列（商品名）、列番号3でC列（価格）をそれぞれ取得し、入力シートのB2・C2に書き込んでいます。

このマクロを実行すると、入力シートのA2に「1003」が入力されていた場合、B2に「ハラス」・C2に「980」が自動的に書き込まれた状態になります。

別シートへの参照をwsMaster.Range()という形で渡すだけで、シートをまたいだVLOOKUPが実現します。

Alt+F8で実行しましょう。

以下のよう自動で別シートのVLOOKUPhが反映されます。

VBAのVLOOKUPでエラー処理を実装する方法

VLOOKUPで検索値が見つからない場合、WorksheetFunction.VLookupはエラーを発生させてマクロが強制終了してしまいます。

実務で使えるVLOOKUP処理には必ずエラー処理を組み込むことが不可欠です。

On Errorを使ったエラー処理の実装

Sub VlookupWithErrorHandling()
    Dim wsInput As Worksheet
    Dim wsMaster As Worksheet
    Dim searchCode As Variant
    Dim resultName As Variant
    
    Set wsInput = ThisWorkbook.Sheets("入力")
    Set wsMaster = ThisWorkbook.Sheets("マスタ")
    
    searchCode = wsInput.Range("A2").Value
    
    On Error Resume Next
    resultName = WorksheetFunction.VLookup( _
        searchCode, wsMaster.Range("A2:C6"), 2, False)
    On Error GoTo 0
    
    If IsEmpty(resultName) Or IsError(resultName) Then
        wsInput.Range("B2").Value = "該当なし"
    Else
        wsInput.Range("B2").Value = resultName
    End If
End Sub

このマクロの動作を詳しく説明します。

「On Error Resume Next」はこの行以降でエラーが発生しても処理を止めずに次の行へ進む指示で、VLOOKUPのエラーを握りつぶすために使います。

VLOOKUPの実行後に「On Error GoTo 0」を記述することで、エラー無視の範囲をVLOOKUPの1行だけに限定しています。

エラー無視の範囲を最小限に絞ることは、予期しないエラーを見落とさないための重要な設計です。

「If IsEmpty(resultName) Or IsError(resultName) Then」でVLOOKUPが値を取得できなかった場合を判定しています。

検索値が見つからずresultNameに何も格納されなかった場合、またはエラー値が入っていた場合は「該当なし」という文字列をB2に書き込みます。

このマクロを実行したとき、A2に存在する商品コード（例：1001）が入力されていればB2に正しい商品名が表示され、存在しないコード（例：9999）が入力されていればB2に「該当なし」と表示された状態になります。

On Error Resume NextとOn Error GoTo 0の組み合わせは、エラーが起きうる処理を1行だけ安全に実行するための定番パターンとして覚えておきましょう。

VBAのVLOOKUPで複数行を一括処理する方法

実務では1行だけでなく複数行に対してVLOOKUPを繰り返し適用したい場面が多くあります。

ループ処理と組み合わせることで、複数行のデータを一括で変換・補完する処理が実現します。

ループでVLOOKUPを複数行に適用するコード例

Sub VlookupLoop()
    Dim wsInput As Worksheet
    Dim wsMaster As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim searchCode As Variant
    Dim resultName As Variant
    Dim resultPrice As Variant
    
    Set wsInput = ThisWorkbook.Sheets("入力")
    Set wsMaster = ThisWorkbook.Sheets("マスタ")
    
    lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        searchCode = wsInput.Cells(i, 1).Value
        
        On Error Resume Next
        resultName = WorksheetFunction.VLookup( _
            searchCode, wsMaster.Range("A2:C6"), 2, False)
        resultPrice = WorksheetFunction.VLookup( _
            searchCode, wsMaster.Range("A2:C6"), 3, False)
        On Error GoTo 0
        
        If IsEmpty(resultName) Then
            wsInput.Cells(i, 2).Value = "該当なし"
            wsInput.Cells(i, 3).Value = "該当なし"
        Else
            wsInput.Cells(i, 2).Value = resultName
            wsInput.Cells(i, 3).Value = resultPrice
        End If
        
        resultName = Empty
        resultPrice = Empty
    Next i
End Sub

このマクロの動作を詳しく説明します。

「lastRow = wsInput.Cells(wsInput.Rows.Count, “A”).End(xlUp).Row」で入力シートのA列の最終行番号を取得しています。

「For i = 2 To lastRow」でi=2（2行目）からlastRowまで1行ずつループしています。

1行目はヘッダーのためi=2から始めている点がポイントです。

「wsInput.Cells(i, 1).Value」でi行目のA列（商品コード）を取得し、searchCodeに格納しています。

ループのたびにresultNameとresultPriceを「resultName = Empty」でリセットしているのは、前のループで取得した値が次のループに引き継がれるのを防ぐためです。

このマクロを実行すると、入力シートのA列に複数の商品コードが入力されていた場合、2行目から最終行まで順番にVLOOKUPが適用され、B列に商品名・C列に価格がまとめて書き込まれた状態になります。

存在しないコードが含まれていた行のB列・C列には「該当なし」が表示され、どの行で検索に失敗したかが一目でわかります。

VBAのVLOOKUPで別ブックのシートを参照する方法

参照先のマスタデータが別のExcelブックに保存されている場合も、VBAでは対応できます。

別ブックを参照するには、Workbooks(“ブック名”).Worksheets(“シート名”).Range(“範囲”)という形式でVLOOKUPの第2引数を指定します。

別ブック参照のコード例と注意点

Sub VlookupOtherWorkbook()
    Dim wsInput As Worksheet
    Dim wbMaster As Workbook
    Dim wsMaster As Worksheet
    Dim searchCode As Variant
    Dim resultName As Variant
    
    Set wsInput = ThisWorkbook.Sheets("入力")
    
    '別ブックがすでに開いている前提
    Set wbMaster = Workbooks("マスタデータ.xlsx")
    Set wsMaster = wbMaster.Sheets("マスタ")
    
    searchCode = wsInput.Range("A2").Value
    
    On Error Resume Next
    resultName = WorksheetFunction.VLookup( _
        searchCode, wsMaster.Range("A2:C6"), 2, False)
    On Error GoTo 0
    
    If IsEmpty(resultName) Then
        wsInput.Range("B2").Value = "該当なし"
    Else
        wsInput.Range("B2").Value = resultName
    End If
End Sub

このマクロの動作を詳しく説明します。

「Set wbMaster = Workbooks(“マスタデータ.xlsx”)」で別ブックのWorkbookオブジェクトを変数wbMasterにセットしています。

このコードは「マスタデータ.xlsx」がすでに開いている状態を前提としており、閉じている場合はWorkbooks.Openで事前に開く処理が必要です。

「Set wsMaster = wbMaster.Sheets(“マスタ”)」でそのブックの中の「マスタ」シートを取得しています。

以降の処理は同一ブックの別シートを参照する場合とまったく同じ記述で動作します。

このマクロを実行すると、別ブック「マスタデータ.xlsx」のマスタシートから検索結果が取得され、入力シートのB2に商品名が書き込まれた状態になります。

別ブックへのVLOOKUP参照では、参照先ブックが開いていない場合はエラーになるため、実務ではWorkbooks.Openによる事前オープンとクローズ処理もセットで実装しておくと安心です。

まとめ　Excel VBAのVLOOKUPで別シートを参照する方法（ワークシート関数・シートをまたいだ検索・引数の書き方）

Excel VBAでVLOOKUPを使って別シートを参照する方法をまとめると

・基本構文：WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)で記述する
・別シート参照：第2引数にWorksheets(“シート名”).Range(“範囲”)を指定するだけでシートをまたいだ検索が実現する
・エラー処理：On Error Resume NextとOn Error GoTo 0でVLOOKUP前後を挟み、IsEmptyで結果を確認する
・複数行処理：Forループとlastrowを組み合わせることで全行一括処理が可能
・別ブック参照：Workbooks(“ブック名”).Worksheets(“シート名”).Rangeの形式で別ブックも参照できるが、事前オープンが必要

これらの書き方を組み合わせることで、シートをまたいだ柔軟なVLOOKUP処理をVBAで自在に実装できます。

まずはWorksheetFunction.VLookupの基本構文と、別シート範囲の指定方法をしっかり覚えることから始めましょう。

エラー処理を必ず組み込む習慣をつけることで、検索値が存在しないケースにも安定して対応できるマクロが書けるようになります。

ぜひ今回のコードをベースに、実際のデータに合わせてカスタマイズしてみてください。