業務である程度Excelを使いこなしてくると、入力作業だけでなく関数を使った処理などもでてきますよね。
そんな時に便利な関数のひとつに、「VLOOKUP関数」があります。
VLOOKUP関数はデータ検索や条件に一致するデータ抽出をするための関数。
事務や分析などでは利用頻度も多く、非常に便利な関数です。しかし、使い方がわかりにくい関数としても有名。
「このやり方であっているのかな」「上手に使いこなせているのかな」と疑問や不安を感じる方も多いのではないでしょうか。
そこで今回の記事では、VLOOKUP関数の基本的な使い方からエラーが起きたときの対処方法などをわかりやすく解説します。
VLOOKUP関数をすでに使いこなしている方は復習として、これから身につけたいという方は業務効率化のために、ぜひ参考にしてくださいね。
目次
VLOOKUP関数とは？
VLOOKUP関数とは、Excelに入力されたデータを縦方向に検索し、値が一致した行の指定した範囲のデータを返す関数です。
大量のデータの中から欲しい情報を検索して入力する場合、手作業では効率が悪いだけでなく、ミスが発生する可能性もあります。
そのようなときにVLOOKUP関数を使うと、効率的に処理することができるのです。
そのため、VLOOKUP関数は事務処理などの実務で頻繁に利用される関数です。
ほかにも似たような関数にHLOOKUP関数やXLOOKUP関数があります。
それぞれの違いは次の通りです。
| VLOOKUP関数：縦（V=Vertical）方向に表データを検索して値を返す関数HLOOKUP関数：横（H=Horizontal）方向に表データを検索して値を返す関数 XLOOKUP関数：VLOOKUP関数とHLOOKUP関数の機能をあわせた新しい関数 |
このなかでも、VLOOKUP関数を利用する頻度は特に高いため、まずはVLOOKUP関数から覚えていきましょう。
また、XLOOKUP関数はOffice365向けに新たに実装された関数であり、利用するOfficeのバージョンによっては利用できない可能性があります。
ご自身が利用しているMicrosoft Officeのバージョンを確認しておいてくださいね。
VLOOKUP関数を利用する4つのシーン
VLOOKUP関数が活躍するのはどのようなときかをまとめました。
日頃の業務にあてはまるものがあるか、チェックしてみましょう。
1.他のリストからデータをとってくる
もっとも典型的な活用シーンです。
他の表からデータを取ってきたい場合、VLOOKUP関数を利用して効率よく作業できます。
2.確認作業に利用する
申込書などからExcelへのデータ入力業務をする時、正しく入力できているか確認したり、別の方がダブルチェックしたりしますよね。
そのようなときにもVLOOKUP関数は活用できます。
3.データ入力の効率化に利用する
システムなどにインポートするExcelデータは、文字列ではなくコードで入力することが多いですよね。たとえば、受発注データなども商品名ではなく商品コードを入力するなどです。
コードを手入力するのはミスしやすいですし、そのミスに気づきにくいという側面があります。
そのような場面では、プルダウンで商品名を選ぶことで、商品コードが自動的に入力されるような仕組みにしておくとよいです。
たとえば、B列は商品リストのA列を「元の値」としたプルダウンを設定しておきます。
プルダウンから商品を選択することで、Ｃ列に商品IDがVLOOKUP関数によって自動入力されるという状況を作るのです。
4.データを差し込みする
伝票とか請求書のような帳票の場合、欄がたくさんあるので毎回手動で入力するにはちょっと不便な作りをしています。
そのような場合には、データを入力・蓄積するシートといった「作業用シート」と、印刷用のシートを分けると便利です。
印刷用のシートにVLOOKUP関数を入力しておけば、差込印刷のような形で利用することができます。
VLOOKUP関数の使い方
ここからは実際にVLOOKUP関数をどのように使うのかを解説していきます。
VLOOKUP関数の書式
| =VLOOKUP( 検索値, 検索範囲, 戻り値を含む列番号, 検索方法 ) |
VLOOKUP関数を利用する際には、検索値・検索範囲・戻り値を含む列番号の3つの引数を必ず指定する必要があります。
| 検索値：検索する値を入力、おもに検索値のセルを指定 検索範囲：検索値が含まれるセルの範囲を指定戻り値を含む 列番号：検索範囲から取り出したいデータの列番号を指定 検索方法：完全一致（0/FALSE）または近似一致（1/TRUE）を指定 ※検索方法は省略でき、省略した場合は近似一致（1/TRUE）として扱われます。 |
検索方法の完全一致と近似一致の違いは、検索値との比較方法です。
たとえば、検索値が25の場合、完全一致では検索範囲から25に該当するデータのみを抽出します。近似一致の場合は、25に近い数値24や26などもデータを抽出します。
それではVLOOKUPの使い方を注意点とともにご紹介します。
3STEPでVLOOKUPの設定をしていきましょう。
準備｜VLOOKUP関数を活用する前にすべきこと
まずは、VLOOKUP関数を活用する前に、「全角と半角の統一」「検索値を昇順に設定」して、VLOOKUPを使えるように準備しましょう。
全角と半角を統一する
全角や半角などを共同してしまうとエラーが出てしまいます。
まだExcelやスプレッドシートになれていない人は、日頃から「半角」を使用することをおすすめします。
検索値を昇順に設定する
検索方法を「TRUE」にした際は、必ず検索値を昇順にする必要があります。
検索値が昇順ではない場合、「管理番号」と「価格」が一致せず参照されてしまいます。
TRUEの場合、前行と次行の関係によって参照されるため、検索値は必ず昇順にしておきましょう。
設定｜セルに関数ボタンに式を入力する
セルに関数ボタンに式を入力します。
下図のように関数ボタンとは「f(x)＝」と表示されている部分のことです。
式を入力する際、「=」「()」などは「半角」で入力することを忘れないようにしましょう。
修正｜エラーは無視しない
VLOOKUP関数を使用する際に、エラーがでるときもあります。エラーが出るのには様々な原因が考えられますが、多くの場合入力ミスが原因です。
参考：U-NOTE｜VLOOKUP関数の使い方とは？準備から基本の式U-NOTE｜
VLOOKUPで#N/Aのエラーが出たときの原因と対処法
VLOOKUP関数は正しく使えているはずなのに「#N/Aエラー」が出てしまうと、焦ってしまうと思います。
ですが「#N/Aエラー」と表示されるのには必ず理由があるので、落ち着いて対処していきましょう。ここでは、考えられる原因とその対処法についてわかりやすく解説していきます。
| 全角と半角が一致していない 検索範囲の左端に検索値が見つからない 検索値が昇順になっていない 検索値が小数で計算した値になっている 検索値が絶対参照になっていない 検索値＜検索範囲の最小値となっている 完全一致を指定しているが一致しているものが存在しない |
エラーを表示させないようにするために役立つ関数に「IFERROR関数」があります。IFERROR関数はエラー時に表示する文字列を指定できる関数で、構文は次の通りです。
| =IFERROR(値, エラー時に表示する文字列) |
なお、VLOOKUP関数と組み合わせることで、検索値が存在しない場合でもエラーを表示させないようにできます。
| =IFFERROR(VLOOKUP関数, エラー時に表示する文字列) |
VLOOKUP関数の活用時に発生する最も多いエラーは「#N/A」です。
前述したとおりその多くは、検索値と検索範囲の指定を誤っていることが原因。
また、VLOOKUP関数では検索範囲の左端を検索のキーとするため、その点も意識しておきましょう。
また、検索値が存在していても想像した結果が返ってこないことはよくあります。
その場合には
- 検索値に半角の記号を使っていないか
- ソートがきちんと機能しているか
これらをチェックします。
Excelでは半角と全角を明確に分けているため、検索値には半角は利用しない方が無難です。
VLOOKUP関数の検索方法は近似一致（1/TRUE）がデフォルトで設定されているので、ソートによる誤った検索結果が表示されることも珍しくありません。
そこで、VLOOKUP関数でエラーを発生させないためには、改めて次にあげることを意識するとよいでしょう。
- 検索値と検索範囲をしっかりと指定する
- 検索値と検索範囲に「$」マークをつけて範囲の固定を行う
- 検索値に半角記号を入力しない
- 検索範囲の左端を基準にソートをかける
- 原則として検索方法は完全一致（0/FALSE）を利用する
これらを意識してVLOOKUP関数を利用することで、エラーが発生する可能性を大きく減らせます。
VLOOKUP関数で複数条件を指定する方法
検索値が一つだけだとデータが重複してしまう時、複数の項目を検索値にすることもできます。方法はとても簡単です。
先ほどの「検索値」に複数の条件を「&」でつなぐだけです。
たとえば、A,B,C,D,Eの５つの会社があり、それぞれの会社が販売している家電製品とその価格が書かれたリストが存在し、この中からB社が販売している掃除機の値段を知りたいとします。
Bの掃除機の値段が知りたい時、条件が「B」と「掃除機」の２つになるので今までに見てきたVLOOKUP関数は使えません。
複数の条件で検索したい時は、それらの条件を一つにまとめた項目を作る必要があります。
準備として、まずA列に「会社名&家電製品」の項目を作り、A3には「=B3&C3」と入力します。
次に、式を完成させていきます。今回はBの掃除機の値段が知りたいので、
条件1は「B12」、条件2は「C12」、両者を「&」でつなげます。
なお、範囲は「B3:E10」で列番号は「3」です。
最後にOKをクリックすると、Bの掃除機の値段が表示されます。
参考：JOBNET｜VLOOKUPとは？ 複数の条件を設定したい場合など、実務での活用法まで解説！
VLOOKUP関数の代わりにINDEX関数とMATCH関数を使用する
VLOOKUP関数にはある特定の制限があり、それはVLOOKUP関数は「左から右の値のみを検索する」ということです。
つまり、参照する値を含む列は、常に戻り値を含む列の左側に置く必要があるということ。Excelやスプレッドシートがこのような方法で作成されていない場合は、VLOOKUP関数を使用できません。
そんなときは、代わりにINDEX関数とMATCH関数の組み合わせを使用します。
Microsoftサポートの例でみていきましょう。
検索する値であるシカゴが左端の列にない小さいリストについて説明します。
そのためVLOOKUP関数を使うことはできません。
代わりに、MATCH関数を使って範囲 「B1:B11」 でシカゴを検索します。
シカゴは4行目にあります。
次に、INDEX関数ではその値を引数として使用して、4番目の列 (列D) にあるシカゴの人口を検索します。 使用された数式はセルA14に表示されるので、試してみてくださいね。
引用：Microsoftサポート｜VLOOKUP、INDEX、または MATCH を使って値を検索する
まとめ
いかがでしたでしょうか。
今回の記事ではVLOOKUP関数の使い方や複数条件での対応法、VLOOKUP関数以外のおすすめ関数をご紹介してきました。
VLOOKUP関数は作業の効率化が図れるため、社会人には必須の関数のひとつです。
VLOOKUP関数は一見難しそうに見えますが、仕組みを理解すれば難しいものではありません。Excelを効率的に使うのに大いに役立つ関数であるため、ぜひ使い方を覚えてみてくださいね。
本記事を参考に使い方と数式を覚えておけば、それほど難しいものではありません。
事務処理やデータ処理の精度や効率が格段にあがるので、習得することをおすすめします。
