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

2020/6/18

もりの

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

やまの

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

今回のポイント

  • あるデータを別のデータから引っ張ってくるのが、VLOOKUP関数
  • VLOOKUP(この値をもとに,この中の,左から何番目を持ってきて,似てるのはどうする?)
  • IF関数などと組み合わせて使うことも

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

もりの

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

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

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

VLOOKUP関数は指定した値をもとに、別のデータから検索して値を抽出してくれる関数です。
これができれば、エクセル得意ですって言えるような関数ですよ!

ちなみに「ブイルックアップ関数」と読みます。

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

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

関数の書き方

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

やまの

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

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

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

下図の表は、もりの音楽会でだれがどの楽器を演奏するかをまとめた表です。
別にある社員名簿を使って、それぞれの所属部署を検索して抽出したいと思います。

例題の表
例題の表
うみの

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

VLOOKUP関数を使って、もりのの所属部署を検索して抽出していきます。

やまの

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

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

もりの

半角英数入力モードにしてから、入力するんだよね。

検索値

最初の引数は、「この値を基準にして」という、検索値を指定するものでした。
検索値は、今のデータと、引っ張ってくる別のデータに共通している値を選びます。

もりの

引数は関数の括弧の中に入れる式だよね。

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

検索値はどちらの表にもある値
検索値はどちらの表にもある値

もとにする値は、もりのの名前が書いてあるセル「A3」になりますね。

範囲

二つ目の引数は「この中の」でした。
「もりの」という名前をもとに、社員名簿から所属部署を持ってきたいので、「この中の」は社員名簿のことですね。

やまの

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

この「範囲」ですが、必ず検索値が一番左になるような範囲にしてください。

もりの

もりのの名前が一番左になるように、範囲を選べばいいんだね。

検索値である名前の列を一番左にして、社員名簿の列をすべて選びました。
今回でいうと、F列からI列ですね。

「F3:I7」と指定をしてもいいのですが、オートフィルを使う場合、指定範囲がずれてしまうので、絶対参照にしないといけません。
また、新しい社員が増えるなど、引っ張ってくるデータが増える場合、範囲も広げる必要があります。

もりの

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

やまの

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

なので、表の書き方などにもよりますが下図のような場合だと、範囲は表を範囲指定するより列全体を選ぶと楽……だと個人的には思います♪

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

もし検索値が社員Noだった場合は、範囲は検索値が一番左がルールなので、G列からI列になりますね。

列番号

次の引数は、「左から何番目にある値」でした。
知りたいのは所属部署ですが、それは前の引数「範囲」の中で、左から数えて何番目の列にあるのか?を入力します。

範囲はF列からI列でした。1番左のF列は社員名、2番目は社員No、所属部署は3番目ですね。
なので、ここには「3」と入力します。

検索方法

最後の引数、検索方法は基本的には「0」でいいと思います。
0(FALSE)にすると「完全一致」、1(TRUE)にすると「近似一致」を指定します。

もりの

どう違うの?

完全一致検索値と検索先の値が全く同じときだけ、値を持ってきてくれます。
検索値が田中太郎さんなら田中太郎さん、10なら10の時だけ反応します。
近似一致値が似ていれば、まぁこれのことだろうという感じで、一部一致していたり、近い値を持ってきてくれます。
検索値が田中太郎さんでも、田中次郎……近いからこれだろう!と反応したり、検索値が10でも、9……近いからこれこれ♪と反応します。

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

これで式は完成です!Enterキーを押して確定してください。

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

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

【Excel・エクセル】VLOOKUP関数の範囲を別シートに指定するやり方

範囲に指定する引っ張ってくるデータは、他のシートでも、別のエクセルファイルからでも大丈夫です。

下図は果物の売上表です。

例題の表

単価が空欄なので、単価表からVLOOKUP関数を使って単価を検索、抽出したいと思います。
そして、肝心の単価表ですが、「単価表」という別シートにありますね。

単価表は別シートにある
単価表は別シートにある

では、別シートから単価を持ってきてみましょう。

まず検索値ですが、売上表にも単価表にも共通して果物の名前がありますので、これを検索値にするのがよさそうですね。
検索値は「A2」とします。

検索値はA2
検索値はA2

次に範囲ですが、「単価表シート」から果物の名前をもとに単価を検索したいので、そのまま単価表シートをクリックしてシートを移動してください。

単価表シートに移動できた
単価表シートに移動できた

今回検索値にした果物の名前はA列、抽出したい単価はB列にあるので、範囲はA列からB列ですね。
単価表シートのまま、A列からB列を選択してください。

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

抽出したい単価は、範囲(A列からB列)の左から二番目にあるので、2
検索方法は0と入力してください。

無事、単価表シートから単価を抽出できました!

単価が引っ張ってこれた
違うシートから単価を検索して持ってこれた
もりの

別シートを参照していると、「シート名!」っていうのがつくんだね。

やまの

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

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

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

今回のポイント

  • あるデータを別のデータから引っ張ってくるのが、VLOOKUP関数
  • VLOOKUP(この値をもとに,この中の,左から何番目を持ってきて,似てるのはどうする?)
  • IF関数などと組み合わせて使うことも