VLOOKUP関数で別シートを参照する方法についてわかりやすく解説

ExcelのVLOOKUP関数で値を別シートから参照したい場合は、参照文字列を「Sheet1!A1」のように「シート名 ＋ ! ＋ セル番号」の形にすることで参照できます。またExcelでは数式の入力途中に別シートの範囲をドラッグするだけで別シートのデータ設定が自動入力されます。ここでは実例を交えてVLOOKUP関数で別シートのセルを参照させる方法について解説します。

関連リンク

別シートのセル参照の記載ルール

Excelのセル参照の記載ルールの解説をします。例えば「=Sheet1!A1」のようにシート名の後に「!」を入力し、その後ろにセル番号を入力すると別シートを参照できます。

しかし、上記のようにシート名からタイプして入力するのは面倒なので、直接別シートのセルをクリックしてセル参照させるのが一般的です。

VLOOKUP関数で別シートからセル参照する方法

VLOOKUP関数で別シートからセル参照する方法について解説します。下記の例ではIDのみ記載されている「売れた商品リスト」のIDを元に別シートの「商品マスタ」からVLOOKUP関数で「商品名」を引っ張ってこようとしています。

書式

=VLOOKUP(検索値,シート名!データ範囲,列番号,FALSE)

数式例

=VLOOKUP(A2,マスタデータ!$A$2:$D$51,2,FALSE)

手順１：空白セルに「=VLOOKUP(」と入力

情報を引っ張って表示するために空白セルを選択し、「=VLOOKUP(」と入力します。入力途中でサジェストが表示されるのでTABキーを押下すると自動で入力されるので便利です。

手順２：検索値として、同じ行の「商品ID」を選択しセル参照

検索値として、同じ行の「商品ID」のセルをクリックし、セル参照させます。

手順３：「,」で区切る

「,」で区切って、第１引数の入力を完了させます。

手順４：「マスタデータ」シートを選択し、範囲をドラッグして選択

VLOOKUPの２つ目の設定値「範囲」は「マスタデータ」シート内にあるので、「マスタデータ」シートを選択して開き、範囲をドラッグして選択します。数式バーを見てみると「シート名」+「!」＋「セル番号」の形式で自動入力されているはずです。

手順５：「F4」キーを押下し、指定した範囲を絶対参照に変更

そのまま「F4」キーを押下し、指定した範囲を絶対参照に変更します。絶対参照にする理由はあとでVLOOKUPの数式をコピーする際に範囲がずれないようにするためです。

関連リンク
絶対参照とは？使い方をわかりやすく解説
絶対参照に切り替えるショートカットキー

手順６：「,」で区切り、列番号を指定

「,」で区切り、列番号を指定します。参照しようとしている「商品名」が商品IDから数えて２番目にあるので「２」と設定します。

手順７：「,FALSE)」と入力

「,FALSE)」と入力します。第４引数の検索方法は「FALSE」を指定すると完全一致したデータを抽出します。近似一致を指定する場合は「TRUE」を指定します。

手順８：ENTERキーを押下し、数式を決定

手順９：他の行に数式をコピーする

他の行に数式をコピーします。数式のコピーにはオートフィル機能が便利です。数式が入ったセルを選択しなおして、右下のフィルハンドルを下にドラッグすることで数式をコピーできます。

関連リンク
オートフィルの使い方まとめ

VLOOKUP関数で別シートからセル参照できました。

関連リンク

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

別シートのデータが増えても問題なくVLOOKUPで検索できるようにする方法

VLOOKUP関数で指定したデータ範囲にデータが追加されたときに通常の参照方法では正しく検索できない場合があります。そうならないように、データが増えても問題なくVLOOKUP関数が動作する方法について解説します。この方法によりメンテナンス性の優れた関数を作成できます。

書式

=VLOOKUP(検索値,シート名!データ範囲,列番号,FALSE)

数式例

=VLOOKUP(A2,マスタデータ!A:C,2,FALSE)

データが反映されない原因は範囲内に新しいデータが入っていないから

上記のようにあるはずのデータが見つからずN/Aエラーが出る場合は、第２引数で指定している範囲内に新しいデータが含まれていないことが原因です。

対策：第２引数の範囲を列ごと指定

データが追加されても問題なく検索できるようにするには第２引数で指定している範囲を列ごと指定します。上部のアルファベットの箇所をクリックすると列ごと選択できます。

下記は列ごと範囲を指定したことで問題なく検索できている状態です。単価の箇所も同様に数式を入れて列番号のみ「３」に変更しています。

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

別シート検索がうまくいかない場合の対処方法

VLOOKUP関数での検索がうまく行かない場合の対処方法について、「#N/Aエラーが出る場合」、「意図しないデータが返ってくる場合」、「日付データが数値になってしまう場合」の３つのケースごとに解説します。ご自身が当てはまるケースを参照してください。

#N/Aエラーが出る場合の対処

#N/Aエラーは第１引数で指定した検索値が、データ範囲に存在しないときに返すエラーです。
#N/Aエラーを出す原因は下記の通り

#N/Aエラーが出る原因

1. 検索値に全角、半角が正しく入力されていない
2. 余計な空白が入っている
3. 検索値の書式設定が文字列と数値で異なる
4. セル参照が正しくない
5. 検索の型が「TRUE」もしくは「1」が設定されている

詳しくは下記のリンク先で解説しているので当てはまる方は参照してみてください。

意図しないデータが返ってくる場合の対処

意図しないデータが返ってくる場合の対処方法について解説します。

原因１：列番号が間違っている

第３引数で指定する列番号が誤っていると、違う列のデータが返ってきてしまいます。適切な列番号を指定しましょう。

日付データが数値になってしまう場合の対処

原因：書式が日付形式になっていない

日付データをVLOOKUP関数で取得する場合、書式も合わせて日付形式にしておく必要があります。Excelの日付データは内部的にはシリアル値と呼ばれる数値データ（1900年1月1日を「1」とした連番）であるため、日付形式にしておかないと、そのシリアル値が表示されてしまいます。日付形式への書式変更方法はホームタブ内の「数値の書式」から切り替えるか「Ctrl＋1」からセルの書式設定を開いて設定します。

複数条件で別シートのデータを検索したい場合の方法

複数の条件で別シートのデータを検索したい場合は下記のようにINDEX関数とMATCH関数を組み合わせて使う方法がスマートです。VLOOKUP関数でもできますが、列を1列追加する必要があります。

書式

=INDEX(データシート名!データ範囲,MATCH(検索値A&検索値B,データシート名!検索範囲A&データシート名!検索範囲B,0),列番号)

数式例

=INDEX(データ!A2:C13,MATCH(数式!B3&数式!C3,データ!A2:A13&データ!B2:B13,0),3)

作成手順

1. 「=INDEX(」を入力
2. 別シートのデータ範囲を指定する
3. 「,MATCH(」と入力
4. 検索条件１つ目と２つ目を「＆」でつなげて「,」で区切る
5. ２つの検索範囲を「＆」でつなげて「,」で区切る
6. 「0),」と入力する
7. 列番号を入力して「)」で閉じる
8. CTRL + SHIFT + ENTERで数式を決定し「｛｝」で囲む
9. 完成

詳細な設定方法は下記リンクで詳しく解説しています。

VBAで別シートのデータを指定する場合の方法

VBAで別シートを使用する場合は「Worksheets」を使います。例えば「 Worksheets(“Sheet1”).Range(“A1:B10”)」というようにWorksheetsでシート名を指定し、Rangeオブジェクトなどに「.（ドット）」でつなげて使います。具体的な書式とコード例は下記です。

書式

Worksheets(シート名).Range(データ範囲) = WorksheetFunction.VLookup(検索値, Worksheets(シート名).Range(データ範囲), 列番号, False)

コード例

Worksheets("入力").Range("B2") = WorksheetFunction.VLookup("A2", Worksheets("マスタデータ").Range("A2:D51"), 2, False)

下記は実際に別シートを参照するVLOOKUPの記述例です。検索値には「入力シート」の「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関数で別シートを参照する方法についてわかりやすく解説しています。VLOOKUPは慣れない人は混乱しがちなので、動画でしっかり学んでおくと良いと思います。おすすめです。

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

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