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

【構文】

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

【使用例】

=VLOOKUP(B2,マスタ!A:G,3,false)
セルB2のデータを参考に、別シート「マスタ」のA列を上から順番に探していきます。「マスタ」のA列にセルB2のデータが完全一致した場合、A列から数えて3列目に該当するデータを出力します。

=VLOOKUP(B2,マスタ!A:G,3,0)
falseを0と置き換えても上の使用例と同様になります。

=VLOOKUP(B2,マスタ!A:C,2,True)
セルB2の数値データを「52」とします。その「52」を参考にして、別シート「マスタ」のA列を上から順番に探していきます。「マスタ」のA列に「25」「50」「75」とデータが続いていた場合、「50」のデータが入力しているセルに着地し、A列から数えて2列目に該当するデータを出力します。この場合、セルB2のデータに「74」が入力されていても「50」のデータに着地します。

=VLOOKUP(B2,マスタ!A:C,2,1)
True=1と置き換えても上の使用例と同様になります。True、1の検索方法は、ポイントカードをイメージするとわかりやすいでしょう。

例えば、社員ID「1100」は誰か? 社員マスタの表から「1100」の人を探し、社員名を引っ張ってくるという関数です。

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

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

関数説明

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

続いて列番号。

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

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

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

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

最後に③列番号。

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

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

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

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

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

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

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

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

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

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

VLOOKUP関数のあれこれ

#N/A(ノーアサイン)とIFERROR関数

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

余談ですが、Excelのエラーで「#N/A」はビックリマークつかないんですよ。ほかのエラーはビックリマーク「!」がつくのにね。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

VLOOKUP関数はExcel初級者には鬼門となる関数だけど、この関数を覚えることで他の関数も覚えられるから、Excel初心者はVLOOKUP関数を先に勉強することをおすすめします! この関数を覚えられないと、SUMIF関数とかCOUNTIF関数とか身につかないんだ。

だからしっかりマスターしてね!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

関数ステータス

関数ライブラリの種類

検索/行列

数式の構文

=VLOOKUP(検索値,範囲,列番号,検索方法) 

引数ダイアログ

Microsoft 公式サポート

VLOOKUP関数 - Office サポート