【Excel・エクセル】VLOOKUP関数の使い方!検索して抽出する関数?

もりの

社員コードから社員名簿にある所属部署を検索して抽出したいんだけど、いい方法はある?

やまの

それなら、VLOOKUP関数を使うんよ。

今回のポイント
  • 検索値を元に別の範囲から値を抽出できるのが、VLOOKUP関数
  • VLOOKUP(この値をもとに,この中の,左から何番目を抽出して,基本0でOK)
  • IF関数などと組み合わせて使うことも

スポンサーリンク

VLOOKUP関数の使い方の練習ファイルダウンロード

もりの

一緒にやってみませんか?

【Excel・エクセル】VLOOKUP関数とは?指定した値を検索して抽出する

VLOOKUP関数のイメージ
VLOOKUP関数は、こういうイメージ

VLOOKUP関数は指定した値をもとに、別の範囲から検索して値を抽出してくれる関数です。

やまの

これができれば、エクセル得意ですって言えるような関数だね!

ちなみに、読み方は「ブイルックアップ関数」です。

「V」はvertical(垂直)の頭文字で、「LOOKUP」は調べるという意味です。

【Excel・エクセル】VLOOKUP関数の使い方

VLOOKUP関数の書き方

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

やまの

簡単に説明すると、こうなるよ。

検索値この値を基準にして
範囲この範囲の
列番号左から何番目にある値を教えて!
検索方法検索値と全く一緒じゃないとダメか、似てるもでいいのか

VLOOKUP関数を実際に使ってみよう

下の図の左の表の所属部署を、右の社員名簿からVLOOKUP関数を使って取り出したいと思います。

VLOOKUP関数の練習問題
VLOOKUP関数の練習問題
うみの

ツッコミどころ多いが……、とりあえず全員打楽器だぞ?

やまの

=VLOOKUP(A3,F:I,3,0)で、求められるよ。説明するね。

初めに

まずは関数名を書きます。

C3のセルに、「=VLOOKUP(」と入力してください。

VLOOKUP関数の書き始め
VLOOKUP関数の書き始め

検索値

最初は検査値、探す基準になる値を決めます。

検索値は、元のデータと抽出する範囲に共通してある値を選びます。

今回は、左の表の出演者と右の表の社員名が同じ値になっているので、名前を基準にするのがよさそうです。

VLOOKUP関数の検索値を指定する画像
検索値はどちらの表にもある値

セルC3では、もりのの部署を調べたいので、もりのと書かれたセルA3をクリックして、「,(カンマ)」を入力します。

検索値を選んだ画像
検索値を選ぶ

範囲

次は範囲。

「もりの」という名前をもとに、社員名簿から所属部署を持ってきたいので、社員名と所属部を含む範囲が良さそうですね。

VLOOKUP関数の範囲を指定する画像
抽出したい表を範囲選択
やまの

この「範囲」には、1つルールがあるんよ。

それは、必ず検索値が一番左になるよう範囲選択すること。

今回は社員名を検索値にしているので、社員名が一番左になるように、F~I列を選択します。

VLOOKUP関数の範囲の決め方
VLOOKUP関数の範囲の決め方

ちなみに「F3:I7」と指定をしてもいいのですが、オートフィルを使う場合、指定範囲がずれてしまうので、絶対参照にしないといけません。

また、新しい社員が増えるなどデータが増える可能性があると、範囲を広げる必要があります。

もりの

オートフィル?絶対参照??

やまの

詳しくは、別のページでお話しするね!

なので、今回はF列~I列を選択しちゃいましょう。

列F~Iを選択して、「,(カンマ)」を入力してください。

範囲には社員名簿の列を選択
範囲には社員名簿を列で選択

列番号

お次は列番号。

取り出したい値は、範囲の左から数えて何番目かですね。

所属部署は3番目にあるので「3」と入力し、「,(カンマ)」です。

VLOOKUP関数の列番号の考え方
左から数えて何番目か

検索方法

最後の検索方法ですが、基本的には「0」でいいと思います。

0(FALSE)にすると「完全一致」、1(TRUE)にすると「近似一致」になります。

もりの

どう違うの?

完全一致検索値と検索先の値が全く同じときだけ、値を抽出できます。
検索値が田中太郎さんなら田中太郎さん、10なら10の時だけ反応します。
近似一致検索値と同じでなくても、近い値をこれだろうと抽出します。
検索値が田中太郎さんでも田中次郎さんを、検索値が10でも、……近いからこれこれ♪と抽出することがあります。

ここでは正確に、同じ名前の人の所属部署を知りたいので、「0」を入力します。

完成!

これで式は完成です!括弧を閉じて、Enterキーを押してください。

もりのの所属部署が表示された
もりのの所属部署が表示された
もりの

ふっふっふ……僕が社長だぞー!

VLOOKUP関数で複数条件を指定するには

VLOOKUP関数の検索値は、基本1つです。

ただ、例えば関東地区の大型店舗の売上を知りたい!など、複数の条件に合う値を抽出したいとき、VLOOKUP関数は使えるのでしょうか。

やまの

複数の条件をまとめたセルを作ればいいんよ!

強引な感じもしますが、条件となる複数のセルを&でつなげたセルを作ります。

そのセルを範囲の一番左にいれればいいのです!

条件のセルを&で繋げた画像
条件のセルを&で繋げる
やまの

詳しいやり方は、こっちのページを見てね!

VLOOKUP関数の範囲に別シートを指定するやり方

範囲には、他のシートの範囲も、別のエクセルファイルの範囲も指定できますよ。

ここに果物の売上表があります。

売上表と単価表、2つのシートがありますね。

VLOOKUP関数の練習問題
売上表と単価表が別のシート

売上表シートの単価を、単価表シートからVLOOKUP関数を使って抽出したいと思います。

別シートからVLOOKUP関数を使って検索する
単価表は別シートにある

ステップ1

まず検索値は、、売上表にも単価表にも共通してある値でしたね。

今回は果物の名前がよさそうです。

というわけで「A2」をクリックします。

VLOOKUP関数の検査値を指定した画像
検索値はA2

ステップ2

次に範囲ですが、知りたい単価があるのは「単価表シート」です。

そのまま単価表シートをクリックしてシートを移動してください。

別シートに移動した画像
単価表シートに移動できた

ステップ3

範囲は検索値と抽出したい値を含んだ、A列とB列が良さそうですね。

単価表シートのまま、A列からB列を選択してください。

単価表シートのA:Bを選択
単価表シートのA:Bを選択

ステップ4

次に列番号ですが、単価は範囲の左から二番目にあるので、「2」と入力、そして「,」
検索方法は「0」と入力してください。

カッコを閉じてエンターキーを押せば、VLOOKUP関数で別シートから値を抽出できました!

VLOOKUP関数で別シートから値を抽出できた画像
違うシートから単価を検索して抽出できた
もりの

別シートを参照していると、「シート名!」ってなるんだね。

やまの

「!」を間違って消さないように気を付けてね。

【Excel・エクセル】VLOOKUP関数とIF関数の組み合わせ

VLOOKUP関数とIF関数を組み合わせると、VLOOKUP関数の結果を条件にしたIF関数にしたり、空白セルを無視してVLOOKUP関数ができたりします。

前者の書き方は「=IF(VLOOKUP(~),○,×)」、後者の書き方は「=IF(○="","",VLOOKUP(~)」です。

やまの

詳しくは、こっちのページで紹介するね。

これで例えば、売上表の製品の価格を価格表から抽出したり、部活動員名簿からだれが何部に所属しているかを抽出したりできますね!

それでは、お疲れさまでした!

今回のポイント
  • 検索値を元に別の範囲から値を抽出できるのが、VLOOKUP関数
  • VLOOKUP(この値をもとに,この中の,左から何番目を抽出して,基本0でOK)
  • IF関数などと組み合わせて使うことも

数式・関数,Excel検索

Posted by morino