元になる表を検索して、その中から合致するデータを転記する「VLOOKUP」関数。基本的な使い方に加えて、仕事に役立つ実例も紹介する。

表から合致するデータを取り出すVLOOKUP(ブイルックアップ)

 元になるデータベースを表を参照して、該当するデータを別の表に転記するというシーンはビジネスではよくあるはず。例えば、単価表が別にあって、そこから指定した商品の単価を転記する場合などがその典型例。そんなときに頼りになるのが、「VLOOKUP」「HLOOKUP」「LOOKUP」の3つの関数だ。

 3つの関数の中で一番よく使う関数がVLOOKUP。ビジネスパーソンの利用率ナンバー1ともいわれている。ぜひ使いこなせるようにしておきたい。

書式

書式 VLOOKUP(検索値,範囲,列番号,検索の型)
検索値(必須) 検索値を指定する
範囲(必須) 検索する範囲を指定する
列番号(必須) 先頭(一番左)の列から数えて何列目かを指定する
検索の型(省略可) 「FALSE」の場合は、検索値と完全に一致する値を探す。「TRUE」または省略の場合は、検索値と一致する値がなかった場合、検索値未満で最も近い値を探す

基本的な使い方

 この例では、飲み物メニューの単価と売上杯数の表を使って、E2セルに飲み物メニューの名前を入力すると、売上杯数をF2セルに表示させる。F2セルに記述するVLOOKUP関数の書式を考える前に、人間が目視で調べる場合を考えてみよう。ここにVLOOKUP関数をマスターするポイントがあるのだ。

 例えば、ウーロン茶が何杯売れたか、売上杯数を調べるとしよう。目視で確認する場合、まず商品の欄(A列)を下方向に探し、「ウーロン茶」を見つけたら右方向に目線を移動し、売上杯数を確認するはず。VLOOKUP関数では、これと同じように「範囲」や「列番号」を記述する。

 具体的には、「検索値」に探したい文字列や数値を指定し、「範囲」にどの表から探すかを記載する。そしてその文字列を見つけたら、「列番号」で指定した列数(ここでは「3」)だけ右にたどり、そのセル(ここでは「C5セル」)のデータを取り出す。ちなみに「列番号」を2にすれば、単価が表示される。

最左列から検索値を探し、値が見つかったらその行を指定された列数分右に移動し、データを取り出す
最左列から検索値を探し、値が見つかったらその行を指定された列数分右に移動し、データを取り出す

【仕事での使い方1】商品番号に合致した品名と単価を別表から転記する

品番を入力すると、同じシートの別の位置にある商品一覧表から、該当する品名と単価を自動で転記する──。こんな便利な仕掛けも、VLOOKUP関数ですぐに実現できる
品番を入力すると、同じシートの別の位置にある商品一覧表から、該当する品名と単価を自動で転記する──。こんな便利な仕掛けも、VLOOKUP関数ですぐに実現できる

 上図のような表で、入力を楽にするために、品番を入力すると、それに対応した品名や単価が自動的に表示されるようにしたい。まさにVLOOKUP関数の出番だ。「列番号」を「2」にすると2列目にある品名を転記でき、「3」にすると3列目の単価を転記できる。

 なお「範囲」で指定する別表のセル範囲は、コピーしてもずれないように、絶対参照にしておくのもVLOOKUP関数を使いこなすポイントだ。

「検索値」の品番の値「11」を、別表のデータ部分(G3:I8)の左端の列で探し、一致したG3セルと同じ行の2列目にある「ダスター」を表示する。式を入力したら、B4~B6セルにドラッグしてコピーする。このとき「範囲」の参照範囲がずれないよう絶対参照にしておく
「検索値」の品番の値「11」を、別表のデータ部分(G3:I8)の左端の列で探し、一致したG3セルと同じ行の2列目にある「ダスター」を表示する。式を入力したら、B4~B6セルにドラッグしてコピーする。このとき「範囲」の参照範囲がずれないよう絶対参照にしておく
同じようにして、単価もVLOOKUP関数で転記する。この場合は、「列番号」を「3」にすればよい
同じようにして、単価もVLOOKUP関数で転記する。この場合は、「列番号」を「3」にすればよい

【仕事での使い方2】個数に対応した単価を表示する

入力した個数に対応する単価を、自動で表示したい。これもVLOOKUP関数を使って実現できる
入力した個数に対応する単価を、自動で表示したい。これもVLOOKUP関数を使って実現できる

 購入数に応じて、単価が割引になる商品がある。100個未満なら150円、100~499個なら140円……といった具合だ。このとき、入力した購入数に対応する単価を自動で表示できると便利。VLOOKUP関数で実現してみよう。

 ポイントは、「検索の型」を「TRUE」にすること。これで、「検索値未満で最も近い値」を検索できる。つまり「○以上△未満」という範囲を対象に転記できるわけだ。

 最初に購入数と単価の関係を別表にまとめよう。「購入数」には「○個以上△未満」の○個以上の数値を小さい順に入力する。これでVLOOKUP関数の引数「検索の型」を「TRUE」とすると、「検索値未満の最も近い値」を探せる。例えば購入数に「580」を入力すると、580未満で一番近い数字である「500」がデータの対象と見なされ、その左(列番号「2」)にある単価を取り出せる。

VLOOKUP関数を使いこなすには「検索の型」の「TRUE」の活用法も身につけておきたい
VLOOKUP関数を使いこなすには「検索の型」の「TRUE」の活用法も身につけておきたい