VLOOKUP:ブイルックアップ【別表にある条件に合致したデータを表示】
=VLOOKUP(検索値,範囲,列番号,検索の型)
■「範囲」に指定した表の左端列で、「検索値」に合致するデータを探し、該当する行の「列番号」で指定した列にあるデータを表示する。「検索値」と完全に一致するデータを探す場合は、「検索の型」を「FALSE」とする
図1のような伝票で、「品番」を入力すると、自動で「製品名」や「単価」を表示する――。こんな便利な仕掛けも、関数を使えば簡単に実現できる。これには、条件に合致したデータを別表から取り出すVLOOKUP(ブイルックアップ)関数を使う。VLOOKUP関数は4つの引数を指定する必要があるので、少々ややこしい。図1のB2セルのケースで、具体的に見てみよう。
VLOOKUP関数の最初の引数「検索値」には、別表からデータを取り出す際のキーとなるデータを指定する。ここでは品番で探すので、品番を入力するA2セルを指定する。
次の引数「範囲」には、検索対象となる別表のデータ部分全体(G3:I5)を指定する。こうすると、「範囲」で指定したセル範囲の“左端”の列を調べ、「検索値」と一致したセルと同じ行にあるデータのいずれかを取り出して表示できる。何列目のデータを取り出すかを指定するのが、3番目の引数「列番号」だ。製品名を表示するには、別表の2列目にあるので、「列番号」を「2」にする。ここを「3」にすると、単価を表示できる。
なお今回の例のように、「検索値」と別表の左端の列のデータ“完全に一致する”場合は、最後の引数「検索の型」に「FALSE」と指定する必要があるので注意しよう。
図1のB2セルの式を下のセルにもコピーして利用したい場合は、一工夫必要だ。別表の範囲(G3:I5)は、他のセルでも共通して参照するので、コピーしたときにずれないように「$G$3:$I$5」と絶対参照にしておこう(図2)[注]。
なおVLOOKUP関数の引数「検索の型」を「TRUE」にすると、完全一致ではなく、「検索値以下で最も近い数値」を探すことができる。
例えば図3のように、重量別の料金一覧表から、指定した重量に対応する料金を調べることが可能だ。C2セルに図3のようなVLOOKUPの関数式を立てる。このときB2セルに「0.5」と入力すると、別表の左端にある「0」「1」「5」「10」の中で、「0.5」以下で最も近い数値の「0」が合致し、同じ行の2列目にある「250」が表示される。
[注] なお「検索値」で指定したセル(ここでは品番)が空欄だと、VLOOKUP関数がエラーになる。これを回避するには、IF関数を使って空欄の場合は空欄を表示させる。例えば図のB2セルなら「=IF(A2="","",VLOOKUP(A2,$G$3:$I$5,2,FALSE))」とすればよい