VLOOKUP（ブイルックアップ）関数の使い方

関数概要

VLOOKUP（ブイルックアップ）関数は、マスタデータから該当するデータを呼び込む関数です。

この関数はスイッチをON（True）にして使う方法と、スイッチをOFF（False）にして使う方法と2パターンあります。業務データでは、OFFにして使うことがほとんどです。

[char no=”1″ char=”さえちゃん１”]例えば、社員ID「1100」は誰か？　社員マスタの表から「1100」の人を探し、社員名を引っ張ってくるという関数です。

もうひとつ例えると、商品を購入する際、レジでバーコードをピッと読み取って商品名や値段が表示されますよね。その仕組みを作る関数なんですけど、できれば業務で使いたくない関数でもあります。

だって、落ち着いて考えてみてください。VLOOKUP関数を使うときは、いつだって手元のデータをすんなり使えないときじゃないですか！？[/char]

動画解説

関数説明

会員番号だけわかっていて、マスタデータから「N0007」番の人を探してくる、というシーンを想定してください。

マスタデータを見ながらデータを直接手入力としてしまうと、入力ミスは免れません。マスタデータがあるのであれば、そのマスタデータを会員No.をキーとして差し込めば、入力ミスは起こりません。

ちなみに、マスタは「名簿マスタ」という別シートにあるとしましょう。

VLOOKUP関数は、別シートまたは別ブックから持ってくることが可能です。しかし、別ブックから読み込むのであれば、別シートにコピーして使った方が数式がシンプルになります。

もちろん、他のシートがロックされていて、別ブックでないと取り込めない！　という縛りがある場合もあるので、状況に応じて使い分けてください。

VLOOKUP関数は、[数式]タブ→[検索／行列]→[VLOOKUP関数]とあります。ABC順に並んでいるので、最後の方に位置しています。

引数が４つもあるので注意が必要です。

=VLOOKUP(①検査値,②データ範囲,③列番号,④検索方法)

まず、VLOOKUP関数にはスイッチがあります。「オン」と「オフ」を選択しないといけません。

こちらは後ほど説明を加えますので、「④検索方法」に指定するスイッチは「オフ」にして使用するということを覚えておいてください。

オフにするためには、「０」または「False」と入力します。

=VLOOKUP(①検査値,②データ範囲,③列番号,0)

続いて、①検査値です。検査値の入力は必ずセル参照になります。

さえちゃんの例でいう、バーコードでピッと読み取る場所。ときどき、検査値を範囲選択して複数箇所を選択してしまっているワークシートがあるのですが、その使い方は違います。

どこのセルを参照しますか？　という指定です。

だから、必ずセル参照の指定になります。この場合、セル「C5」、つまり「N0007」という[会員No]があるセルを参照します。

次に、マスタデータの場所を指定します。

[名簿マスタ]シートに切り替えて、マスタデータの表すべてを列選択します。

なぜ、列選択か？　それはマスタデータはいつだって追加削除がされるためです。

続いて列番号。

VLOOKUP関数は、V（ヴァーチカル：縦方向）から、LOOKUP（調べる）関数です。

ただ、調べる規則があって、この関数は必ず②の選択したマスタデータの表の左から１列目で検索を開始します。

だから[会員No]が１列目にないと、その瞬間にVLOOKUP関数は無効化となります。

①検査値のデータは、②のマスタデータの１列目より上から調べていき、①のセル参照した「N0007」を探します。

最後に③列番号。

=VLOOKUP(C5,名簿マスタ！A:F,③列番号,0)

これは、上から調べて「N0007」が見つかった場合、その見つかったセル位置の、左から何列目のデータを出力しますか？　という指定になります。

つまり、「3」と入力すれば名前が出力され、「4」と入力すれば年齢が出力されます。以下のキャプチャでは「3」と指定したので、「斎藤　美佳」がピックアップされます。

ここで、④検索方法で「0」または「False」と指定した理由を説明します。

①検査値をもとに、②指定したデータ範囲の１列目を探していって、①検査値と完全一致したら、③列番号のデータを送る、という命令となります。

スイッチオフで使うとは、こういうことです。OKボタンを押して確定すると、このとおり。

年齢のデータをピックアップするときは、まったく同じ数式で4列目を指定すればOKです。

あとはオートフィルで全部のデータを出力できます。

これがスタンダードな使い方です。

この関数は自転車に乗るのと同じで、１回乗ってしまえばそう簡単に忘れることはありません。けれども乗るまでが大変な関数なので、腹落ちするまでしっかり勉強してくださいね。

VLOOKUP関数のあれこれ

＃N/A（ノーアサイン）とIFERROR関数

検査値を指定したけれども、マスタにそのデータがないとき、「#N/A（ノーアサイン）」というエラーが出てきます。

※ 日本語読みがノーアサインで、英語読みがNot Available valueです。

[char no=”1″ char=”さえちゃん１”]余談ですが、Excelのエラーで「#N/A」はビックリマークつかないんですよ。ほかのエラーはビックリマーク「！」がつくのにね。

これは、今はエラーではあるけれども、データが補完されれば表示されるので、だから「！」がないのかな？　と勝手に想像しています。ほかのエラーはすべて式を修正しないと対処できませんからね。[/char]

会員番号を存在しない「N0100」に書き換えてみます。このとおり、マスタデータに所定のデータはありません！　というエラーが表示されます。

どうしてもこれを消したい場合、IFERROR関数と組み合わせて使用します。IFERROR関数については、こちらでは関数の説明を割愛しますが、ここだけ改めて掲載しておきます。

空白セルを表現する「””」は、マクロファイルを使用されている場合等、通常の空白セルではなく、データの存在する空白セルという認識をしてしまうため、誤作動を招きかねない数式となります。

ですので、表示上は目的を達成できますが、あまりおすすめな数式ではありません。

マスタデータで検索値のデータが重複している場合

マスタデータに重複したコードがあると、VLOOKUP関数は１列目の上からデータを調べていく作業を行うため、２番目以降のコードは絶対に読み取りません。

以下の場合、N0005のコードで「青木 彩加」さんは絶対に表示されません。

このとおり、「伊藤 夏江」さんが出力されます。

マスタデータに重複した値がないかどうか？　しっかりと確認しましょう。

これは、[条件付き書式]で簡単に調べることができます。マスタデータの１列目を選択します。

続いて、[条件付き書式]→[重複する値]を選びます。

条件はなんでも構いません。わかればいいので、このままOKです。

これで重複したコードの有無を確認できます。

確認後は、余計な条件付き書式を残したままにするのは気持ちが悪いので、「CTRL+Z」で１手順前に戻しておくと安全です。

さえちゃんのVLOOKUP関数ワンポイントアドバイス

[char no=”3″ char=”さえちゃん3″]VLOOKUP関数はExcel初級者には鬼門となる関数だけど、この関数を覚えることで他の関数も覚えられるから、Excel初心者はVLOOKUP関数を先に勉強することをおすすめします！　この関数を覚えられないと、SUMIF関数とかCOUNTIF関数とか身につかないんだ。

だからしっかりマスターしてね！[/char]

また、スイッチをONで使う第４引数を「1」または「True」で使う場合はこのとおり。

現在のポイントは「23」ポイントなので、受けられるサービスは「大盛り無料」までとなります。ポイント数が「27」でも「29」でも「大盛り無料」までです。「替え玉1回無料」の権利はありません。

ExcelではFalseを[完全一致]に対して、Trueを[近似値検索]と表記しているため、数値の近いほうがヒットすると思いがちですが、そうではありません。

ポイントカードをイメージしていただければ、Trueの検索方法の動きがわかるかと思います。

業務データでこのようなデータの取得はほとんどありません。Excel講師でFOMの研修テキストぐらいでしか遭遇したことがないので、こんなのは覚えなくてかまいません。

Googleスプレッドシート向けアドバイス

VLOOKUP関数は、画像を代入させることも可能です。まず、画像マスタ表を作成します。

まず、画像を入れたいセルを選択した状態で、画像を選択します。

画像の右上にある３点ボタンを押して、[選択したセルに画像を置く]を選びます。

これで画像がセルに差し込まれました。

同様にして、ほかの画像を入れます。

これで、VLOOKUP関数で読み込んでみましょう。このとおり、セルに画像を埋め込めばVLOOKUP関数で画像を別シートに出力されることも可能です。

クリエイティブレポートなど、画像を使ったレポートを作成する場合は重宝しますので、ぜひこちらも覚えておいてください。

ちなみに2021年時点ではExcelにはありません。

埋め込んだ画像を開放するためには、埋め込んだセルを右クリックして、項目の下にあるこちらを押してください。

これでシート上に画像が戻ってきます。

関数ステータス

関数ライブラリの種類

検索／行列

数式の構文

＝VLOOKUP（検索値,範囲,列番号,検索方法）　

引数ダイアログ

Microsoft 公式サポート

VLOOKUP関数 – Office サポート

榊 裕次郎

Excel講師

1981年10月生まれのてんびん座、東京都出身。趣味は、旅行と料理とワイン。2024年は、佐賀県に行って「呼子のイカ」を思いっきり食べたいです。

仕事では2023年も引き続き、青森・秋田・岩手でのお仕事依頼、お待ちしております！