Excel(エクセル)のVLOOKUP関数(ブイルックアップ)は表を縦方向に検索して検索値を抽出する関数です。
膨大なデータを管理する人にとっては、間違いなく利用頻度大の便利関数となりますので、ぜひこの機会に習得してください。
この記事では、VLOOKUP関数を初めて学ぶ人向けに、VLOOKUP関数の使い方の手順から、エラーが発生した場合の対処方法など幅広く紹介します。
VLOOKUP関数(ブイルックアップ)とは？
VLOOKUP関数は、表を縦方向に検索して情報を抽出する関数です。
VLOOKUPの「V」は縦を意味する「Vertical」の頭文字で、LOOKUPは英語で「探す」という意味です。
指定した検索条件に『当てはまる値』を、指定した検索範囲から調べて、指定した位置から抽出することが出来る関数です。
もう少しかみ砕いて、具体的な例で説明すると、
商品番号が『２５』の商品を、指定した商品リスト表から調べて、表の左から３列目の『商品名』を抽出することが出来る関数です。
この説明では分かりづらい方は、この後に紹介するVLOOKUP関数の使い方を見れば感覚的に理解出来るようになります。
まずは、いったん『表から条件に合った値を抽出することが出来る』と、思っていただいて結構です。
VLOOKUP関数の構文と引数
VLOOKUP関数の構文と引数を紹介します。
構文とは、関数によって利用する引数の数や種類のことを言います。関数の書式と思いましょう。
引数とは、関数を使用するために指定するセル番号や数値、文字などのことを言います。
VLOOKUP関数の構文
VLOOKUP関数の構文は下のとおりで、４つの引数を指定する必要があります。
「検索値」「検索範囲」「列番号」「検索方法」の４つを指定することで利用出来ます。
＝VLOOKUP(検索値,検索範囲,列番号,[検索方法]）
VLOOKUP関数の４つの引数
検索値
数値やセル範囲、文字列を指定できます。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：”薬”、B4）
検索範囲
検索する範囲を指定します。検索範囲を固定したければ、絶対参照の$(ドル）を付けます。（例：B4:E15、$B$4:$E$15）
列番号
検索範囲の一番左から何列目の値を抽出するかを数値で指定します。（例：３）
検索方法
「FALSE(もしくは0）」か、「TRUE(もしくは1）」のどちらかを選びます。（例：FALSE、TRUE、0、1） ※詳細は以下の参照
VLOOKUP関数の検索方法の「FALSE」と「TRUE」の違い
４つ目の引数「検索方法」の「FALSE」と「TRUE」の違いを説明します。
結論からお伝えすると、VLOOKUP関数を初めて触る方は「FALSE」の一択で覚えましょう。
FALSE
検索方法「FALSE」を指定した場合、「検索値」と完全一致する値が検索され、「検索値」が見つからない場合は、エラー値「#N/A」が表示されます。
TRUE
検索方法「TRUE」を指定した場合、「検索値」と完全一致する値が検索され、「検索値」が見つからない場合には、近似値が表示（「検索値」未満で最も大きい値）されます。
「FALSE」と「TRUE」の違いは、検索値が検索範囲に無かった場合のアウトプットが異なります。そのため、検索値が検索範囲に存在していた場合は、FALSEでもTRUEでも計算結果は同じです。
実務的に「TRUE」で利用する場面は少ないため、「FALSE」を使いましょう。「TRUE」の使い方は下の見出しで紹介していますので興味のある方は確認してみてください。
VLOOKUP関数の使い方と手順を紹介
それでは下の表を使って、VLOOKUP関数で「商品番号」が「25」の「商品名」を抽出する方法を手順で説明します。
手順１．VLOOKUP関数を挿入
H4セルにVLOOKUP関数を挿入するため、「＝VLOOKUP(」と入力します。
＝VLOOKUP(
手順２．VLOOKUP関数の第１引数を入力
続いて第１引数「検索値」を設定するため、商品番号「25」が入力されているG3セルをクリックし、「,」(カンマ)を入力します。
＝VLOOKUP(G3,
手順３．VLOOKUP関数の第２引数を入力
続いて第２引数「検索範囲」を設定するため、検索値である「商品番号」列から右側のセル範囲をドラッグで選択します。選択し終えたら、「,」(カンマ)を入力します。
＝VLOOKUP(G3,B3:E8,
VLOOKUP関数を他のセルにもコピペで利用する場合は、必要に応じてファンクションキー「F4」を使って絶対参照の「$」を付けておきましょう。
=VLOOKUP(G3,$B$3:$E$8,
絶対参照が分からない方は、以下の記事を参考にしてみてください。
手順４．VLOOKUP関数の第３引数を入力
続いて第３引数「列番号」を設定します。手順３で選択したセル範囲の左側から３列目が「商品名」列なので、「3」を入力します。
＝VLOOKUP(G3,B3:E8,3,
手順５．VLOOKUP関数の第４引数を入力
続いて第４引数「検索方法」を設定します。「FALSE」と「TRUE」の２種類がありますが、上の見出しで説明したとおり、基本的に「FALSE」を使いましょう。
=VLOOKUP(G3,B3:E8,3,FALSE
手順６．VLOOKUP関数を確定
第４引数を入力したら、「Enter」キーを押して関数を確定させてください。
そうすると、表の中から商品番号25の商品名「風邪薬」が抽出されました。
VLOOKUP関数の３つの注意点
VLOOKUP関数を利用していく上で、知っておくべき３つの注意点を紹介します。
VLOOKUP関数を使う表に検索値が複数存在する場合
１つ目は、検索範囲の中に検索値が２つ以上存在している場合です。
下の表には、検索値の商品番号「15」が検索範囲内に２つ存在してます。(B4セルとB7セル）
この場合、VLOOKUP関数で商品名を抽出しようとすると、上の値が優先されて、B4セルの商品名「シャンプー」が抽出されます。
そのため、下側のB7セルの商品名「照明器具」を抽出しようとする場合は、VLOOKUP関数を複数条件で利用する必要があります。
この方法を知りたい場合は、下の「VLOOKUP関数の複数条件の使い方」の見出しを参考にしてください。
引数「検索値」と「検索範囲」の設定
２つ目の注意点は、1つ目の引数「検索値」と２つ目の引数「検索範囲」の位置関係です。
下のVLOOKUP関数の正しい例のように、検索値が検索範囲の一番左の列にある場合は、正しく値が抽出されます。
しかし、下の図のように検索値が検索範囲の中に入っていない状態でVLOOKUP関数を利用すると・・・
下の図のように、VLOOKUP関数の結果は「#N/A」とエラー値が表示されてしまいます。
検索値が検索範囲の一番左の列でなく、検索範囲の中央にいるような場合でも、同様に「#N/A」とエラー値が表示されてしまいます。
VLOOKUP関数を他のセルにコピペする場合
VLOOKUP関数を他のセルにコピペして利用する際には、引数「検索範囲」に絶対参照を設定しておく必要があります。
どういうことか図解で説明します。
H3セルに挿入されているVLOOKUP関数「=VLOOKUP(G3,B3:E8,3,FALSE)」をH4セルにコピペします。
そうすると、検索範囲内に商品番号「１０」は存在するにも関わらず、VLOOKUP関数の結果は「#N/A」とエラー値が表示されてしまいます。
あらためてコピーしたH4セルのVLOOKUP関数を確認してみます。
そうすると、以下のように３つ目の引数「検索範囲」まで一つ下に移動してしまっています。
=VLOOKUP(G4,B4:E9,3,FALSE)
そのため、検索値の商品番号「１０」が検索範囲から外れてしまっているためエラーが表示されます。
VLOOKUP関数を他のセルにもコピペで利用する場合は、ファンクションキー「F4」を使って、下のように引数「検索範囲」に絶対参照【$】を付けておけばコピー先でも検索範囲が変わらないのでエラーを防ぐことが出来ます。
=VLOOKUP(G3,$B$3:$E$8,3,FALSE)
VLOOKUP関数の結果がエラー「#N/A」となる場合
VLOOKUP関数を使った結果がエラー値「#N/A」となるのは、検索範囲内に検索値が見つからないために発生します。
以下の４つの原因が考えられます。
- 検索値が検索範囲の一番左の列に存在しない
- VLOOKUP関数の検索範囲が絶対参照に未設定
- VLOOKUP関数の検索値がわずかに異なる
- セルの書式が文字列
番号①と➁の原因は上の見出しで紹介した通りです。エラーに思い当たる操作があれば修正しましょう。
番号③と④がどういった内容か紹介します。
③検索値と検索する文字がわずかに異なる
３つ目のパターンは検索値と検索範囲の値がわずかに異なるために、検索値がヒットしない場合です。
例えば、「11 222」と「11 222」の数値は同じ値に見えるかもしれませんが、前者は”11”と”222”の間に半角スペースが含まれていて、後者は、”11”と”222”の間に全角スペースが含まれます。
検索値が「11 222」(半角スペース）で検索範囲の値が「11 222」(全角スペース）のため、半角スペース分異なるのでVLOOKUP関数上は違う値とみなされ、「#N/A」が表示されます。
同様に、半角スペースが先頭や末尾に入り込んでいる、漢字が異なるなど、意外に陥りやすい誤りです。
④セルの書式が文字列
４つ目のパターンは、検索値か検索範囲の値どちらかのセルの書式設定が【文字列】になっている場合です。
下図のように、検索値”15”は表内に存在しているので一見問題なさそうに見えますが、VLOOKUPの抽出結果は「#N/A」です。
原因は、B4セルの書式設定(表示形式)は【文字列】であることが原因です。
セルの書式設定(表示形式）を確認するには、対象のセルを右クリックし、右クリックメニューの「セルの書式設定」からセルの表示形式を確認してください。
文字列になっている場合は、表示形式を「標準」に変更しましょう。
VLOOKUP関数のエラー値の発生原因とエラーの解消方法、別の値への変換方法は以下の記事で詳しく紹介していますので、合わせて確認してください。
VLOOKUP関数で別のシートや別ブックの値を抽出する方法
VLOOKUP関数の検索値や検索範囲が異なるワークシートや異なるExcelブックに存在する場合でも関数は利用出来ます。
VLOOKUP関数の使い方は基本的にほぼ同じですが、シート（ブック）を切り替える手順が追加されますので操作手順を紹介します。
手順１．VLOOKUP関数の第１引数を入力
VLOOKUP関数を挿入するセルに、第１引数「検索値」を入力し、末尾に”,”(カンマ)を入力します。
手順2．検索範囲のシートをクリック
検索範囲のデータがある「表」シートをクリックします。そうすると、関数の末尾に「表！」とワークシート名の後に！が付きます。
＝VLOOKUP(B4,表!
手順３．検索範囲をドラッグ
続いて第２引数「検索範囲」を設定するため、検索値である「商品番号」列から右側のセル範囲をドラッグで選択します。選択し終えたら、”,”(カンマ)を入力します。
＝VLOOKUP( B4,表!B3:E8,
手順４．関数を完成させる
残りは通常の手順と同じです。第３引数「列番号」と第４引数「検索方法」を入力して「Enter」キーを押して関数を確定させてください。
＝VLOOKUP(B4,表!B3:E8,3,FALSE
手順５．VLOOKUP関数を確定
「Enter」キーで確定させると、VLOOKUP関数を挿入したシートに表示画面が戻ります。また、表の中から商品番号25の商品名「風邪薬」が抽出されます。
異なるブックのシートを指定する場合は、タスクバーのExcelのアイコンにマウスのカーソルを合わせてください。(図①)
そうすると、起動中のExcelブックが表示されるので、対象のExcelブックをマウス操作で選択(図➁）して検索範囲に使用するエクセルブックを切り替えて下さい。
VLOOKUP関数の複数条件の使い方
検索値が検索範囲内に複数存在する場合、VLOOKUP関数は一番上の値を返します。
そのため、下の表のような場合、検索値「15」で商品名を抽出すると、上側の「シャンプー」が抽出されます。
では下側の検索値「15」の商品名「照明器具」を抽出するためにはどうすればよいでしょうか。
検索値が複数ある場合は、下の２つの手順を実施するだけで複数条件に対応出来ます。
- 表にそれぞれの条件を結合したVLOOKUP関数用のキー列を挿入する
- VLOOKUP関数の1つ目の引数「検索値」に”＆”を利用する
D列に「商品番号」と「商品カテゴリ」の文字列を結合した「検索キー」列を挿入し、VLOOKUP関数の第１引数も「商品番号」と「商品カテゴリ」を&演算子を用いて結合します。
そして、以下のように関数を利用することで、下側の検索値「15」の商品名「照明器具」を抽出することが出来ます。
=VLOOKUP(H3&I3,D3:F8,2,FALSE)
このVLOOKUP関数の複数条件の使い方の詳しい手順や、３つ以上の検索値の場合の対処方法などは以下の記事で詳しく紹介していますので、参考にしてみてください。
[検索方法]を「TRUE」で指定した使い方
VLOOKUP関数の第４引数「TRUE」を指定した場合、「検索値」と完全一致する値が検索され、「検索値」が見つからない場合には、近似値が表示（「検索値」未満で最も大きい値）されます。
この見出しは「TRUE」を使った事例を具体的に１つ紹介します。
下の表から商品番号「34」の商品カテゴリを抽出しようとします。しかし、表には商品番号「34」は存在しません。
このように検索値が検索範囲から見つからない場合は、検索値未満で最大の値である商品番号「25」が近似の検索値として利用されるので、商品カテゴリ「風邪薬」が抽出されます。
=VLOOKUP(G3,B3:E8,3,TRUE)
このように、「FALSE」と「TRUE」の違いは、検索値が検索範囲に無かった場合に限り抽出結果が異なります。そのため、検索値が検索範囲に存在していた場合は、FALSEでもTRUEでも計算結果は同じです。
VLOOKUP関数の上級テクを紹介
VLOOKUP関数に関連する記事を３つ紹介します。
関数に使い慣れてきたら、ぜひこの３つのテクニックも覚えてみて下さい。
VLOOKUP関数とIF関数を組み合わせる
VLOOKUP関数とIF関数と組み合わせることで、『表から情報を抽出し、その抽出値を条件によって別の値を変換する』ことが出来るようになります。
具体的な実例を１つご紹介します。
下の商品販売高リスト表は商品番号毎の販売高が入力されています。
セルG4にIFとVLOOKUPを組み合わせることで、セルF4の商品番号の販売高が600(千円）を越える場合は、”○”を、超えない場合は”×”と表示させることが出来ます。
=IF(VLOOKUP(F4,$B$3:$D$11,3,FALSE)>=600,"○","×"）
この実例の詳しい作成手順や、これ以外にもVLOOKUP関数とIF関数を組み合わせた使い方を下のリンク記事で詳しく紹介していますので、興味のある方は参考にしてみて下さい。
VLOOKUP関数の列番号を自動入力する方法
VLOOKUP関数を複数のセルで利用する際、引数「列番号」をいちいち切り替えるのは面倒です。
そんな時、「列番号」が自動で切り替わる時短テクニックが２つあります。
COLUMN関数やMATCH関数と組み合わせた方法になります。
下の表と数式のように、第３引数に「COLUMN(B3)」と使うことで、VLOOKUP関数を隣のセルにコピペしても列番号を手作業で修正する必要はありません。
=VLOOKUP($G$4,$B$4:$E$15,COLUMN(B3),FALSE)
下のリンク記事で詳しい使い方を紹介していますので、ぜひ参考にしてみてください。
VLOOKUP関数の進化版「XLOOKUP関数」
XLOOKUP関数はVLOOKUP関数の後継関数として認知度が高まっています。
VLOOKUP関数は、慣れている人でも列番号の入力やエラー表示の解消など不便さを感じる方は多いかと思います。
XLOOKUP関数はVLOOKUP関数のそんな不便さを解消して誕生しました。
この関数が使えるExcelのバージョンは、Microsoft365とOffice2021です。
利便性がかなりアップしていますので、利用出来るバージョンであればVLOOKUP関数とはお別れしましょう！
VLOOKUP関数で表の更新されたセルを確認する
VLOOKUP関数は表のどのセルが更新されたか確認するのに役立ちますので、その使い方をご紹介します。
下の図の左側が更新前の表で、右側が更新後の表です。
表の行数が異なるので、更新された表には新しいデータが追加されていることが分かります。ではどの行が追加された行かVLOOKUP関数を使って確認します。
右側の更新後の表の方が２行分データが多いので、データ数が多い方の更新後の表にVLOOKUP関数を挿入します。
表に隣接したF列に以下のVLOOOKUP関数を挿入し、変更後の表の「商品番号」を検索して、変更前の表の「商品番号」を抽出します。
=VLOOKUP(B3,'表(元データ)'!$B$3:$B$13,1,FALSE)
VLOOKUP関数を挿入し終えたら、下のセルにもVLOOKUP関数をコピペして挿入します。
そうすると、２つのセルでエラー値「#N/A」が表示されます。この２行の商品番号「32」と「72」は更新前の表には存在しないということです。つまり、この２行が新しく表に追加されたデータということが分かります。
このように、VLOOKUP関数のエラー値「#N/A」をあえて表示させることで表と表を比較することが出来るので、ぜひ実践で役立ててみて下さい。
👇エラー値を別の値に変換したい場合は以下の記事を参照してください。