command lab
  • TOP
  • BLOG
  • Excel

  • 【エクセル】VLOOKUP関数の使い方(表から検索値に対応する値を取得)

【エクセル】VLOOKUP関数の使い方(表から検索値に対応する値を取得)

VLOOKUP関数はExcelをビジネスで使う上で欠かせない関数です。しかし構成が複雑でExcel初心者には理解しにくくなっています。ここではそんなExcel初心者の方でもVLOOKUP関数の使い方をしっかり理解できるよう丁寧に解説していきます。

VLOOKUP関数で何ができるのか?

VLOOKUP関数を使うとどういったことが便利になるのでしょうか?例えば膨大な商品情報のマスタデータがあるとします。あなたは商品IDのリストだけを渡され、その商品IDの商品情報のデータをマスタデータを参照して入力しなければいけないとします。

VLOOKUP関数を知らない場合

VLOOKUP関数を知らない場合はおそらく下記のように商品IDをマスタデータ内で検索をかけて、ヒットした行から情報をひとつずつコピーして入力するのではないでしょうか?この方法でも作業は完了できますが、手間もかかり、コピーペーストでミスをする可能性もあります。

VLOOKUP関数を使う場合

ではVLOOKUP関数を使うとどうでしょうか?商品ID入力欄以外にVLOOKUP関数を入力することで商品情報を自動で取得し表示できます。

上記の例のようにVLOOKUP関数をうまく使用すれば、商品IDをキーとして手間をかけず正確にマスタデータから情報を取得することができます。

VLOOKUP関数を入力していれば、キーとなる商品IDを変更しても自動で更新される

一度VLOOKUP関数を入力しておけば検索値である商品IDを変更しても自動で更新されるため、商品IDから情報を取得するツールとして使用することができます。このようにVLOOKUP関数を業務効率化に非常に役に立つのでビジネスの世界では欠かせない関数となっています。

VLOOKUP関数の設定項目

VLOOKUP関数の設定項目は4つあります。関数では設定項目のことを引数(ひきすう)と呼び、引数は下記のように「,」区切りで設定していきます。

=VLOOKUP(検索値,範囲,列番号,検索の型

それぞれの引数について詳しく説明していきます。

引数1:検索値

検索値には、検索する値を指定します。ここで指定した値を2つ目の引数で指定する範囲の一番左の列から検索します。例えば商品IDなどの検索するキーとなる値をここに指定します。

引数2:範囲

検索値を含む範囲を指定します。一番左の列は1つ目の引数で探す値が含まれている列を指定し、検索値をキーに取得したい値がある列まで範囲を伸ばします。

引数3:列番号

一番左の列から何番目の列の情報を取得するかを指定します。例えば1を指定すると一番左の列の値を取得し、2を指定すると2列目の値を取得します。0を指定すると#VALUEエラーを返します。

引数4:検索の型

省略可能です。検索する際に完全一致した場合のみをマッチさせるか(FALSE)、完全一致する値が見つからなかったときはそれに近い値をマッチさせるか(TRUE)を指定します。一般的には完全一致以外の値をマッチさせると間違った結果を招いてしまう恐れがあるため完全一致のFALSEを指定します。よくわからなければFALSEを指定することをおすすめします。

VLOOKUP関数の使用手順

具体的な設定手順について説明します。この例ではVLOOKUP関数で商品マスタデータを参照し、商品IDのリストから各種情報を引っ張ってきて表示させていきます。

この手順では下記のようなVLOOKUP関数を作成します。

背景が薄いグレー部分がVLOOKUP関数が入力されている箇所です。A列の商品IDをキーに情報を取得します。

準備:検索値が最初の列にあるデータを用意する

範囲に指定するデータを用意します。注意点としてVLOOKUP関数で検索値を探す列は必ず一番最初の列である必要があります。この例では商品IDが検索値となるので、商品IDの列が最初の列にあります。

手順1:VLOOKUP関数を入れるセルを選択

VLOOKUP関数を入力するセルを選択します。欲しい情報を自動で表示したいセルにVLOOKUP関数を入力します。

手順2:「=VLOOKUP(」と入力

数式バーに「=VLOOKUP(」と入力します。入力途中に予測関数が表示されるのでVLOOKUP関数を選択し、TABキーを押下すると自動入力されるので非常に便利です。

手順3:検索値を入力

検索値をセル参照で指定します。セル参照したいセルをクリックすると自動で入力されます。またこの例では後でVLOOKUP関数をC列にコピーしても参照先がズレないようにA列を絶対参照として指定します。絶対参照に切り替えるショートカットキーである「F4」キーですので、F4」キーを3回押下して、A列のみ固定する絶対参照に切り替えます。

関連リンク
絶対参照とは?使い方をわかりやすく解説

手順4:範囲を入力

範囲をセル参照で指定します。セル参照したい範囲を選択することで自動で入力されます。ここでも上記同様、あとで数式をコピーしたあとに参照先がずれないように、F4」キーを1回押下して、列、行ともに絶対参照に切り替えます。

手順5:列番号を入力

列番号を入力します。ここでは商品名の情報を取得したいので、2列目を示す「2」を指定します。

手順6:検索の型を入力

検索の型を入力します。ここはFALSEを入力すると覚えておいてください。

手順7:数式をほかセルにコピー

他のセルにも数式を下記アニメーションのような手順でコピーします。まずは隣の値段の列に数式をコピーします。コピーした数式は列番号は2のままなので値段の情報を取得するよう列番号を3に修正します。その後に商品名と値段の数式を他の行にコピーします。

完成

商品IDを元に情報を取得することができました。

VLOOKUP関数の発展的な使用例

VLOOKUP関数をワイルドカードや他の関数と組み合わせることで発展的な使い方をすることができます。 ここでは4つの発展的な使用例について紹介させていただきます。

使用例1:ワイルドカードを使った検索例

ワイルドカードを使うことで、正確に検索値を入力せずとも、指定文字が含まれている最初の値にマッチさせることができます。この例では検索値に「”*”&E3&”*”」と指定することで、E3に入力した文字列と*が結合し「*クレ*」となります。これは「クレ」という文字列が含まれる文字列を表し、結果として「クレヨン」がマッチし、その対応値である400が返却されています。

=VLOOKUP("*"&E3&"*",B3:C12,2,FALSE)
結果 → 400

関連リンク
ワイルドカードの使い方についてわかりやすく解説

使用例2:MATCH関数を併用して列番号制御

この例では売上が複数月に渡って記載されているとき、商品名と売上月を指定して、対応する値を取得します。VLOOKUP関数の列番号の箇所にMATCH関数を入れ子で指定し、H3に設定されている売上月と同様の見出しの位置を取得し、列番号として指定することで指定した売上月の売上を取得しています。

=VLOOKUP(H2,B3:E12,MATCH(H3,B2:E2),FALSE)
結果 → 848392

関連リンク
MATCH関数の使い方(範囲内の任意の値の位置を取得)

使用例3:検索値が見つからない場合の返却値を設定する例

通常VLOOKUP関数は検索値が見つからない場合、#N/Aエラーを返しますが、#N/Aエラーを自由に制御したい場合はIFNA関数を併用します。VLOOKUP関数の数式をIFNA関数で囲い、IFNA関数の第2引数にエラー時の返却値を指定することで、制御可能です。

=IFNA(VLOOKUP(F3,B3:D12,2,FALSE),"Not Found")
結果 → Not Found

使用例4:近似値を取得する例

VLOOKUP関数の「検索の型」は基本的にFALSEを指定し、完全一致モードとすることをおすすめしますが、TRUEを設定して近似値検索が好ましい場面もあります。この例では値段の範囲によって割引金額を指定しており、F列-G列にある割引表をもとにD列の割引金額をVLOOKUP関数で出しています。

=VLOOKUP(C3,$F$3:$G$7,2,TRUE)
結果 → ¥5

VLOOKUP関数の弱点は検索値から右側の値しか取得できないこと

VLOOKUP関数の性質として、検索値から右側の値しか取得できません。検索値から左側の値を取得した場合はMATCH関数とINDEX関数を組み合わせて使う方法かExcel365以降のバージョンであればXLOOKUP関数を使用します。

関連リンク
INDEX関数とMATCH関数で対応値を検索する方法
XLOOKUP関数の使い方を分かりやすく解説

VLOOKUP関数の要点

  • VLOOKUP関数の検索値は必ず指定範囲内の一番右である必要があります。
  • 左側の値を取得したい場合はMATCH関数とINDEX関数を組み合わせて使うXLOOKUP関数を使用します。
  • 第4引数の検索の型は、値が完全一致する必要があるかどうかを制御します。デフォルトはTRUEで近似値を許可します。
  • 検索の型をTRUEに設定した際の挙動として、検索値が見つからない場合、検索値より少なくて一番近い値とマッチします。
  • 近似値モードで検索する場合は検索する列を昇順にソートをしなければ、正しく検索できません。

関連リンク
VLOOKUP関数の列番号とは?自動で列番号を変更する方法も
VLOOKUP関数で別シートから参照する方法についてわかりやすく解説
VLOOKUP関数で「0」やエラーを空白にする方法についてわかりやすく解説
VLOOKUP関数を複数条件で検索する方法についてわかりやすく解説
VLOOKUP関数をVBAで使用する方法
HLOOKUPの使い方をわかりやすく解説

おすすめの解説動画

VLOOKUP関数についてわかりやすく解説している動画です。丁寧に解説しているのでおすすめの動画です。

ショートカットキーの練習できます

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトで定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。

下のスタートボタンを押して練習モードに入ります。
STARTSTOP
※入力モード切り替え ctrl + shift + alt + space
PRE
NEXT
ガイドモード
リピートモード

※推奨ブラウザ Google Chrome

PUSH ENTER

関連記事