VLOOKUP関数の使い方

VLOOKUP関数

VLOOKUP関数の使い方

使用関数:VLOOKUP

別表のデータから検索してその値を自動入力できるVLOOKUP関数(ヴイルックアップ)は様々な場面で使われます。商品コードから価格と商品名を、氏名から出身地と年齢を、購入金額から配達料を自動で転記してくれるのです。

スポンサーリンク

VLOOKUP関数に使う表は縦並べで作成

VLOOKUP関数に必要なのは、数式が入るセルと検索に使う表です。
VLOOKUP関数の数式が入るセルと検索に使う表

VLOOKUPは縦方向に検索する関数であるため、表のデータが縦並べに作成されている必要があります。「VLOOKUP」はVertical Look Up(縦の検索)の略ですね。
検索に使う表はデータを縦並べに作成

VLOOKUPに適した表の作成プロセスは以下の記事を参照して下さい。

表の作り方を実践|初心者が踏み出すExcelの第一歩

横方向に検索するなら「HLOOKUP関数」(Horizontal Look Up)を使います。操作はVLOOKUPと同じ。「V」を「H」に変えるだけです。

横方向に並ぶ列項目も検索して交差位置の値を取り出すために、VLOOKUP関数をMATCH関数で補完します。この方法の解説記事です

VLOOKUP関数とMATCH関数で行・列項目の交差位置を取り出す

数式が入るセルと検索に使う表を同じシート上に置いてもいいですが、実務で作業する時は別シートか別ブックに置いて管理することをお勧めします。
ブックとシートについて
検索に使う表を別シートで管理
また、データ表はテーブル化しておくと便利です。VLOOKUP関数がうまく機能しない、予期しないエラーが出るという問題を回避するのにも役立ちます。

数式は間違っていない、検索範囲にはちゃんと検索値が然るべき場所に入ってる・・・でも、VLOOKUP関数はエラー#N/Aを返してくる! 一体、なぜ?!という時に確認すべき事項と対処法をまとめました。

VLOOKUP関数でエラーが出た時の確認事項と対処法

VLOOKUP関数の構成

では、実際にVLOOKUPで数式を組み立てて、特定のデータを検索してみましょう。

例として、商品の「価格表」から「商品名」を検索して「請求明細」のセルに自動入力させてみます。
「請求明細」の左端のセルに「商品コード」を入力すると、自動で右隣の「商品名」が表示されるということですね。
「価格表」から「商品名」を検索して「請求明細」のセルに自動入力

この例では、まず、「商品名」の入る最初のセル「B5」に数式を入れます。
「B5」をクリックして選択し、「数式」タブの「関数ライブラリ」にある「検索/行列▼」からドロップダウンするリストの一番下「VLOOKUP」をクリックします。
関数ライブラリ→検索/行列▼→VLOOKUPをクリック
関数の引数」ダイアログボックスが開きます。
VLOOKUP関数の引数は「検索値」「範囲」「列番号」「検索方法」です。
VLOOKUP関数の引数ダイアログボックス

引数(1):検索値

VLOOKUP関数の最初の引数は「検索値」です。
検索のキーとなるデータ」を指定しますこの例では「商品コード」ですね。

「商品コード」を入力する最初のセルを選択します。選択されたセル周囲に破線が回転し始め、ダイアログボックスの「検索値」の項目に「A5」と入りました。
VLOOKUPの引数「検索値」を設定

引数(2):範囲

次の引数の「範囲」とは、「検索するデータ範囲」のことです。
この例では、シートを「価格表」に切り替えて、
シートを「価格表」に切り替え
表のデータ範囲をドラッグして選択します。この時、見出し行は選択しません。選択するのは、データが入っている範囲だけです。
範囲を選択したら、F4キーを一度押して絶対参照にしておきます。
VLOOKUPの引数「範囲」を設定し、セル番地は絶対参照にする
表をテーブル化してある(または、範囲にあらかじめ名前を定義してある)なら、テーブル名(または、範囲に定義した名前)をそのまま入力します。
表がテーブル化されているなら、引数「範囲」にテーブル名を入力

引数(3):列番号

3番目の引数の「列番号」では、「前章の引数(2)の「範囲」の内、参照するデータの列は左から何番目か?」を指定します。
この例では、「商品名」を参照して表示したいわけですから、左から2番めの列ということです。半角で「2」と入力します。
VLOOKUPの引数「列番号」を設定

引数(4):検索方法

4番目の引数の「検索方法」は、「FALSE=検索値と完全一致で検索する」と「TRUE=完全ではないが近い値も検索する」のどちらの方法を選ぶかを指定します。

この場合は近似値を参照する必要がないので、「FALSE」完全一致の検索方法を選びます。「検索方法」の項目に数字の「0」を入力(「TRUE」なら「1」)。「FALSE」と打ち込んでもOK。
VLOOKUPの引数「検索方法」を「FALSE」に設定
通常「FALSE」指定がほとんどですが、検索方法の使い分けをちゃんと理解したい方は以下の記事を参照して下さい。

VLOOKUP関数の「検索方法」で「FALSE」と「TRUE」の使い分けについて実例を挙げて解説しています。

VLOOKUP関数で「FALSE」と「TRUE」をどう使い分けるか

エラー値「#N/A」の始末と数式のオートフィルコピー

これで、全ての入力が完了。
自分で数式バーに入力するなら、「=VLOOKUP(A5,価格表!$A$5:$C$9,2,0)」または「=VLOOKUP(A5,テーブル1,2,0)」になります。

さて、ここで、一つ厄介なことが…。
ダイアログボックスの項目を入力し終えて「OK」すると、「#N/A」というエラー値が返されています。
未入力セルに「#N/A」
検索値である「商品コード」に何も入れてないのだから、当然といえば当然ですね。
試しに「商品コード」を入力してみると、ちゃんと自動入力されます。
「商品コード」を入力するとエラーが消えて「商品名」が入力
このエラー値を表示させないようにするにはIFERROR関数でVLOOKUP関数を囲む必要があります。
=VLOOKUP(A5,テーブル1,2,0)」という式を「=IFERROR(VLOOKUP(A5,テーブル1,2,0),"")」 に変えてみて下さい。「#N/A」が消えて空欄になります。

未入力箇所の#N/Aエラーを消す方法の詳しい解説記事です。

#N/A消しにIFERROR関数を使う

最後に、A5セルを選択して「商品名」の列にオートフィルコピーします。

表に罫線などの書式が施されているなら、書式なしのオートフィルコピー。

書式がそのまま維持されるオートフィル

書式なしのオートフィルコピー

複数の列に数式を使い回し

他の列に、同じ表から異なるデータを転記したい時は、最初の数式を当該セルにそのままコピペして、引数の「列番号」だけを修正します。

この例では、「商品コード」入力時に「商品名」と共に「単価」も自動入力されるよう設定します。
「商品コード」入力時に「商品名」と共に「単価」も自動入力
前章までで組み立てた「商品名」の数式をコピーして「単価」の最初のセルにペースト、「=IFERROR(VLOOKUP(A5,テーブル1,2,0),””)」を「=IFERROR(VLOOKUP(A5,テーブル1,3,0),””)」に修正します。
「商品名」の数式をコピーして「単価」のセルにペースト、「列番号」を変更

VLOOKUP関数を使用した実例

見積書の作成プロセスで、ドロップダウンリストから選択した値に連動して「単価」の欄に数値が自動入力されるよう設定します。

ドロップダウンリストとVLOOKUP関数を連動させる

カレンダーの祝日と年中行事を自動で表示させる設定。VLOOKUP関数で祝日名を検索し、IFERROR関数で平日に返されるエラー値を処理します。

VLOOKUPで祝日と年中行事を自動表示

郵便番号を半角で打ち込み、日本郵便で配布している郵便番号データからVLOOKUP関数を使って住所を呼び出す方法です。

郵便番号表とVLOOKUPで郵便番号から住所1を自動表示する

タイトルとURLをコピーしました