※この記事は2023年1月に再編集しています。
VLOOKUP 関数を使って、下図の検索用エリアのセルに「社員番号」をいれたら、該当する社員の名前とメールアドレスが表示される、そんなしくみを作ってみます。
今回は同じワークシートのなかに、データを ”探しに行く場所” となる表を作成しています。この表の特徴は、
- 1 列目に検索に使うキーワードである [社員番号] の列を配置 (もちろん重複なし)
- 1 列目は昇順ではない ([部署] で並べ替えてまとめてあるので社員番号の順序はバラバラ)
- 2 列目に [氏名]、4 列目に [メールアドレス] を配置
- テーブルではない標準のセル範囲
です。
VLOOKUP 関数は指定した範囲の左端列でしか探してくれないので、キーワードとしたい値は表の左端列に準備しないといけません。
数式に必要な材料 (引数) の指定について
VLOOKUP 関数の構文はこちら↓
=VLOOKUP (検索値 , 範囲 , 列番号 , [検索方法])
検索値 には、検索したい値、またはその値が格納されているセルを指定します。
検索したい値を固定の文字列で指定する場合は “1020” “石田” のようにダブル クォーテーションで括ります。
範囲 には、検索したい値やそれに関連するデータが含まれる表のセル範囲を指定します。
1 行目の見出しは通常は範囲に含めません (含めてもエラーにはならないけれど探す必要がないのでいれない)。
列番号 には、範囲の左端列を列番号「1」として列番号を数えて、返したい値が指定した範囲の左から何列目にあるのかを番号で指定します。
[検索の型] には、検索したいデータが表の左端列で見つからなかった場合にどうするかを指定します。
検索値と完全に一致するデータだけを検索し、検索結果が見当たらない場合はエラーを表示する場合は FALSE を指定します。TRUE を指定、もしくは入力を省略した場合は、そのデータを超えない最大値が検索されます。このように動かしたいときには範囲の左端列のデータは昇順に並べ替えておく必要があります。
数式の作成
今回の例では、セル A2 の値を検索値として使用し、J5 から N24 を範囲として指定し、返してほしい値の格納されている列として左から 2 列目 ([名前] 列)、完全一致 (FALSE) の検索方法を指定します。
=VLOOKUP (A2 , J5：N24 , 2 , FALSE)
① 結果を表示するセルに数式を作成します。
セルやセル範囲は、入力してもよいしクリックやドラッグで選択してもよいです。
3 つ目の列番号を指定したあと、「，」を指定すると検索方法の候補が表示されます。[↓] で移動して [Tab] キーで候補から選択できます。
② [）] を入力して [Enter] キーで確定します。
③ メールアドレスは表の左から 5 列目です。
前の手順で作成した数式の列番号のところだけ異なる数式で対応できますが、セルをコピーすると参照がずれるので、コピーをしたいのなら中身 (数式) だけコピーして再利用するか、セルのコピーをした場合は参照を修正してください。
・表が別シートにある場合
探す場所として指定するセル範囲が、数式を作成している (結果を表示したい) シートとは異なるシートにあるときには、範囲にシート名を記述します。
・表がテーブルの場合
探す場所として指定するセル範囲をテーブルに変換しておくと、テーブル名で範囲を指定できるため、データが追加されたときに数式自体を編集する必要がありません。
・検索方法を TRUE にした場合
今回のデータでは、あえて 1 列目の値を昇順にしていません。このときに検索方法を TRUE にしたらどうなるのか見てください。
求めている結果になっていません。社員番号が 1018 の「織田 信雄」が結果として返っています。
検索方法で FALSE を指定すると、上から 1 つずつ検索値と一致するかどうかが比較され、一致する値が見つかったところで処理が中断、もし最後までいっても見つからなかったら #N/A エラーが返ります。
検索方法で TRUE を指定範囲のなかの中央の値と比較します。比較した値よりも検索値が大きければこれ以降、前半部分を探しません。そして、後半のなかの中央の値と再度比較して・・・と繰り返します。このような探し方を二分探索といいます。
そのため、TRUE としたい場合は 1 列目のデータを昇順で並べ替えておかなければならないのです。
最終的に該当する 1/2 のなかで近しい値があったとき、検索値そのものを超えない一番大きな値が検索結果となります。
辞書で何かを調べるときに、最初のページから 1 つずつチェックするのと同じように、FALSE は処理に時間がかかります。一方の TRUE は辞書の真ん中の文字と比べて、前半または後半だけに絞って探していくので FALSE よりも処理が速く進められます。
TRUE にしたいけれど完全一致にしたい、という場合は こちら をご覧ください。
VLOOKUP 関数の基本として、結果が 1 か所だけに返ってくればよい、という例でご紹介しました。
実務で非常によく使われる関数であるため、基本的なことを知っておく必要があります。
石田 かのこ
