データの入力作業を効率化に必須のVLOOKUP関数
「商品IDを入力したら、自動で商品名や価格も表示されたらいいのに」「顧客コードを入力したら会社名や住所がパッと出るようにしたい」
これらと似たようなご相談を頻繁に頂きます。要は、顧客や商品などを特定するキーをあるセルに入力すると、その顧客や商品に関する情報が別のセルに表示されるようにできればいいわけです。
このようなことができるのがVLOOKUP関数です。「VLOOKUP」という関数名の一文字目の「V」は「Vertical」の頭文字で、「垂直に」という意味です。「LOOKUP」は「探す」と訳せますから、この関数は「何かを縦方向に探す」というイメージで理解して頂きたいと思います。
＜目次＞
- VLOOKUP関数の使い方
- VLOOKUP関数の4つの引数
- 第二引数では、メンテナンス性を高める「列全体指定」がおすすめ
- 完全一致か近似一致か。第四引数「検索の型」の使い方
- エラー値を出さないために
- VLOOKUP関数で、検索列より左側の値を参照できる？
- VLOOKUP関数で部分一致による検索はできるか
- HLOOKUP関数も合わせて知っておきましょう
- IF関数のネストが増えてきたらVLOOKUP関数で簡略化を
VLOOKUP関数の使い方
例えば次の表のA列からC列に、商品ID、商品名、価格を入力しなければいけないとき。
商品情報の入力シート
商品IDと商品名と価格の3項目を何行も入力するのはめんどくさいわけです。なので、A列のセルに商品IDだけ入力すれば、B列のセルに商品名、C列のセルに価格が出てくるようにすればラクなわけですね。
それにはまず各商品IDに該当する商品名と価格の一覧表、いわゆるマスタを別途どこかに作成しておく必要があります。こちらの例では、E列からG列にその商品マスタが用意してあります。
ではあらかじめ、A4セルに「A001」という商品IDを入力した状態でB列のセルに関数を入力してみましょう。B4セルに次のVLOOKUP関数式を入れると、A4セルの商品IDに該当する商品名が出ます。
=VLOOKUP(A4,E:G,2,0)
VLOOKUP関数を入力
まずこの式を日本語に訳してみます。
第一引数で指定したA4セルの値（「ここではA001」）を、第二引数で指定したE列からG列の一番左の列…つまりE列で上から探し、見つかったらそのセル（ここではE4セル）から第三引数で指定した列目（2列目ということになります）の値を参照せよ。
…ということになります。
第四引数には「0」が入っていますが、これはこのあと解説します。
ちょっと乱暴な説明にはなりますが、まずは初めてこの関数を勉強する際は、第四引数はとりあえず0と打てばよいと覚えてください。実は0か1が選べます。
より実務的には、A列のセルが空白の時にエラー値が出ないように、
=IFERROR(VLOOKUP(A4,E:G,2,0),"")
または
=IF(A4="","",VLOOKUP(A4,E:G,2,0))
といった式を入力することになります。
また、第四引数は0ではなくFALSE、1ではなくTRUEと打つこともできますが、その辺は好みの問題です。
さらに単価を出したいC列のセルに入力する関数は、第三引数が「3」になります。単価は、マスタであるE:G列の左端列から見て3列目にあるからですね。
さらに実際の入力では改めてB列とC列にそれぞれ一から入力するのでなく、B列に入力した式を使いまわせるようにB列には次のように絶対参照の設定を加えた式を入力し、C列にコピーして第三引数だけを2から3に修正するという手順がラクです。
=VLOOKUP($A4,$E:$G,2,0)
「＄」マークは絶対参照を表し、「=$A4」の、「A」の前の「$」がA列を参照しつづけることを意味し、「4」行目は「$」がついていないため変化すること（相対参照）を意味しています。
さらにラクにするには第三引数に、指定したセル参照の列番号を返すCOLUMN関数を工夫して使うと2を3に書き換える必要さえなくなる…など、関数はいろいろ組み合わせることで仕事がどんどんラクになるのです。絶対参照やCOLUMN関数などを使ったテクニックについては別の記事で解説致します。
VLOOKUP関数の4つの引数
では、VLOOKUP関数の4つの引数について理解していきましょう。=VLOOKUP（検索値，検索範囲， 列番号，検索の型）
- 第一引数：「検索値」。いわゆる、手がかり、キーとなる値ですね。第二引数で指定した範囲の左端列で探す値です。
- 第二引数：「検索範囲」。左端列で値を探す範囲。マスタとして使う範囲です。列全体で指定するのがポイントです。
- 第三引数：「列番号」検索値が検索範囲の左端列で見つかったら、そこから右側の何列目のセルを参照するかという指定。
- 第四引数：「検索の型」。0にすると完全一致、1にする近似値一致となります。詳しくはこのあと解説します。
もしF列で検索値が見つからなければ、VLOOKUP関数は「#N/A」というエラー値を出します。
見つかった場合は、そのセル（上記B2セルの場合はA2セルの値、つまり""）が見つかったら、そのセル（上記例ではF3セル）から第三引数で指定した数の列目…上記の場合は第三引数は2ですから、F3セルから2列目であるG3セルの値を参照する、ということになるのです。
第二引数では、メンテナンス性を高める「列全体指定」がおすすめ
VLOOKUP関数の第二引数（ここでは「E:G」）は列全体指定をおすすめしています。理由はマスタの行数が増えても式を修正する必要がないからです。「列全体指定は動作が重くなるからやめたほうがよい」というご意見もありますが、まず影響はありません。
上級者の方は名前の定義で可変対応……といったこともお考えになるかもしれませんが、他の方でも簡単に扱えるシンプルな数式で済むなら業務としてはそれがベストです。
完全一致か近似一致か。第四引数「検索の型」の使い方
第四引数、「検索の型」について0にすると完全一致、1にする近似値一致と述べました。実際にどのように使い分けるかというと、たとえば次のように購入金額別に顧客のランクを定めるようなケースが実在の某社で行われたことがあります。- 購入金額が0～100万円の人はE
- 購入金額が100万1円～200万円の人はD
- 購入金額が200万1円～300万円の人はC
- 購入金額が300万1円～400万円の人はB
- 購入金額が400万1円～500万円の人はA
- 購入金額が500万1円以上の人はS
購入金額が100万1円の人からDになります。
購入金額が350万円の人はB。
購入金額が420万円の人ならAになるわけです。
またテストの点数などでも同様に90点以上はA、80点～89点はBなどのように、一定の数値範囲ごとに評価やランクを設定する作業です。このような時に、VLOOKUP関数の第四引数を1（またはTRUE）にする、「近似一致」という方法が使われます。いわゆる「あいまい検索」ですね。
まず、次のシートのE:F列のようなマスタを用意しておきます。大事なポイントは、E列の購入金額が必ず大きい順、つまり昇順で並んでいることが必須ということです。
C4セルにVLOOKUP関数を入力
この状態で、C4セルに次の式を入力し、6行目までコピーするとこのように各購入金額に該当するランクが入力されます。
=VLOOKUP(B4,E:F,2,1)
VLOOKUP関数の第四引数を0（またはFALSE）にしておくと、検索範囲左端列での検索において、第一引数の「検索値」と完全一致する値にしかヒットしません。
しかし第四引数を1（またはTRUE）にすると、検索範囲の左端列において、第一引数の「検索値」の数値以下の最大値にヒットするようになるのです。検索値と完全一致する値か、それを超えない最大値にヒットするということです。
エラー値を出さないために
より実務的には、A列のセルが空白の時にエラー値が出ないように、エラーが出たときに表示させたいデータを設定できるIFERROR関数を使い、=IFERROR(VLOOKUP(A4,E:G,2,0),"")
（VLOOKUP関数の処理でエラーが出たときに空白を表示させる）
または、条件によって処理を変えるIF関数を使い、
=IF(A4="","",VLOOKUP(A4,E:G,2,0))
（もしA4のセルが空白だった場合は空白を表示させる、空白でない場合はVLOOKUP関数のデータを表示させる）
といった式を入力することになります。
詳しくは「IFERROR関数でエラー値をわかりやすく表示させよう」「IF関数の使い方 条件によって処理を変える自動判定」をご覧ください。
VLOOKUP関数で、検索列より左側の値を参照できる？
よく頂く質問なのですが、これはVLOOKUP関数ではできません。このようなケースではOFFSET関数とMATCH関数で対応します。記事「OFFSET関数の使い方～表の中で特定の値を参照する」で解説していますのでご参照ください。VLOOKUP関数で部分一致による検索はできるか
こちらもよく頂くご質問です。これは可能です。例えば検索値と完全に一致するセルを探すのではなく、「A」という文字を含んだセルを探したいという場合は、次のようにワイルドカード文字（検索する際にどんなパターンにもマッチする特殊文字）である「＊」（アスタリスク）と組み合わせます。
=VLOOKUP("*"＆”A"＆"*",検索範囲,列指定、検索の型)
HLOOKUP関数も合わせて知っておきましょう
あまり使用頻度は高くなく、そもそもこの関数が必要なシート設計に問題があると筆者は考えているものですが、VLOOKUP関数と合わせてご紹介しておきたいのがHLOOKUP関数です。VLOOKUPの頭文字「V」が「Vertical」（垂直）のVなら、HLOOKUPの頭文字の「H」は「Horizontal」（水平）のHです。
次のように、マスタが横方向になっているようなケースではHLOOKUP関数で対応することになります。しかし根本的にはマスタは縦方向のデータベース表形式で作成するべきなので、本来はそこから改善したいケースではあります。
HLOOKUP関数の例
C4セルに次の式を入力して、6行目までコピーした結果です。
=HLOOKUP(B4,$E$3:$K$4,2,1)
これは、第一引数で指定した検索値を、第二引数で指定した範囲の「1行目」（ここではE3:K3）で検索し、見つかったらそこから数えて「2行目」（第三引数で指定した数字）にあるセルを参照する、という処理を行っています。
IF関数のネストが増えてきたらVLOOKUP関数で簡略化を
IF関数を解説した記事「IF関数の使い方 条件によって処理を変える自動判定」で、IF関数のネストが増えてきたらVLOOKUP関数に切り替えましょうということを紹介しました。たとえば先ほどのような購入金額レンジごとにランクを設定するような作業は、多くの方が最初にIF関数で一つずつ設定していくことを発想されると思います。
しかしそうではなく、先ほどの例のようにあらかじめデータを変換するマスタを別途用意しておけば、シンプルなVLOOKUP関数で済んでしまうわけです。
【関連記事】
