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)

手順1:空白セルに「=VLOOKUP(」と入力

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

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

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

手順3:「,」で区切る

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

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

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

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

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

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

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

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

手順7:「,FALSE)」と入力

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

手順8:ENTERキーを押下し、数式を決定

手順9:他の行に数式をコピーする

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

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

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エラーが出る場合は、第2引数で指定している範囲内に新しいデータが含まれていないことが原因です。

追加データは第2引数で指定している範囲内に含まれていないのが原因

対策:第2引数の範囲を列ごと指定

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

上部アルファベット箇所をドラッグして列ごと選択
第2引数設定時

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

列ごと範囲を選択し、問題なく検索できるようになった状態
ゆう

列ごと選択しておけば、データが追加されても問題なくVLOOKUPで検索できます。これによりメンテナンス性がよくなり、範囲を指定しなおす必要もなくなります。管理が楽になりますね。

関連リンク
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エラーが出る場合」、「意図しないデータが返ってくる場合」、「日付データが数値になってしまう場合」の3つのケースごとに解説します。ご自身が当てはまるケースを参照してください。

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

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

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

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

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

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

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

原因1:列番号が間違っている

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

列番号を間違えて設定している例

原因2:第4引数の検索方法がTRUEもしくは省略している

第4引数の検索方法がTRUEに設定されていると、近似値での検索ができるようになります。検索値が数値データであれば完全一致しなくてもマッチしてしまうため、誤ったデータが返ってくる場合があります。近似値での検索を望まない場合は第4引数の検索方法は必ずFALSEに指定しておくのが良いでしょう。省略するとTRUEになってしまうため注意が必要です。

第4引数をTRUEに設定し、誤ったデータを返してしまっている例。得にIDが数値データの場合に起こりやすい。

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

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

日付データを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. 検索条件1つ目と2つ目を「&」でつなげて「,」で区切る
  5. 2つの検索範囲を「&」でつなげて「,」で区切る
  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  'エラーナンバーが0以外の場合
            result.Value = "該当なし"  '結果のセルに「該当なし」と表示
            On Error GoTo 0  'エラー回避を解除
        End If
End Sub

VBAで別シートのデータを指定する場合の方法の詳細については下記リンクで詳しく解説しています。

おすすめの解説動画

VLOOKUP関数で別シートを参照する方法についてわかりやすく解説しています。VLOOKUPは慣れない人は混乱しがちなので、動画でしっかり学んでおくと良いと思います。おすすめです。

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

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

下のスタートボタンを押して練習モードに入ります。
STARTSTOP
※入力モード切り替え ctrl + shift + alt + space
PRE
NEXT
現在の問題をリストから除外
除外したリストをリセット
現在の除外状況のURL取得
コピー
ガイドモード
リピートモード

※推奨ブラウザ Google Chrome

PUSH ENTER
よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!
目次