【VLOOKUP関数の使い方】Excelで条件に一致するセルを検索して指定した列の値を返す方法

Instagramでも公開しています!

VLOOKUP関数は、Excelの関数の中でも頻繁に使用される関数の一つです。

データ分析やデータ管理などの多くのファイルで活用されるので、Excelを使用する上では必ず習得しておくべき関数と言っても大げさではありません。

excel-fighter-ichigo

でも、Excel初心者の私にはVLOOKUP関数って難しいです…

excel-fighter-lemo

たしかに初めて使うときは難しく感じますよね。でもサンプルで2~3回練習すれば関数の構造や動作を理解できます。一緒に操作してみましょう!

本記事では、Excel初心者の方のために宇宙一わかりやすく!を目標にVLOOKUP関数を解説しています。

Excel初心者のいちごさんと一緒に操作をして、VLOOKUP関数をマスターしましょう。

VLOOKUPってどんな関数?

VlOOKUP(検索値,範囲,列番号,[検索方法])

VLOOKUPとは「条件に一致するセルを縦方向に検索して指定した列の値を返す」ことができる関数です。

VLOOKUPは検索/行列に分類されており、関連する関数として XLOOKUP(Office365のみ搭載)、HLOOKUP、LOOKUPがあります。

関数名VLOOKUP
読みブイルックアップ
分類検索/行列
書式VLOOKUP(検索値,範囲,列番号,[検索方法])
役割探したい値(検索値)を検索対象の表(範囲)の1列目で下方向に検索し、見つけたセルと同じ行の指定した列(列番号)のセルのデータを抽出する

今回は下図のように、VLOOKUP関数を使って表1の果物の単価を表2から抽出します。

VLOOKUP関数を使って果物の単価を抽出する[excel-fighter.net]
VLOOKUP関数を使って果物の単価を抽出する

ここでは、VLOOKUP関数の動作を理解するために、必要最低限のデータのみの表を使用します。VLOOKUP関数をある程度理解できたら、複雑な表でVLOOKUP関数を活用しましょう。

VLOOKUP関数で使う表を作成する

VLOOKUP関数を入力するための[表1]と、VLOOKUP関数が検索する範囲の[表2]の二つの表を作成します。

VLOOKUP関数で使う表を作成する[excel-fighter.net]
VLOOKUP関数で使う表を作成する

表2の商品名や単価は、好きなように入力してもかまいません。表1の商品名は、表2の商品名と同じデータをランダムに入力しましょう。

また、上図では見た目でわかりやすくするために罫線や色などの書式を設定していますが、VLOOKUP関数で必要なのはデータだけです。書式は設定せず、下図のように文字だけのシンプルな表でも大丈夫です。

表は書式を設定しなくてもOK[excel-fighter.net]
表は書式を設定しなくてもOK

ただし、セル番地は上図にあわせて表を作成してください。VLOOKUP関数に限らず、Excelの関数はセル番地を参照するため、番地がずれると理解しづらくなります。

VLOOKUP関数を入力する

表を作成したら、続いてVLOOKUP関数を入力していきましょう。

Excelで関数を入力する方法は二つあります。一つ目は[関数の挿入]ダイアログボックスで入力する方法、二つ目はセルに直接入力する方法です。

今回は二つ目のセルに直接入力する方法を実践していきます。

数式をセルに入力する際は、必ず半角英数(直接入力モード)で入力しましょう。日本語入力(変換入力モード)で入力すると、半角英数に変換する必要があり手間と時間がかかります。
1. B4をクリックして選択し、「=vl」と入力

B4セルにVLOOKUP関数を入力していきます。B4セルを選択した後、「=vl」と入力しましょう。

1. B4をクリックして選択し、「=vl」と入力[excel-fighter.net]
VLOOKUPを候補一覧に表示する
数式をセルに直接入力する際は、=(イコール)記号からはじめます。
2. [VLOOKUP]が表示されるので[Tab]キーで選択

「=vl」と入力すると、すぐ下に自動的に[VLOOKUP]が表示されるので、[Tab]キーを1回押して選択します。

VLOOKUPを選択する[excel-fighter.net]
VLOOKUPを選択する
セルにイコールから始まる関数名を入力すると、入力途中でも候補となる関数が表示されます。候補が一つの場合はそのまま[Tab]キーを押下、複数の候補が表示される場合は[↓]キーで目当ての関数に移動し[Tab]キーを押下すると、選択することができます。
[Tab]キー以外では、目当ての関数をダブルクリックすることでも関数を選択することができます。
3. 【検索値を指定】A4をクリックした後、[,(カンマ)]を入力

VLOOKUPの1番目の引数[検索値]を指定します。ここでは、「みかん」を入力したA4セルが検索値になります。

A4をクリックすると自動的に[A4]が表示されます。続けて区切り文字の[,(カンマ)]を入力します。

【検索値】を指定する[excel-fighter.net]
[検索値]を指定する
4. 【範囲を指定】E4からD7までドラッグで選択して[F4]キーを1回押した後、[,(カンマ)]を入力

VLOOKUPの2番目の引数[範囲]を指定します。ここでは、表2でデータが入力されているD4からE7が範囲になります。

ちなみに今回の表の場合、D4セルは入力帯と重なっているためドラッグの起点にできません。

【範囲】を指定する[excel-fighter.net]
【範囲】を指定する

このようなときは、見えているセルをドラッグの起点にしましょう。E4からD7へドラッグすると[D4:E7]が反映されます。

【範囲】を指定する[excel-fighter.net]
【範囲】を指定する

[D4:E7]が反映されたら[F4]キーを1回押します。[$D$4:$E$7]に変更されればOKです。続けて区切り文字の[,(カンマ)]を入力します。

【範囲】を指定する[excel-fighter.net]
【範囲】を指定する
[F4]キーを押すとセル番地に「$(ドルマーク)」が付与されます。$が付与されたセル番地は「絶対参照」という参照方法に変更され、数式をコピーしても固定することができます。
5. 【列番号を指定】「2」を入力した後、[,(カンマ)]を入力

VLOOKUPの3番目の引数[列番号]を指定します。ここでは、表2の単価を抽出したいので、範囲の2列目になります。

「2」を入力した後、区切り文字の[,(カンマ)]を入力します。

【列番号】を指定する[excel-fighter.net]
【列番号】を指定する
6. 【検索方法を指定】「0」を入力した後、[,(カンマ)]を入力

VLOOKUPの4番目の引数[検索方法]を指定します。

検索方法は「完全に一致する値を検索する」か「近い値(近似値)を検索する」かの二つから選択できます。

検索
方法
完全一致近似値一致
入力
内容
0もしくはFALSE1もしくはTRUEもしくは指定しない
結果完全に一致する値を検索する近似値を検索する

今回は完全一致で検索したいので「0」を入力します。すべての引数の入力を終えたので、[ ) 閉じカッコ]を入力します。

【検索方法】を指定する[excel-fighter.net]
【検索方法】を指定する
7. [Enter]キーで確定する

[Enter]キーで数式を確定し、入力完了です。

B4セルにはVLOOKUPで抽出した結果の「300」が返り、数式バーには入力した数式が表示されます。

VLOOKUPの入力完了[excel-fighter.net]
VLOOKUPの入力完了
8. フィルハンドルをダブルクリックして数式をコピーする

B4に入力したVLOOKUP関数をB7までコピーします。

左右どちらかの隣り合わせる列にデータが入っている場合は、そのデータがある行までダブルクリックするだけでコピーすることができます。

B4セルを選択して、右下のフィルハンドルをダブルクリックしてください。

数式をコピーする[excel-fighter.net]
数式をコピーする
隣り合わせる列にデータが入っていない場合は、ダブルクリックではコピーすることができません。そのようなときはフィルハンドルをコピーしたいセルまでドラッグします。
9. 数式のコピーが完了

B4セルのフィルハンドルをダブルクリックした後、B7セルまで数式がコピーされたことを確認してください。

数式のコピーが完了[excel-fighter.net]
数式のコピーが完了
excel-fighter-ichigo

わぁ!びっくり!すごく簡単に数式をつくることができました!

excel-fighter-lemo

そうでしょ?VLOOKUPって意外と簡単なんです。続いて、もっと簡単に関数を理解するために「数式を文章化」していきましょう。

VLOOKUP関数を文章化して理解する

完成したVLOOKUP関数を文章化すると、より分かりやすくなります。

VLOOKUP関数を文章化して理解する[excel-fighter.net]
VLOOKUP関数を文章化して理解する
VlOOKUP(検索値,範囲,列番号,[検索方法])
この数式を文章化すると…
B4のVLOOKUPは、みかん(A4)範囲(D4からE7)の1列目(D列)から完全一致で検索して、見つけたセルと同じ行の2列目を返す。

「範囲の1列目」はVLOOKUP関数の内部設定なので、数式内で指定する必要はありません。VLOOKUP関数では常に「範囲の1列目を縦方向に検索する」ことを覚えておきましょう。

Excelで頻繁に使用される関数は、上記のように「日本語文章」に変換するとわかりやすくなります。「この関数は〇〇セルを〇〇して~」という風に脳内で文章化しながら数式を作成してみましょう。

VLOOKUP関数の引数を理解する

VLOOKUP関数は、[検索値]、[範囲]、[列番号]、[[検索方法]]の4つの引数で構成されます。

VlOOKUP(検索値,範囲,列番号,[検索方法])
引数説明
検索値探す値。セル番地(A4)やダブルクォーテーションで囲んだ文字列(“みかん”)を指定する。
B4=VLOOKUP(A4,$D$4:$E$7,2,0)
B4=VLOOKUP(“みかん”,$D$4:$E$7,2,0)
範囲検索する範囲となる表。範囲の1列目に検索値を含む表を指定する。通常、検索対象となる範囲は絶対参照(セル番地にドルマークをつける)で固定する。相対参照(セルにドルマークをつけない)のまま数式をコピーすると、範囲がずれるので正しい結果を返せない可能性がある。
B4=VLOOKUP(A4,$D$4:$E$7,2,0)
列番号検索範囲の何列目を返すかを指定する。範囲の先頭列(左端列)から数えて何列目になるかを数字で表す。列番号を特定のセルに入力しておく場合は、そのセル番地を指定することもできる。
B4=VLOOKUP(A4,$D$4:$E$7,2,0)
B4=VLOOKUP(A4,$D$4:$E$7,セル番地,0)
[検索方法]完全に一致する値を検索するか、近い値を検索するか指定する。完全一致なら[0か”FALSE”]、近似値一致なら[1か”TRUE”]を指定する。この引数は省略することもでき、省略した場合は近似値一致を選択したことになる。
B4=VLOOKUP(A4,$D$4:$E$7,2,0)
B4=VLOOKUP(A4,$D$4:$E$7,2,“FALSE”)
B4=VLOOKUP(A4,$D$4:$E$7,2)

上述で紹介したVLOOKUP関数の引数の役割は、VLOOKUP関数を何度も入力していくうちに、自然と覚えています。

それまでは、よくわからなくなったらこの記事を見返して参考にしてください。

VLOOKUP関数の入力で気を付けたいこと

VLOOKUP関数を作成する際の注意点をまとめました。

VLOOKUP関数の結果でエラーが返ったり、結果は返るものの間違っていたりする場合の参考にしてください。

VLOOKUP関数の結果が「#N/A」になる
ここに気を付けよう
  • 完全一致検索を指定している場合は、検索値と全く同じ値が検索範囲の1列目にあるか
  • 検索範囲が正しく入力されているか
  • 近似値一致を指定している場合は、検索範囲の表を1列目を最優先キーで降順/昇順に並べ替えているか
VLOOKUP関数の結果が正しくない/間違っている
ここに気を付けよう
  • 完全一致検索を指定している場合は、検索値と全く同じ値が検索範囲の1列目にあるかあるか
  • 検索範囲が正しく入力されているか
  • 近似値一致を指定している場合は、検索範囲を1列目を最優先キーで降順/昇順に並べ替えているか
  • 完全一致検索を指定しないといけないのに、近似値一致を指定していないか、またはその逆はないか
excel-fighter-ichigo

よくわかりました!宇宙一わかりやすかったです!

excel-fighter-lemo

よかったです!何度か繰り返し練習するとスムーズに関数を組むことができるようになるので、お時間を作って練習してみてください。

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください