VLOOKUP（ブイルックアップ）関数はExcelをビジネスで使う上で欠かせない関数です。しかし構成が複雑でExcel初心者には理解しにくくなっています。ここではそんなExcel初心者の方でも仕事で使えるように表から検索値に対応する値を取得するVLOOKUP関数の使い方をしっかり丁寧に解説していきます。
またページ下部ではVLOOKUP関数に関する様々なテクニックについてまとめています。
VLOOKUP関数で何ができるのか？
VLOOKUP関数を使うとどういったことが便利になるのでしょうか？例えば膨大な商品情報のマスタデータがあるとします。あなたは商品IDのリストだけを渡され、その商品IDの商品情報のデータをマスタデータを参照して入力しなければいけないとします。
VLOOKUP関数を知らない場合
VLOOKUP関数を知らない場合はおそらく下記のように商品IDをマスタデータ内で検索をかけて、ヒットした行から情報をひとつずつコピーして入力するのではないでしょうか？この方法でも作業は完了できますが、手間もかかり、コピーペーストでミスをする可能性もあります。
VLOOKUP関数を使う場合
ではVLOOKUP関数を使うとどうでしょうか？商品ID入力欄以外にVLOOKUP関数を入力することで商品情報を自動的に取得し表示できます。この関数のおかげで圧倒的に簡単に必要な値を取得することができます。
上記の例のようにVLOOKUP関数をうまく使用すれば、商品IDをキーとして手間をかけず正確にマスタデータから情報を取得することができます。
VLOOKUP関数を入力していれば、キーとなる商品IDを変更しても自動で更新される
一度VLOOKUP関数を入力しておけば検索値である商品IDを変更しても自動で更新されるため、商品IDから情報を取得するツールとして使用することができます。このようにVLOOKUP関数は業務効率化に非常に役に立つのでビジネスの世界では欠かせない関数となっています。
VLOOKUP関数の設定項目（書式）
VLOOKUP関数の設定項目は4つあります。関数では設定項目のことを引数（ひきすう）と呼び、引数は下記のように「,」区切りで設定していきます。
それぞれの引数について詳しく説明していきます。
引数１：検索値
検索値には、検索する値を指定します。ここで指定した値を２つ目の引数で指定する範囲の一番左の列から検索します。例えば商品IDなどの検索するキーとなる値をここに指定します。
引数２：範囲
検索値を含む範囲を指定します。範囲内の一番左端の列は引数１で指定した検索値が含まれている列を指定し、検索値をキーに取得したい値がある列まで範囲を伸ばします。
引数３：列番号
検索値に該当した際に一番左の列から何番目の列の情報を取得するかを指定します。例えば1を指定すると一番左の列の値を取得し、2を指定すると2列目の値を取得します。0を指定すると#VALUEエラーを返します。負の数値も指定できないので必ず1以上の絶対値を指定します。
引数４：検索の型
省略可能です。検索する際に完全一致した場合のみをマッチさせるか（FALSE）、完全一致する値が見つからなかった時はそれに近い値（検索値を超えない最大値）をマッチさせるか（TRUE）を指定します。一般的には完全一致以外の値をマッチさせると間違った結果を招いてしまう恐れがあるため完全一致のFALSEを指定します。よくわからなければFALSEを指定することをおすすめします。
VLOOKUP関数の使用手順
具体的な設定手順について説明します。この例ではVLOOKUP関数で商品マスタデータを参照し、商品IDのリストから各種情報を引っ張ってきて表示させていきます。
この手順では下記のようなVLOOKUP関数を作成します。
背景が薄いグレー部分がVLOOKUP関数が入力されている箇所です。A列の商品IDをキーに情報を取得します。サンプルデータを用意しているので、ダウンロードして実際にVLOOKUP関数を入力してみましょう。
準備：検索値が最初の列にあるデータを用意する
範囲に指定するデータを用意します。注意点としてVLOOKUP関数で検索値を探す列は対応する値がある列より必ず左側の列である必要があります。この例では商品IDが検索値となるので、商品IDの列が取得したい列より左側の列にあります。
手順１：VLOOKUP関数を入れるセルを選択
VLOOKUP関数を入力するセルを選択します。欲しい情報を自動で表示したいセルにVLOOKUP関数を入力します。
手順２：「=VLOOKUP(」と入力
数式バーに「=VLOOKUP(」と入力します。入力途中に予測関数が表示されるのでVLOOKUP関数を選択し、TABキーを押下すると自動入力されるので非常に便利です。
手順３：検索値を入力
検索値をセル参照で指定します。セル参照したいセルをクリックすると自動で入力されます。またこの例では後でVLOOKUP関数をC列にコピーしても参照先がズレないようにA列を絶対参照として指定します。絶対参照に切り替えるショートカットキーである「F4」キーですので、F4」キーを3回押下して、A列のみ固定する絶対参照に切り替えます。
関連リンク
絶対参照とは？使い方をわかりやすく解説
手順４：範囲を入力
範囲をセル参照で指定します。セル参照したい範囲を選択することで自動で入力されます。ここでも上記同様、あとで数式をコピーしたあとに参照先がずれないように、F4」キーを1回押下して、列、行ともに絶対参照に切り替えます。
手順５：列番号を入力
列番号を入力します。ここでは商品名の情報を取得したいので、2列目を示す「2」を指定します。
手順６：検索の型を入力
検索の型を入力します。ここはFALSEを入力すると覚えておいてください。
手順７：数式をほかセルにコピー
他のセルにも数式を下記アニメーションのような手順でコピーします。まずは隣の値段の列に数式をコピーします。コピーした数式は列番号は2のままなので値段の情報を取得するよう列番号を3に修正します。その後に商品名と値段の数式を他の行全体にコピーします。
完成
商品IDを元に情報を取得することができました。
VLOOKUP関数の要点
- VLOOKUP関数の検索値は必ず指定範囲内の一番右である必要があります。
- 左側の値を取得したい場合はMATCH関数とINDEX関数を組み合わせて使うかXLOOKUP関数を使用します。
- 第４引数の検索の型は、値が完全一致する必要があるかどうかを制御します。デフォルトはTRUEで近似値を許可します。
- 検索の型をTRUEに設定した際の挙動として、検索値が見つからない場合、検索値より少なくて一番近い値とマッチします。
- 近似値モードで検索する場合は検索する列を昇順にソートをしなければ、正しく検索できません。
VLOOKUP関数の様々なテクニック
VLOOKUP関数を使いこなす上で様々なテクニックをご紹介します。
複数条件で検索したい時のテクニック
VLOOKUP関数は本来、条件を複数指定することはできませんが、列を1つ追加してワードを結合することで簡単に複数条件で検索することは可能です。また少し難易度は高いですが、INDEX関数とMATCH関数を組み合わせて作成する方法では列を追加せずとも複数条件で検索できるようにすることも可能です。
VLOOKUP関数で複数条件を設定する方法
２つの条件を結合する列を追加することで複数条件での検索を実現する方法です。この例では形と色の２つの条件で合致した値を返しています。書式と使用例は以下の通り。
書式
=VLOOKUP(検索値A&検索値B,検索範囲,列番号,FALSE)使用例
=VLOOKUP(B3&C3,B6:E17,4,FALSE)
作業列を追加せずに複数条件で検索する方法（INDEX関数とMATCH関数）
INDEX関数とMATCH関数を組み合わせて、VLOOKUPのような機能を実現し、さらに複数条件で検索する方法です。この方法であれば、上記のVLOOKUPでの複数条件検索のように列を追加しなくても複数検索が実現できます。注意点としてはCTRL + SHIFT + ENTERで数式を決定し数式を「｛｝」で囲む必要があります。「{}」は直接入力するエラーになるので、必ずCTRL + SHIFT + ENTERで対応してください。
書式
{=INDEX(データ範囲,MATCH(検索値A&検索値B,検索範囲A&検索範囲B,0),列番号)}使用例
{=INDEX(B6:D17,MATCH(B3&C3,B6:B17&C6:C17,0),3)}詳しい手順やその他の応用テクニックは下記リンクを参照してください。
返却値をエラーや０でなく空白にするテクニック
VLOOKUP関数は検索値が見つからない場合は#N/Aエラー、返却値が空欄の場合は「0」を返します。返却値を空欄を返すにはVLOOKUP関数の数式に「&””」と追加し文字列に変換することで「0」で返さないようにし、さらにIFERROR関数で囲むことでエラーを空白に変換することができます。書式や使用例は下記です。
書式
=IFERROR(VLOOKUP(検索値,範囲,列番号,FALSE)&"","")使用例
=IFERROR(VLOOKUP(B3,B6:D15,2,FALSE)&"","")詳しい方法については下記のリンクで解説しています。
数式コピーの際に列番号を可変（ずらす）させる方法
複数のセルにVLOOKUP関数をコピーペーストすると、列番号を一つ一つ変更する必要がでてくる場合があります。そのような時は列番号部分にCOLUMN関数を使うとコピーペースト時に自動で変動してくれるので便利です。COLUMNS関数とは指定した範囲の列数を返す関数で、範囲の始点を絶対参照にすることによって、始点から何列目にあるかを自動で取得することができます。
書式
=VLOOKUP(検索値,範囲,COLUMNS(列番号分の範囲),FALSE)使用例
=VLOOKUP($A3,$G$3:$K$18,COLUMNS($G$2:H2),FALSE)詳しい作成方法については下記リンクで解説しています。
別シートを参照する方法
VLOOKUP関数で別シートを参照したい場合は数式入力中にシートを切り替えてセルを選択するだけ設定可能です。手打ちで設定したい場合は参照文字列を「Sheet1!A1」のように「シート名 ＋ ! ＋ セル番号」の形にすることで参照できます。詳しい手順や応用テクニックなどは下記のリンクに記載しています。
書式
=VLOOKUP(検索値,シート名!データ範囲,列番号,FALSE)使用例
=VLOOKUP(A2,マスタデータ!$A$2:$D$51,2,FALSE)
IF関数と組み合わせて使う方法
IF関数とVLOOKUP関数を組み合わせると様々な機能を持った数式を作成することができます。例えば上記の画像では在庫が0だった場合は「在庫なし」と表示し、0以外の場合はその在庫数を返す機能を持たせています。
書式
=IF(VLOOKUP(検索値,範囲,列番号,FALSE)=0,”在庫なし”,VLOOKUP(検索値,範囲,列番号,FALSE))使用例
=IF(VLOOKUP(H2,B3:E12,4,FALSE)=0,”在庫なし”,VLOOKUP(H2,B3:E12,4,FALSE))その他の作成例についても下記で詳しく解説しているので、確認してみてください。
ワイルドカードを活用して部分一致検索（〜を含む）をする方法
VLOOKUP関数では検索値の部分を「”*”&A1&”*”」のようにワイルドカードという特殊な文字列と組み合わせて指定することで部分一致検索、つまり「〜を含む」検索をすることができます。
書式
=VLOOKUP("*"&検索値&"*",範囲,列番号,FALSE)使用例
=VLOOKUP("*"&$G$2&"*",$B$3:$D$12,1,FALSE)ワイルドカードとは
「〜を含む」検索を理解するにはワイルドカードの理解が必要です。
ワイルドカードとは関数に指定する検索値などに「〜を含む」や「〜の文字から始まる」「〜の文字で終わる」などの条件を加えることができる特殊な文字列のことを指します。 ワイルドカードの文字列は「*」（アスタリスク）と「?」（クエスチョン）を指し、これらを組み合わせて条件を指定します。例えば「*ハサミ*」のようにアスタリスクで文字列を囲むと「ハサミ」を含む文字列という意味となります。セル参照と組み合わせる場合は「”*”&A1&”*”」というようにアスタリスクを「”」で囲み、セル参照と「&」でつなげます。
| ワイルドカード | 説明 |
|---|---|
| * | 0文字以上の任意の文字列を表す |
| ? | 1文字の任意の文字列を表す |
設定方法については下記リンクで詳しく解説しています。
特定条件の合計値を出したい場合の方法
特定条件の合計値を出したいときにVLOOKUP関数を使おうとする方が多くいますが、この場合はVLOOKUP関数ではなく、SUMIF関数が正解です。
SUMIF関数の書式
SUMIF関数の基本の書式は下記となります。
- 範囲：検索対象または値を求める対象となるセル範囲を指定します。
- 検索条件：集計するための条件を指定します。必ず「”」で囲みます。
- 合計範囲：実際に計算の対象となるセル範囲を指定します。合計範囲を省略すると最初に指定した範囲内で条件を満たす数値が合計されます。
下記ではSUMIF関数を使って特定条件の合計値を出す手順について詳しく解説しています。
#N/Aエラーが起きた時の対処方法
本来VLOOKUP関数において#N/Aエラーは検索値が見つからない場合に発生するエラーですが、検索値があるはずなのにエラーが返される場合のよくある理由は下記のとおり
- 検索値に全角、半角が正しく入力されていない
- 余計な空白が入っている
- 検索値の書式設定が文字列と数値で異なる
- セル参照が正しくない
- 検索の型が「TRUE」もしくは「1」が設定されている
下記のリンクでそれぞれのエラーの原因と解決方法について解説しています。
左側（右から左に）の値を取得したい場合の方法
VLOOKUP関数は検索値に対する対応値は必ず右側になければ取得できないという弱点がありますが、別の方法で左側の値を取得することができます。方法は2つあり「INDEX関数とMATCH関数を組み合わせて使う方法」と「XLOOKUP関数を使う方法」があります。新しく追加されたXLOOKUP関数はMicrosoftの「Office 365」もしくは「Office 2021」以降のバージョンで使えますが、それ以前のバージョンだと「INDEX関数とMATCH関数を組み合わせて使う方法」に限られます。
古いExcelバージョンでも使える方法：INDEX関数とMATCH関数で検索
INDEX関数とMATCH関数を組みわせて使うことでVLOOKUP関数と同じ機能を実現できるほか、検索値に対して左側の値も取得できます。設定方法は少し複雑ですが、慣れれば問題なく設定できるはずです。
書式
=INDEX(対応範囲,MATCH(検索値,検索範囲,0))使用例
=INDEX(C3:C12,MATCH(F3,B3:B12,0))
「Office 365」or「Office 2021」以降で使える方法：XLOOKUP関数で検索
Microsoftの「Office 365」もしくは「Office 2021」以降のバージョンではVLOOKUP関数の後継関数である「XLOOKUP」が使えます。この関数はINDEX関数とMATCH関数の組み合わせることで出来る汎用性も兼ね備えている非常に便利な関数です。XLOOKUP関数のポイントは下記の通り
XLOOKUP関数のポイント
- 検索値を含む列を左端にしなくても良い
- 一致モードのデフォルトが完全一致
- 戻り値を一気に表示可能
- 垂直配列と水平配列の両方で機能
- 逆検索を実行可能
XLOOKUP関数の書式
XLOOKUP関数には設定項目が6つありますがそのうち3つは省略可能で「検索値」、「検索範囲」、「戻り範囲」の3つ設定すれば機能します。
XLOOKUP関数についての詳しい解説は下記リンクを参照してください。
VLOOKUP関数をVBAで使用する方法
VBAでVLOOKUP関数を使用したい場合の方法について解説しています。VBAではApplication.WorksheetFunctionを使用してVLOOKUP関数を使うことができます。下記で詳しく解説しています。
「#NAME?」と表示された時の対処方法
VLOOKUP関数を入力したセルに「#NAME?」というエラーが表示されたら、関数名の入力ミスが疑われます。正しいスペルかどうか確認しましょう。下記リンクでは、関数を簡単に正しく入力する方法を詳しく解説しています。
MATCH関数を併用して列番号制御する方法
この例では売上が複数月に渡って記載されているとき、商品名と売上月を指定して、対応する値を取得します。VLOOKUP関数の列番号の箇所にMATCH関数を入れ子で指定し、H3に設定されている売上月と同様の見出しの位置を取得し、列番号として指定することで指定した売上月の売上を取得しています。
=VLOOKUP(H2,B3:E12,MATCH(H3,B2:E2),FALSE)
結果 → 848392関連リンク
MATCH関数の使い方(範囲内の任意の値の位置を取得)
検索値が見つからない場合の返却値を設定する例
通常VLOOKUP関数は検索値が見つからない場合、#N/Aエラーを返しますが、#N/Aエラーを自由に制御したい場合はIFNA関数を併用します。VLOOKUP関数の数式をIFNA関数で囲い、IFNA関数の第2引数にエラー時の返却値を指定することで、制御可能です。
=IFNA(VLOOKUP(F3,B3:D12,2,FALSE),"Not Found")
結果 → Not Found近似値を取得する方法（検索の方をTRUEに設定）
VLOOKUP関数の「検索の型」は基本的にFALSEを指定し、完全一致モードとすることをおすすめしますが、TRUEを設定して近似値検索が好ましい場面もあります。この例では値段の範囲によって割引金額を指定しており、F列-G列にある割引表をもとにD列の割引金額をVLOOKUP関数で出しています。
=VLOOKUP(C3,$F$3:$G$7,2,TRUE)
結果 → ¥5おすすめの解説動画
VLOOKUP関数についてわかりやすく解説している動画です。丁寧に解説しているのでおすすめの動画です。
ショートカットキーの練習できます
当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。
※推奨ブラウザ Google Chrome
