【Excel】エクセルのVLOOKUP関数の使い方（基本構文・別シート参照・検索・抽出）

いつも記事を読んでいただきありがとうございます！！！ これからもお役に立てる各情報を発信していきますので、今後ともよろしくお願いします(^^)/

Excelで商品名や社員名を入力し、対応する単価・所属・住所などを自動表示したいときに役立つのがVLOOKUP関数です。

表を見ながら手入力すると、転記ミスや更新漏れが起こりやすくなりますが、VLOOKUP関数なら検索値をもとに一覧表から必要なデータを抽出できます。

特に、別シートに用意したマスターデータを参照する使い方を覚えると、見積書、在庫表、顧客管理表などを効率よく作成できるでしょう。

ただし、検索範囲の先頭列や列番号、完全一致と近似一致の違いでつまずくことも少なくありません。

この記事では、VLOOKUP関数の基本構文から別シート参照、検索結果を抽出するときの注意点までを、サンプルデータと数式を交えて解説します。

まずは、最も基本的な使い方から確認していきましょう。

 

エクセルでVLOOKUP関数を入力する基本手順

それではまず、VLOOKUP関数を使って商品コードから商品名と単価を表示する基本手順について解説していきます。

商品コード	商品名	単価
A101	ノート	180
A102	ボールペン	120
A103	付箋	250

 

VLOOKUP関数の基本構文

VLOOKUP関数の構文は、=VLOOKUP(検索値,範囲,列番号,検索方法)です。

検索値には探したい商品コードや社員番号が入力されたセルを指定します。

範囲には、検索対象となる一覧表全体を指定します。

列番号には、指定範囲の左端列から数えて何列目の値を返すかを入力します。

検索方法は、コードや番号を正確に一致させたい場合にFALSEを指定するのが基本です。

VLOOKUP関数は、範囲の最も左にある列から検索を始める関数だと理解すると、表の作り方を判断しやすくなります。

たとえばA列が商品コード、B列が商品名、C列が単価なら、商品名を取り出す列番号は2、単価を取り出す列番号は3です。

数式を入力する前に、検索するキーとなる列が表の左端にあるか確認しましょう。

 

商品名を検索して表示する数式

ここでは、F2セルに商品コードを入力し、G2セルに商品名を表示するケースを考えます。

元データがA2からC4にあり、1行目は見出しになっているものとします。

F2が検索値、$A$2:$C$4が検索範囲、2が商品名の列番号、FALSEが完全一致の指定です。

範囲に付けた$記号は絶対参照であり、数式を下方向へコピーしても検索範囲がずれないようにする役割があります。

検索範囲はオートフィル前に絶対参照へ固定すると、複数行の明細でも安定して使えます。

F2へA102と入力すると、G2にはボールペンと表示されます。

数字ではなく文字列のコードを扱う場合も、検索値とマスター側の表示形式をそろえることが大切です。

 

単価を抽出してオートフィルする操作

単価を返したい場合は、商品名を取得した数式の列番号だけを3へ変更します。

H2セルに数式を入力したら、セル右下の小さな四角であるフィルハンドルを下へドラッグします。

これにより、F3、F4などの各商品コードに対応した単価を連続して抽出できます。

先頭セルに正しい数式を入力してからオートフィルする流れが、入力作業を短縮する基本です。

検索範囲を固定していないと、下へコピーしたときに参照範囲も移動してしまいます。

数式バーで$A$2:$C$4のように表示されているか、コピー後に確認すると安心です。

 

VLOOKUP関数で別シートを参照する方法

続いては、商品マスターを別シートに保存し、入力用シートからVLOOKUP関数で参照する方法を確認していきます。

入力シートのセル	内容	マスターシートの参照列
B2	A101	A列 商品コード
C2	検索結果	B列 商品名

 

別シート名を含めた範囲指定

別シートを参照する場合は、検索範囲の前にシート名と!を付けます。

この数式では、現在のシートにあるB2の値を検索し、商品マスターシートのA2からC100を対象に商品名を返します。

シート名にスペースが含まれる場合は、’商品 マスター’!$A$2:$C$100のようにシングルクォーテーションで囲みます。

別シート参照では、シート名の後ろに必ず!を付けることが書式上の重要な決まりです。

手入力でシート名を打つより、数式入力中に参照先シートをクリックして範囲をドラッグすると入力ミスを減らせます。

参照元と参照先のシートを分けると、入力欄とマスターデータを整理しやすくなります。

 

商品マスターを固定して明細へコピーする方法

見積書や注文書では、明細行ごとに商品コードを入力し、商品名と単価を取得する場面が多くあります。

たとえば入力シートのB列に商品コード、C列に商品名、D列に単価を表示する構成にします。

検索値のB2は行番号が変わる必要があるため固定しません。

一方、商品マスターの範囲はコピー後も変わらないため、行と列の両方に$を付けます。

検索値は相対参照、マスター範囲は絶対参照という考え方を使い分けると、数式を読みやすく保てます。

数式を入力した後は、C2とD2を必要な明細行までコピーしましょう。

 

外部ブックを参照するときの注意点

VLOOKUP関数では、別のExcelファイルにあるマスター表を参照することもできます。

外部ブックを参照する数式には、ファイル名、シート名、セル範囲の情報が自動的に含まれます。

ただし、参照元のファイルを移動したり名前を変更したりすると、リンク切れになることがあります。

外部参照は共有フォルダの構成変更に影響を受けやすいため、運用前に保存場所のルールを決めることが重要です。

複数の利用者でファイルを扱う場合は、マスターを同一ブックの別シートに置く方法が管理しやすいケースもあります。

ファイルを開いたときにリンク更新の確認画面が出た場合は、参照先が正しいか確認してから更新を実行しましょう。

 

VLOOKUP関数で完全一致検索を行う設定

続いては、商品コードや顧客番号を正確に探すための完全一致検索について確認していきます。

検索方法	第4引数	主な用途
完全一致	FALSE	商品コード、社員番号
近似一致	TRUE	点数区分、税率表

 

FALSEを指定する完全一致の考え方

商品コード、伝票番号、会員番号のように、同じ値を正確に探したいときは第4引数へFALSEを指定します。

FALSEを指定すると、検索値と完全に同じ文字列または数値がある場合だけ結果を返します。

たとえばB2がA105で、A列にA105が存在すれば対応する3列目の単価が返ります。

見つからない場合は#N/Aとなるため、存在しないコードを見逃しにくい点も利点です。

業務用のコード検索ではFALSEを省略せず明記すると、数式の意図が伝わりやすくなります。

第4引数を省略するとTRUEとして扱われるため、意図しない近似一致になる可能性があります。

 

入力形式の違いで検索できない原因

VLOOKUP関数で#N/Aが出るときは、表に値がないとは限りません。

検索値が文字列で、マスター側が数値になっていると、見た目が同じでも一致しない場合があります。

先頭に0がある社員番号や郵便番号は、文字列として扱うことが多いデータです。

00123と123は、Excelでは別の値として扱われることがあるため注意が必要です。

セルの表示形式を文字列に統一する、先頭のアポストロフィを確認する、余分な空白を削除するなどの対処を行います。

コピーしたデータに不要なスペースが含まれる場合は、TRIM関数で空白を整理してから検索する方法もあります。

 

近似一致TRUEを使う場面

TRUEは、検索値以下で最も近い値を探す近似一致の指定です。

たとえば点数に応じて評価を返す表、売上金額に応じて割引率を返す表では近似一致が使われます。

近似一致を使う検索表は、検索対象の先頭列を小さい順に並べ替えておく必要があります。

この場合、H列に0、60、70、80、90のような基準点を昇順で配置します。

TRUEを使う表は並び順が結果に直結するため、商品コード検索とは性質が異なります。

日常的なマスター検索ではFALSE、段階的な判定表ではTRUEという使い分けを意識しましょう。

 

VLOOKUP関数で検索結果を抽出する応用

続いては、VLOOKUP関数を利用して請求書や在庫表で必要な情報を抽出する応用について確認していきます。

検索値	抽出できる項目	活用例
商品コード	商品名、単価、在庫数	見積書、注文書
顧客コード	会社名、住所、担当者	請求書、顧客台帳

 

IFERROR関数で#N/Aを非表示にする方法

入力前の空白セルや未登録コードで#N/Aを表示したくない場合は、IFERROR関数でVLOOKUP関数を囲みます。

この数式では、VLOOKUP関数がエラーになったときに空白文字を返します。

見積書のように入力途中のセルが多いシートでは、エラー表示を減らして見やすくできるでしょう。

IFERROR関数はエラーを隠すだけでなく、利用者に見せる表示を整える役割もあります。

ただし、マスターにないコードの入力ミスまで気付きにくくなる可能性があります。

入力ミスを確認したい場合は、空白ではなく「未登録」と表示する設定も有効です。

 

複数列の情報を取り出す列番号の設定

同じ検索値から商品名、単価、在庫数など複数の項目を取り出す場合、列番号だけを変えて数式を作成します。

検索範囲が商品コード、商品名、単価、在庫数の4列なら、それぞれの列番号は2、3、4です。

検索範囲は、取り出したい最も右の列まで含めて指定します。

列番号が範囲内の列数を超えると#REF!エラーになるため、表に列を追加・削除した際は数式も見直します。

項目数が多い表では、列番号の意味を見失わないよう、見出しと数式を対応させて確認する習慣が役立ちます。

 

請求書や見積書での実務的な使い方

VLOOKUP関数は、明細表に商品コードを入力するだけで商品名と単価を表示する仕組みに向いています。

さらに数量を入力し、単価と掛け合わせれば金額を自動計算できます。

ここでD2がVLOOKUP関数で取得した単価、E2が数量です。

マスターを一元管理し、入力シートではコードだけを扱う構成にすると、価格改定時の修正箇所を減らせます。

商品名を直接入力する運用では表記ゆれが起こりやすいため、コード入力とデータ検証のリストを組み合わせる方法も便利です。

VLOOKUP関数の結果をもとに金額や税額を計算すれば、日々の帳票作成を効率化できます。

 

VLOOKUP関数で発生しやすいエラーと対処

続いては、VLOOKUP関数で表示されやすいエラーの原因と確認方法について解説していきます。

エラー	主な原因	確認箇所
#N/A	検索値が見つからない	コード、空白、表示形式
#REF!	列番号が範囲外	範囲と列番号

 

#N/Aエラーを確認する順番

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

まず、検索値のセルをコピーし、マスター表の先頭列で検索して一致するデータがあるか確認します。

次に、余分なスペース、全角半角、先頭の0、文字列と数値の違いを確認しましょう。

見た目が同じコードでも内部のデータ形式が異なれば一致しません。

完全一致で検索する業務表では、コードの入力規則を作ることも再発防止につながります。

 

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

#REF!は、列番号が検索範囲の列数より大きいときによく発生します。

たとえばA列からC列までの3列を範囲に指定しているのに、列番号へ4を指定するとエラーです。

検索範囲をA2:D100へ広げるか、正しい列番号へ修正します。

表の途中に列を挿入した場合も、想定と異なる列を返していないか確認が必要です。

 

検索列が左端にない表への対応

VLOOKUP関数は右方向にしか値を返せないため、検索したい列が取得列より右にある表では使えません。

たとえばC列の商品コードからA列の商品名を返す、という左方向の検索はVLOOKUP関数の対象外です。

この場合は、表の列順を見直すか、XLOOKUP関数やINDEX関数とMATCH関数の組み合わせを検討します。

VLOOKUP関数の制約は左端列で検索し右側の列を取得することです。

既存の表を無理に変更できない場合は、別の検索関数を使う判断も必要になります。

 

まとめ エクセルのVLOOKUP関数の使い方（検索・抽出・別シート参照）

VLOOKUP関数は、商品コードや顧客コードを検索値として、マスター表から必要な情報を抽出するための代表的なExcel関数です。

基本構文は、=VLOOKUP(検索値,範囲,列番号,検索方法)であり、通常のコード検索では最後の引数にFALSEを指定します。

検索範囲の左端列に検索値が存在すること、取得したい項目に対応する列番号を指定することが基本になります。

別シートを参照するときは、商品マスター!$A$2:$C$100のようにシート名と絶対参照を組み合わせましょう。

検索値は相対参照、マスター範囲は絶対参照にする考え方を覚えると、オートフィルを使った明細作成がスムーズになります。

#N/Aが出た場合は、検索値の入力、データ形式、空白、マスターへの登録状況を順番に確認します。

VLOOKUP関数を正しく使い、転記作業を減らしながら、正確で更新しやすいExcelシートを作っていきましょう。