【Excel】エクセルのVLOOKUP関数の使い方（別表からデータを検索・表示）

エクセルで商品コードや社員番号を入力したとき、別表に登録した商品名、単価、部署名などを自動表示したい場面は少なくありません。

このような検索と表示に役立つ代表的な関数がVLOOKUP関数です。

入力のたびに表を目視で探して転記する作業を減らせるため、見積書、在庫管理表、名簿、売上集計など幅広い業務で活用できます。

ただし、参照範囲の固定、列番号、完全一致と近似一致の違いでつまずくこともあります。

最初に正しい基本式を理解すると、別シートや別表を使った検索も安定して行えます。

この記事では、VLOOKUP関数で別表からデータを検索して表示する方法を、サンプル表と数式を使って詳しく解説します。

 

VLOOKUP関数で別表からデータを表示する方法

それではまず、商品コードを入力して別表から商品名と単価を表示する基本操作について解説していきます。

入力表	A列	B列	C列
1行目	商品コード	商品名	単価
2行目	P002	別表から表示	別表から表示

 

検索用の表と別表の準備

VLOOKUP関数を使う前に、検索するための入力表と、検索対象となる商品マスタ表を用意します。

ここでは、入力用シートのA列に商品コードを入力し、B列に商品名、C列に単価を表示する構成にします。

別表は同じシート内でも別シート内でもかまいませんが、初めて使う場合は別シートに商品マスタを作ると役割がわかりやすくなります。

商品マスタの1行目には見出しを置き、A列に商品コード、B列に商品名、C列に単価を入力します。

VLOOKUP関数は、指定した範囲のいちばん左の列で値を探す仕組みです。

したがって、検索キーである商品コードは、商品マスタの左端列に配置する必要があります。

たとえば商品名を左、商品コードを右に置いた表では、通常のVLOOKUP関数で商品コードを基準に左側の商品名を返すことはできません。

なお、見出し行を含めるかどうかはどちらでも計算できますが、範囲を説明しやすくするため、数式ではデータ開始行のA2から指定する方法がおすすめです。

入力用の表と商品マスタのコード表記をそろえることも重要です。

片方が文字列のP002で、もう片方が数値の2になっていると、見た目が近くても検索できません。

 

商品名を表示する基本数式

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

この数式では、最初のA2が検索値です。

つまり、入力用シートのA2に入力された商品コードを検索します。

商品マスタ!$A$2:$C$4は検索範囲で、商品マスタシートのA2からC4までを対象にする指定です。

3番目の2は、指定範囲の左端から数えて2列目の値を返すという意味になります。

商品コードが1列目、商品名が2列目なので、商品名を返すには2を指定します。

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

商品コード、社員番号、伝票番号のように一致すべき値を検索するときは、原則としてFALSEを指定します。

この式を確定すると、A2がP002の場合、商品マスタのP002を探し、B列のノートをB2に表示します。

数式を入力する際、シート名に空白がある場合は、シート名をシングルクォーテーションで囲みます。

たとえば商品 マスタというシート名なら、’商品 マスタ’!$A$2:$C$4のように指定しましょう。

 

単価を表示してオートフィルする操作

次に、入力用シートのC2セルに単価を表示する数式を入れます。

商品名の数式との違いは、3番目の引数だけです。

単価は検索範囲内の3列目にあるため、列番号を3に変更します。

検索範囲全体の列番号ではなく、指定した検索範囲の左端を1として数える点が重要です。

数式を下方向へコピーする場合は、B2とC2を選び、セル右下の小さな四角であるフィルハンドルを下へドラッグします。

対象行が多い場合は、フィルハンドルをダブルクリックすると、隣接するデータ列に合わせて数式をコピーできることがあります。

このとき検索値のA2はA3、A4へ自動的に変化します。

一方、商品マスタの範囲は変化させたくないため、$A$2:$C$4のように絶対参照にします。

絶対参照を付けずにコピーすると、検索範囲まで下にずれてしまい、途中の行から正しい結果が表示されない原因になります。

 

VLOOKUP関数の引数と完全一致の指定

続いては、VLOOKUP関数の4つの引数と、検索結果を正確にする完全一致の指定を確認していきます。

引数	入力例	役割
検索値	A2	探したい商品コード
範囲	商品マスタ!$A$2:$C$4	検索する別表
列番号	2	返す列の位置

 

検索値と検索範囲の考え方

VLOOKUP関数の第1引数は検索値です。

検索値には、セル参照、文字列、数値を指定できますが、日常的な表ではA2のように入力セルを指定することが多いでしょう。

検索範囲は第2引数で指定し、検索する列から返したい列までを一つの長方形として選びます。

商品名と単価を返したい場合は、商品コード列だけではなく、商品名列と単価列も含めてA列からC列までを指定します。

VLOOKUP関数は、範囲の外にある列の値を返せません。

たとえば範囲を$A$2:$B$4にしたまま列番号3を指定すると、参照範囲に3列目がないためエラーになります。

また、範囲の左端列に同じ商品コードが複数ある場合、VLOOKUP関数は上から最初に見つけたデータを返します。

重複したマスタは意図しない検索結果につながるため、商品コードなどの検索キーは原則として一意に管理しましょう。

テーブル機能を使っている場合でも、通常のセル範囲を指定してVLOOKUP関数を作成できます。

ただし、後から商品を追加する予定があるなら、範囲を広めに取るか、テーブル参照やXLOOKUP関数も検討すると管理しやすくなります。

 

列番号の数え方

第3引数の列番号は、検索範囲の中で何列目を表示するかを指定する数値です。

商品マスタ!$A$2:$C$4を範囲にした場合、A列は1、B列は2、C列は3となります。

商品名なら2、単価なら3を入力します。

ここでよくある誤りは、ワークシート全体の列番号で考えてしまうことです。

たとえば検索範囲をD列からF列にした場合でも、D列は範囲内では1列目、E列は2列目、F列は3列目です。

列の追加や並べ替えを行うと、数式内の列番号が意図とずれることがあります。

商品マスタの構成を変更した後は、VLOOKUP関数が返している列も必ず確認しましょう。

固定の帳票で使う場合は問題になりにくい一方、頻繁に列を増減する管理表では注意が必要です。

 

FALSEとTRUEによる検索方法

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

FALSEは完全一致で、検索値と同じ商品コードや社員番号だけを探します。

完全に一致する値がなければ、結果は#N/Aエラーになります。

TRUEは近似一致で、検索値以下の最大値を返す検索方法です。

たとえば点数に応じて評価を返す表、所得金額に応じて税率を返す表などで使われます。

ただしTRUEを使う場合、検索範囲の左端列は昇順に並んでいる必要があります。

コード検索では、意図しない近い値を返さないためにFALSEを明示するのが安全です。

第4引数を省略するとTRUEとして扱われるため、商品コード検索で引数を省略する方法はおすすめできません。

数式を見直しやすくするためにも、完全一致ならFALSEまで必ず書く習慣を付けましょう。

 

別シートと別ブックを参照する数式

続いては、商品マスタを別シートや別ファイルに保存している場合の参照方法を確認していきます。

シート	A列	B列	C列
商品マスタ	商品コード	商品名	単価
データ	P003	検索結果	検索結果

 

別シートを指定する数式

同じブック内に商品マスタというシートがある場合は、シート名の後ろに半角の感嘆符を付けて範囲を指定します。

商品マスタ!の部分が、別シートを参照している記述です。

商品マスタシートのA2からC100までを検索し、その2列目の商品名を返します。

参照範囲は、実際のデータ行より少し大きめに設定しておくと、新しい商品を追加したときに数式を修正する手間を減らせます。

ただし、必要以上にシート全体を検索範囲にすると、ファイルが重くなる場合があります。

検索範囲は、将来の追加分を考慮しつつ、必要な大きさに絞ることが基本です。

シート名を変更すると、通常はエクセルが数式内の参照も自動更新します。

一方、シートを削除すると参照が壊れるため、マスタ用シートを整理する際は注意しましょう。

 

別ブックを指定する数式

商品マスタを別のエクセルファイルで管理している場合も、VLOOKUP関数で参照できます。

たとえば商品マスタ.xlsxというブックの商品一覧シートを参照する数式は、次のような形になります。

別ブックを開いた状態で数式を作成すると、参照先のブック名、シート名、セル範囲をエクセルが自動で入力してくれます。

手入力よりも間違いを防ぎやすいため、数式バーでVLOOKUP関数を入力したあと、参照先ファイルをクリックして範囲選択する方法が便利です。

参照先ブックを閉じると、保存場所を含む長いパスが数式に表示されることがあります。

これは外部参照と呼ばれる状態です。

外部参照では、元ファイルの名前変更、移動、削除によってリンク切れが起こる可能性があります。

複数人で共有するファイルでは、参照先を共通フォルダに固定し、勝手にフォルダ構成を変えない運用が大切です。

 

参照範囲を絶対参照で固定する理由

VLOOKUP関数の検索範囲には、$記号を付けて絶対参照を設定します。

たとえば商品マスタ!$A$2:$C$100のように指定すると、数式を下の行へコピーしても、検索範囲は変わりません。

検索値のA2だけは相対参照にしておくため、次の行ではA3、その次の行ではA4へ変化します。

この組み合わせにより、各行の商品コードを同じ商品マスタから検索できます。

検索値は動かし、マスタ範囲は固定することが、オートフィルでVLOOKUP関数を使う基本です。

数式入力中に範囲を選択してF4キーを押すと、絶対参照、行固定、列固定、相対参照を順に切り替えられます。

Windows環境では、キーボードのFnキーとの組み合わせが必要な機種もあります。

もしF4キーで切り替わらない場合は、数式内に$を手入力しても問題ありません。

 

VLOOKUP関数のエラーと修正方法

続いては、検索結果が表示されないときに確認したい#N/Aエラー、参照エラー、文字列と数値の違いについて確認していきます。

エラー表示	主な原因	確認項目
#N/A	検索値が見つからない	コードとデータ形式
#REF!	列番号が範囲外	範囲と列番号
#VALUE!	引数の指定不備	数式の構文

 

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

#N/Aは、指定した検索値が検索範囲の左端列に見つからないときに表示されます。

まず、入力セルの商品コードと、商品マスタの商品コードが本当に同じかを確認しましょう。

余分な空白、全角と半角の違い、ハイフンの種類の違いがあると、見た目が似ていても完全一致にはなりません。

特に、外部システムからコピーしたデータには、末尾スペースや見えにくい文字が含まれることがあります。

#N/Aは関数が壊れた表示ではなく、該当する検索データが見つからないという通知です。

検索値が文字列として入力されているのに、別表では数値として保存されているケースも代表的な原因です。

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

表示形式を変更するだけでは値の種類がそろわない場合があるため、数値への変換、文字列への変換を行います。

エラーを空白に見せたい場合は、IFERROR関数と組み合わせることもできます。

ただし、エラーを非表示にすると、マスタ未登録のコードにも気付きにくくなります。

入力漏れを確認したい表では、#N/Aをそのまま表示する運用も有効です。

 

#REF!エラーと列番号の見直し

#REF!エラーは、数式が参照できないセルや列を指定しているときに起こります。

VLOOKUP関数では、検索範囲より大きい列番号を指定した場合に発生しやすいエラーです。

たとえば検索範囲が$A$2:$C$100であるのに、列番号に4を指定すると、4列目が存在しないため#REF!になります。

列番号は、検索範囲に含まれる列数以下でなければなりません。

数式をコピーしたあとに検索範囲がずれている場合も、参照エラーや誤った結果につながります。

数式バーをクリックして、検索範囲に$が付いているかを確認しましょう。

また、商品マスタの列を削除した場合、既存のVLOOKUP関数が参照先を失うことがあります。

列の削除や挿入を行った後は、検索結果を数件確認して、商品名と単価が正しく対応しているかを見直すことが大切です。

特に単価の列がずれると、請求書や見積書に誤った金額が反映されるおそれがあります。

 

文字列と数値の不一致への対処

商品コードが001、002のように先頭のゼロを含む場合は、文字列として扱うことが一般的です。

一方で、片方の表が数値の1、2として保存され、もう片方が文字列の001、002として保存されていると、VLOOKUP関数は一致と判断しません。

セルの表示形式を文字列に統一し、入力時にも先頭ゼロを維持する方法が基本です。

既存データを数値に変換するならVALUE関数、文字列化するならTEXT関数を使う方法があります。

たとえば3桁の商品コードとしてそろえる場合は、TEXT関数で000の形式を指定します。

ただし、変換用の列を追加して処理する場合は、どの列をVLOOKUP関数の検索値にするかを明確にします。

目視だけでは判別しにくい問題だからこそ、テスト用に1件をコピーして一致するかを確認すると安心です。

 

検索表を管理しやすくする工夫

続いては、VLOOKUP関数を多くの行で使うときに、検索表を更新しやすくする工夫を確認していきます。

商品コード	商品名	単価	分類
P004	付箋	160	文具
P005	ホチキス	480	文具

 

テーブル機能による範囲の自動拡張

商品マスタに商品を追加する機会が多い場合は、表をテーブルとして設定すると便利です。

商品マスタの範囲を選択し、挿入タブからテーブルを選ぶと、見出し付きのテーブルとして管理できます。

テーブルの末尾に新しい商品を入力すると、テーブル範囲は自動的に拡張されます。

通常のVLOOKUP関数で固定範囲を指定している場合は、追加行が範囲外にならないよう範囲を修正する必要があります。

マスタの更新頻度が高い表では、範囲漏れを防ぐ仕組みを用意することが重要です。

テーブル名を利用した数式は少し長く見えますが、どの表を参照しているか判断しやすい利点があります。

共有ファイルでは、商品マスタを編集する担当者と、検索結果を利用する担当者を分ける運用も考えられます。

その場合でも、商品コードの重複確認と入力規則はマスタ側で徹底しましょう。

 

入力規則による商品コードの選択

VLOOKUP関数の検索値を手入力すると、タイプミスで#N/Aが発生することがあります。

入力規則のリストを使えば、登録済みの商品コードから選択でき、入力ミスを減らせます。

入力用シートのA2から必要な範囲を選択し、データタブのデータの入力規則を開きます。

入力値の種類でリストを選び、元の値に商品コード一覧の範囲を指定します。

設定後は、セル右側の矢印から商品コードを選べるようになります。

検索値の表記を統一できれば、VLOOKUP関数のエラーも大きく減らせます。

商品名を選ばせるよりも、重複しない商品コードを選ばせるほうが、マスタとの照合は安定します。

商品コードが利用者にわかりにくい場合は、商品名を表示する補助列を用意するなど、入力しやすさとのバランスを考えましょう。

 

検索式をコピーする前の確認

数式を数百行へコピーする前に、1行目の結果を確認することが重要です。

商品コード、商品名、単価を1件ずつ商品マスタと照合し、列番号や検索範囲が正しいかを確認します。

確認後にオートフィルを使えば、大量データでも短時間で検索結果を作成できます。

数式をコピーしたセルには、数式そのものが入っています。

結果を固定したい帳票では、コピー後に値として貼り付ける作業が必要になることもあります。

ただし、値に貼り付けると、商品マスタを更新しても表示結果は自動更新されません。

更新連動を残したいなら数式のままにし、提出用に確定したいなら値へ変換するという使い分けが適切です。

 

まとめ エクセルのVLOOKUP関数の使い方（別表からデータを検索・表示）

エクセルのVLOOKUP関数を使うと、入力した商品コードや社員番号をもとに、別表から必要な情報を検索して表示できます。

基本式では、検索値、別表の範囲、返す列番号、検索方法の4つを指定します。

商品コードのような識別番号を探す場合は、末尾にFALSEを指定して完全一致にすることが基本です。

検索対象のコードは別表の左端列に置き、検索範囲は$記号で固定してからオートフィルを使いましょう。

#N/Aが表示されたときは、コードの未登録、余分な空白、文字列と数値の不一致を確認します。

#REF!が表示されたときは、検索範囲と列番号の関係を見直すことが有効です。

別シートや別ブックのマスタも参照できますが、外部参照ではファイル名や保存場所の変更に注意が必要です。

商品マスタをテーブル化し、入力規則も組み合わせると、更新が多い業務表でも使いやすくなります。

まずは小さなサンプル表でVLOOKUP関数を入力し、商品名と単価が正しく表示されることを確認してから実務用の表へ広げていきましょう。