- 公開日:
Excel VBAでVLOOKUP関数の参照を別シートにする方法
この記事では、Excel VBAでVLOOKUP関数の参照を別シートにする方法をご紹介します。
VLOOKUP関数でデータを抽出する際、日付やカテゴリでシートを分けている場合でも別シートを参照してデータ検索をすることができます。
以下では、複数シートを参照してデータを抽出する場合に追加したシートも自動で反映されるVBAコードをご紹介します。
開発タブを表示する

VBAを使用してVLOOKUP関数の参照を別シートにする方法をご紹介するので、上記の画像のような「開発」タブが表示されていない場合は、「開発」タブを表示させましょう。
詳細は、以下の記事を参考にしてみてください。
VLOOKUP関数の参照を別シートにするVBAコード
VLOOKUP関数で別シートを参照して検索結果を取得したい場合に、自動で結果を表示するVBAコードを下記に記載するのでコピーして使用してください。
詳しい使い方は、下記「Excel VBAでVLOOKUP関数の参照を別シートにする方法」セクションでご紹介しています。
Sub 検索値に紐づく値を別シートから取得する()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim lastRow1 As Long
' シート1とシート2を設定
Set ws1 = ThisWorkbook.Sheets("在庫検索")
Set ws2 = ThisWorkbook.Sheets("在庫表")
' シート1の最終行を取得
lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
' VLOOKUP関数を評価してセルに値を設定
For i = 2 To lastRow1 ' A2からA列の最終行までの範囲をカバー
Dim lookupResult As Variant
lookupResult = Application.VLookup(ws1.Cells(i, 1).Value, ws2.Range("A:D"), 4, False)
' 検索結果をセルに設定
If Not IsError(lookupResult) Then
ws1.Cells(i, 2).Value = lookupResult
Else
ws1.Cells(i, 2).Value = "" ' 検索値が見つからない場合、空白に設定
End If
Next i
End Sub
ご紹介した上記のコードについて、例を挙げてご説明します。

上記の画像は、「VLOOKUP関数の参照を別シートにするVBAコード」を実行した例です。
「在庫検索」シートのA2セル以降のA列に入力した値を「検索値」とします。
検索値を「在庫表」シートのA列からD列の範囲で検索して、検索値に紐づく4列目(例:在庫数)の値を「在庫検索」シートのB2セル以降のB列に表示します。
この際、検索値が存在しない場合はエラーではなく空白が返されます。
シート名やVLOOKUP関数の引数などはデータ範囲に合わせて調整してください。
Excel VBAでVLOOKUP関数の参照を別シートにする方法
VLOOKUP関数で別シートを参照して結果を取得する際、VBAを使って自動で行う方法をご紹介します。

①開発タブ、②Visual Basicの順に選択します。

「Microsoft Visual Basic for Applications」が開きました。①挿入タブ、②標準モジュールの順に選択します。

「標準モジュール」が開きました。上記セクション「VLOOKUP関数の参照を別シートにするVBAコード」をコピーして貼り付けてください。コードの説明も参考にしてみてください。

作成したマクロを保存します。①ファイルタブ、②上書き保存の順に選択します。

「名前を付けて保存」ダイアログボックスが表示されました。①保存先(例:デスクトップ)を選択します。②ファイル名(例:検索値に紐づく値を別シートから取得する)を入力し、③ファイルの種類でExcelマクロ有効ブックを選択し、④保存ボタンを押します。

Excelシートに戻ります。①開発タブ、③マクロの順に選択します。

「マクロ」ダイアログボックスが表示されました。①作成したマクロ(例:検索値に紐づく値を別シートから取得する)を選択し、②実行ボタンを押します。

「在庫検索」シートの検索値(例:商品コード)に紐づく「在庫表」の在庫数を表示することができました。「商品コード」10011は存在しない値なので、空白が表示されました。
VBAでVLOOKUP関数の参照を最終シートまでする方法
VLOOKUP関数の参照を複数シートからしたい場合、すべてのシートを参照する方法をご紹介します。また、シートを追加しても結果を自動で抽出することができます。

上記の右の表は、1日の商品別の売り上げが入力されています。2日から5日までのシートにも同じように商品別の売り上げが入力されています。
上記の左の表は、以下でご紹介するVBAコードを使って検索値の商品コードに紐づく「売上」の値を1日から5日のそれぞれのシートから抽出したものです。
以下では、VBAを使ってVLOOKUP関数の参照を最終シートまで自動でする方法をご紹介します。

①開発タブ、②Visual Basicの順に選択します。

「Microsoft Visual Basic for Applications」が開きました。
①挿入タブ、②標準モジュールの順に選択します。

「標準モジュール」が開きました。
以下のコードをコピーしてモジュールに貼り付けてください。
Sub 検索値に紐づく値を最終sheetまで取得する()
' 「商品別売り上げ」シートを指定
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Worksheets("商品別売り上げ")
' ループ用のカウンタ宣言
Dim i As Integer
' A列のセルからA2セル以降をループ
For i = 2 To targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
' 検索値を取得
Dim searchValue As Variant
searchValue = targetSheet.Cells(i, 1).Value
' 「最終シート」までループ
Dim j As Integer
For j = 2 To ThisWorkbook.Worksheets.Count
With ThisWorkbook.Worksheets(j)
' VLookup関数で別シートを参照し、エラーハンドリングで結果を「商品別売り上げ」シートに書き込む
Dim result As Variant
result = Application.VLookup(searchValue, .Range("A:E"), 5, False)
' 結果を「商品別売り上げ」シートに書き込む
targetSheet.Cells(i, j).Value = IIf(IsError(result) Or IsEmpty(searchValue), "", result)
End With
Next j
Next i
End Sub
上記のコードは、「商品別売り上げ」シートに入力された検索値を他のすべてのシートで検索し、検索値に紐づく値を返します。
「商品別売り上げ」シートのA2セル以降のA列に入力された値を検索値として、その他すべてのシートのA列からE列の範囲を検索します。
エラーがない場合はVLOOKUP関数の結果を返し、エラーの場合は空白を返して「商品売り上げ」のB列から横方向データを並べます。
い。

作成したマクロを保存します。
①ファイルタブ、②上書き保存の順に選択します。

「名前を付けて保存」ダイアログボックスが表示されました。
①保存先(例:デスクトップ)を選択し、②ファイル名(例:検索値に紐づく値を最終sheetまで取得する)を入力します。③ファイルの種類でExcelマクロ有効ブックを選択し、④保存ボタンを押します。

Excelシートに戻ります。
①開発タブ、②マクロの順に選択します。

「マクロ」ダイアログボックスが表示されました。
①作成したマクロ(例:検索値に紐づく値を最終sheetまで取得する)を選択し、②実行ボタンを押します。

「商品別売り上げ」シートの検索値(例:商品コード)に紐づく1日から5日までのそれぞれのシートから「売上」を抽出することができました。
「商品コード」10011は存在しない値なので、空白が表示されました。
以下では、「6日」シートを追加した際にマクロを実行するとどうなるのか試してみます。

画面下の「+」を押して新しいシートを追加します。
今回は、分かりやすいようにG1セルに見出し(例:6日)を入力して「格子」で囲んでいますが、あらかじめデータを追加することが分かっている場合は、見出しを入力しておくと便利です。

①+をダブルクリックしてシート名(例:6日)を変更し、②A1セルからデータを入力します。
今回は、VLOOKUP関数の検索範囲でA列からE列を指定しているので参照範囲がずれないようにA1セルからデータを入力します。

①「商品別売り上げ」シートに移動し、②開発タブ、③マクロの順に選択します。

「マクロ」ダイアログボックスが表示されました。
①作成したマクロ(例:検索値に紐づく値を最終sheetまで取得する)を選択し、②実行ボタンを押します。

追加した「6日」シートの結果を反映することができました。