【Excel】エクセルの別シートのデータを反映させるVLOOKUPの使い方（連動）

Excelで商品一覧や社員名簿、売上表を別々のシートで管理していると、同じ情報を何度も入力する作業が発生します。

このような場面では、VLOOKUP関数を使うことで、別シートにある表から必要なデータを検索し、入力先のシートへ自動で反映できます。

検索する値と参照する表の対応を正しく設定すれば、商品名、単価、担当者、部署などを連動表示できるため、転記ミスの削減にも役立ちます。

この記事では、別シートのデータを反映させるVLOOKUP関数の入力方法から、エラーの原因、実務で使いやすい表の作り方まで解説していきます。

サンプルデータはすべて1行目が見出しである前提です。

 

別シートを連動させるVLOOKUP関数の入力方法

それではまず、別シートのデータをVLOOKUPで反映する基本操作について解説していきます。

入力用シート A列	入力用シート B列	商品マスタ A列	商品マスタ B列
商品コード	商品名	商品コード	商品名
A001	自動表示	A001	ノート
A002	自動表示	A002	ペン

 

検索値と参照表の準備

VLOOKUP関数では、最初に検索の手掛かりとなる値を指定します。

商品コードから商品名を表示する場合は、入力用シートのA2セルに商品コードを入力し、そのコードを検索値として利用します。

参照元となる商品マスタシートでは、A列に商品コード、B列に商品名、C列に単価というように、関連する情報を横方向に並べましょう。

VLOOKUP関数は参照範囲の最も左側の列を検索するため、検索に使う商品コードは必ず表の左端に置くことが重要です。

たとえば商品名がA列、商品コードがB列という並びでは、商品コードを検索して左側の商品名を取得できません。

データベースのように使うマスタシートでは、コード列を先頭にする習慣を付けると、VLOOKUP以外の関数でも扱いやすくなります。

 

別シート参照を含む数式の作成

入力用シートのB2セルを選択し、商品名を表示する数式を入力します。

この数式のA2は、検索したい商品コードが入力されているセルです。

商品マスタ!$A$2:$C$100は、別シートにある検索表の範囲を示しています。

シート名の後ろに半角の感嘆符を付けることで、別シートのセル範囲を指定できます。

3番目の引数である2は、指定した範囲の左から2列目、つまり商品名を返す指定です。

最後のFALSEは完全一致を意味します。

商品コード、社員番号、伝票番号のように完全に一致するデータを探す場合は、FALSEを指定するのが基本です。

商品マスタというシート名に空白や記号が含まれる場合は、シート名を単一引用符で囲みます。

数式を入力してEnterキーを押すと、A2のコードに対応する商品名がB2に表示されます。

 

オートフィルによる複数行への反映

B2セルで正しい結果が表示されたら、セル右下の小さな四角を下方向へドラッグして数式をコピーします。

これがオートフィルです。

コピー後のB3セルでは検索値がA3へ、B4セルではA4へ自動的に変わります。

一方で、商品マスタ!$A$2:$C$100の行番号と列番号にはドル記号が付いているため、コピーしても参照する表の位置は変わりません。

別シートの検索範囲には、列記号と行番号の両方へドル記号を付けた絶対参照を使うと覚えておきましょう。

絶対参照にしない場合、数式を下へコピーするほど参照範囲がずれ、意図しない検索結果やエラーにつながります。

 

VLOOKUP関数の引数と別シート参照の仕組み

続いては、VLOOKUP関数を構成する4つの引数と、別シート参照の考え方を確認していきます。

引数	入力例	役割
検索値	A2	探したい商品コード
範囲	商品マスタ!$A$2:$C$100	別シートの検索表
列番号	2	商品名の列

 

検索値に指定するセル

検索値は、マスタ表から探したいデータです。

入力用シートで商品コードをA2に入力するなら、VLOOKUPの第1引数にはA2を指定します。

検索値は数値でも文字列でも利用できますが、参照元と入力元でデータ形式をそろえる必要があります。

たとえば片方が数値の1001で、もう片方が文字列の1001として保存されていると、見た目が同じでも一致しないことがあります。

コードを扱う場合は、先頭のゼロを残す必要があるかを最初に確認することが大切です。

00123のようなコードは、セルの表示形式だけでゼロを付ける方法と、文字列として入力する方法で結果が異なります。

マスタのコード列と入力用のコード列は、同じ形式に統一しましょう。

 

範囲と列番号の指定

第2引数の範囲には、検索列から返したい列までを含めた表全体を指定します。

商品コードがA列、商品名がB列、単価がC列なら、商品マスタ!$A$2:$C$100を範囲にします。

第3引数の列番号は、シート全体の列番号ではありません。

指定した範囲の左端を1として数えます。

同じ商品コードから単価を反映させたいときは、入力用シートのC2に次の数式を入れます。

検索値と範囲を同じにし、列番号だけを3へ変更する仕組みです。

 

完全一致と近似一致の選択

第4引数にはFALSEまたはTRUEを指定します。

FALSEは完全一致、TRUEは近似一致です。

商品コードや社員番号を検索する一般的な業務表では、FALSEを使います。

TRUEは、点数に応じた評価、数量に応じた割引率、所得に応じた税率のように、段階的な表を検索するときに使われます。

近似一致を使う場合は、検索列を昇順に並べる必要があります。

意図しない結果を避けるため、用途が明確でない限り第4引数を省略せずFALSEと入力する方法が安全です。

 

別シートで商品名と単価を連動させる実例

続いては、商品コードの入力に合わせて商品名と単価を自動表示する実例を確認していきます。

受注入力 A列	受注入力 B列	受注入力 C列	受注入力 D列
商品コード	商品名	単価	数量
A001	ノート	180	3
A002	ペン	120	5

 

商品マスタシートの作成

まず商品マスタという名前のシートを用意します。

A1に商品コード、B1に商品名、C1に単価を入力し、2行目から商品データを登録します。

VLOOKUPで利用する表は、空白行を必要以上に入れず、1件につき1行で管理すると見やすくなります。

商品コードが重複している場合、VLOOKUPは上から見つけた最初のデータを返します。

商品マスタの検索キーは重複しないように管理することで、どの商品情報が反映されるか分からなくなる問題を防げます。

単価を変更した場合も、マスタシートのC列だけを修正すれば、VLOOKUPで参照している入力用シートへ反映されます。

 

受注入力シートへの数式入力

受注入力シートのB2に商品名を表示する数式を入力します。

検索値の$A2は、数式を右方向へコピーした場合でも商品コード列Aを参照し続ける指定です。

次にC2へ単価を表示する数式を入力します。

B2とC2では返す列だけが異なるため、2つの数式を並べて確認すると仕組みを理解しやすくなります。

商品コードをA2へ入力すると、B2には商品名、C2には単価が連動して表示されます。

売上金額をD列以降で計算する場合は、単価と数量を掛け算する数式を追加できます。

こうしておけば、商品名や単価を毎回手入力する必要がありません。

 

数式コピーと空白時の表示

数式を下の行までコピーすると、各行の商品コードに応じた情報が反映されます。

ただし、商品コードが未入力の行では、VLOOKUPの結果としてエラーが表示されることがあります。

入力前の行をすっきり見せたい場合は、IFERROR関数やIF関数を組み合わせます。

この数式では、A2が空白なら空白を表示し、A2にコードが入力されたときだけVLOOKUPを実行します。

入力途中の帳票では空白チェックを加えると、不要なエラー表示を減らせます。

 

VLOOKUPエラーの原因と修正方法

続いては、VLOOKUPで別シートのデータが反映されないときに確認したいエラーの原因を解説していきます。

表示	主な原因	確認内容
#N/A	検索値が見つからない	コードと形式
#REF!	列番号が範囲外	範囲と列番号
#VALUE!	引数の指定ミス	区切り記号とセル参照

 

#N/Aが表示されるケース

#N/Aは、検索値に一致するデータが参照表に見つからないときに表示されます。

まず、入力用シートのコードと商品マスタのコードを見比べて、文字の違いがないか確認しましょう。

半角と全角の違い、余分な空白、先頭ゼロの有無は見落としやすい原因です。

また、検索範囲の先頭列が商品コード列になっているかも重要です。

VLOOKUPは表の左端の列だけを検索するため、検索したいコード列が範囲の途中にあると一致していても検索できません。

マスタへ新しい商品を追加したのに反映されない場合は、数式の範囲が新しい行まで含まれているかを確認してください。

 

参照範囲と列番号の確認

#REF!エラーは、指定した列番号が参照範囲の列数より大きいときに発生します。

たとえば商品マスタ!$A$2:$B$100は2列しかないため、列番号3を指定するとエラーになります。

単価を返したいなら、C列まで含む商品マスタ!$A$2:$C$100を指定する必要があります。

列を途中で挿入した場合も、数式が意図した列を返しているか確認しましょう。

商品名と単価の列順が変わったのに列番号を変更していないと、エラーではなく別の項目が表示される場合があります。

これは気付きにくいため、表の構成を変えた後は代表的なコードで結果を確認する習慣が必要です。

 

データ形式と文字列の違い

数式も範囲も正しいのに#N/Aが出る場合は、データ形式を確認します。

セルの左上に緑色の三角が表示されている場合、数値が文字列として保存されている可能性があります。

数値と文字列をそろえるには、対象のセルを選択して表示形式を確認し、必要に応じて数値へ変換します。

余分なスペースが疑われる場合は、TRIM関数で前後の空白を取り除く方法もあります。

外部システムから貼り付けたデータでは、目に見えないスペースや文字種の違いが含まれることがあります。

エラーを隠す前に、検索値とマスタのデータが本当に同じ内容かを確認することが根本的な解決につながります。

 

VLOOKUPを使いやすくする表作成と応用

続いては、VLOOKUPを長く使い続けるための表の整え方と、実務で便利な応用方法を確認していきます。

管理方法	メリット	注意点
マスタを別シート化	修正箇所を集約できる	コードの重複を防ぐ
Excelテーブル化	行追加に対応しやすい	見出しを固定する
IFERRORの使用	エラー表示を整えられる	原因確認も行う

 

マスタデータの更新ルール

別シート参照を安定して使うには、マスタシートを正しい情報の保管場所として扱うことが重要です。

商品名や単価の変更があったとき、入力用シートを個別に修正するのではなく、商品マスタを更新します。

VLOOKUPの結果は再計算によって反映されるため、複数の帳票をまとめて更新できます。

マスタは誰がいつ更新するのかを決めておくと、同じコードに異なる情報が登録される問題を防げます。

重要なマスタを編集できる人を限定することも、業務ファイルでは有効です。

 

IFERROR関数との組み合わせ

検索値が存在しない場合に#N/Aを表示したくないときは、IFERROR関数を組み合わせます。

この式では、VLOOKUPが正常に商品名を取得できた場合は商品名を表示し、エラーが出た場合は未登録と表示します。

空白を表示したい場合は、最後の未登録を空の文字列へ変更します。

ただし、すべてのエラーを空白にすると、商品コードの誤入力に気付きにくくなる場合があります。

入力担当者へ確認してほしい表では、未登録のように分かりやすい表示を残す方法が向いています。

 

XLOOKUP関数との使い分け

Microsoft 365などの新しいExcelでは、XLOOKUP関数も利用できます。

XLOOKUPは検索列と返す列を別々に指定できるため、VLOOKUPでは苦手な左方向の検索にも対応できます。

一方で、古いExcelとの互換性が必要なファイルでは、VLOOKUPのほうが使いやすいことがあります。

複数の環境で共有する帳票では、利用者のExcelバージョンを考慮して関数を選ぶ必要があります。

基本的なマスタ参照を理解する目的では、VLOOKUPは今でも十分に役立つ関数です。

 

まとめ エクセルの別シートのデータを反映させるVLOOKUPの使い方

エクセルで別シートのデータを反映させるには、VLOOKUP関数で検索値、参照範囲、列番号、完全一致の指定を正しく設定します。

基本となる数式は、=VLOOKUP(A2,商品マスタ!$A$2:$C$100,2,FALSE)の形です。

検索値には入力用シートの商品コードを指定し、別シートのマスタ表は検索列が左端になるように準備しましょう。

数式をコピーする際は、別シートの参照範囲を絶対参照にすることで、行ごとに検索表がずれるトラブルを防げます。

#N/Aが表示された場合は、コードの入力内容、余分な空白、数値と文字列の違い、参照範囲を順番に確認します。

商品名や単価をマスタシートで一元管理し、入力用シートへ自動反映する仕組みを作れば、日々の入力作業を効率化できるでしょう。