【関連記事】Excel（エクセル）の関数30選！よく使う基本の関数を一覧で紹介｜CANVAS
VLOOKUPとは
VLOOKUPはExcelで使用できる関数の1つで、初心者や数式が苦手という人でも簡単に表から条件に合ったデータを取得できる便利な機能です。
VLOOKUP関数を使うと、指定した範囲の表を検索し、指定したデータに対応する値を取り出すことができます。
この記事では、Excelをより活用したい方のために、VLOOKUP関数の基礎から応用、活用できるシーン、さらにはエラーが出た際の対処法まで詳しく解説します。
VLOOKUPの概要
ここでは、VLOOKUPでできることや基本的な構文などの概要を説明します。構文に関連して、引数の1つである「検索方法」の注意事項についても解説します。
VLOOKUPでできること
VLOOKUPは、Excelの表から縦方向にデータを検索し、指定した条件に一致する値を取得します。
VLOOKUPを使えば、データの件数が多い大きな表からでも、必要な情報を効率よく抽出することが可能です。
例えば、社員IDを入力するだけでその社員の所属部署を表示したり、顧客リストから特定の業種の企業名を検索したりすることができます。
VLOOKUPを活用することで、表の中から目視でデータを探すより手間を省くことができ、作業時間の短縮や、データ処理の誤りの減少につながります。
VLOOKUPの構文
VLOOKUPを使用するには、取得した情報を表示したいセルに以下の式を入力します。
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)「検索値」「範囲」「列番号」「検索方法」は、引数と呼ばれるもので、「,」区切りで検索のための条件を記述します。それぞれの引数についてわかりやすく説明します。
■検索値
検索値はVLOOKUPの1番目の引数で、抽出したいデータの条件を指定します。例えば、「商品名」列、「価格」列を含む在庫リスト表の中からマウスパッドの価格を取得したい場合、商品名「マウスパッド」が検索値です。
検索値は、関数内で直接指定することも、セル参照を使用することもできます。例えば、マウスパッドを直接指定する場合は、次のように入力します。
=VLOOKUP("マウスパッド", A2:B10, 2, FALSE)セル参照を使用する場合は、検索値が入力されているセルを指定します。例えば、セルA1に検索値が入力されている場合、次のように入力します。
=VLOOKUP(A1, A2:B10, 2, FALSE)この場合、セルA1に「マウスパッド」が入っていても「キーボード」が入っていても、その値が検索値となります。
■範囲
範囲はVLOOKUPの2番目の引数で、VLOOKUPが検索を行うデータの範囲です。範囲には、検索値が含まれる列と、取得したいデータが含まれる列を指定します。
例えば、商品名で検索して価格を取得したいとき、商品名がA列、価格がB列、データが2行目から15行目まである場合は、範囲は「A2:B15」となります。
また、検索値は、検索範囲の最初の列（1番左の列）に存在する必要があります。例えば、「C2:F10」で範囲指定する場合、検索値はC列になければなりません。
■列番号
列番号はVLOOKUPの3番目の引数です。指定した範囲の中で、取得したいデータが含まれる列の位置を指定します。
範囲の最初の列を1とカウントし、次の列は2となります。例えば、商品名を検索して価格を取得する場合、価格が範囲内の2列目にあるとすると、列番号は2になります。
=VLOOKUP(A1, A2:B10, 2, FALSE)■検索方法
検索方法はVLOOKUPの4番目の引数で、検索値を完全一致、近似一致のどちらの検索方法で検索するかを指定します。
完全一致で検索したい場合は「FALSE」、近似一致で検索する場合は「TRUE」を指定します。
検索方法のFALSEとTRUEの違い
VLOOKUP関数を使用する際、検索方法で完全一致「FALSE」と近似一致「TRUE」のどちらを選ぶかで結果が大きく変わるため、詳しく解説します。
まず、検索値が文字列の場合には、完全一致「FALSE」を指定する方が良いでしょう。例えば、「マウスパッド」に近い文字列の商品名を検索しようと近似一致「TRUE」にしても、意図した通りの結果が返らないことが多いためです。
検索値が数値の場合は、検索値に最も近いデータを検索します。例えば、範囲内に5、20、 40という値が並んでいるとき、検索値に25を指定すると、最も近い20で検索されます。
この場合、検索値の対象となる列が昇順に並んでいる必要がありますので注意が必要です。VLOOKUPでは基本的に、該当した1番上の行だけ取得するためです。
そのため、「TRUE」を使用したい明確な用途がない限り、検索値が文字列の場合でも数値の場合でも、予期しない結果が返るのを避けるため、基本的には「FALSE」を指定するのが良いでしょう。
VLOOKUPの応用
ここでは、ExcelのVLOOKUP関数をより効果的に活用するための応用を説明します。IF関数との組み合わせによる条件分岐、複数条件の設定方法、そして別シートのデータ参照について解説します。
IF関数との組み合わせ
VLOOKUP関数は、IF関数と組み合わせることで、条件分岐を使った結果表示を行うことができます。
IF関数の構文は「=IF (論理式, 真の場合, 偽の場合)」です。この「論理式」の中に、VLOOKUP関数をそのまま使って条件を指定できます。
例えば、検索値「マウスパッド」の価格が1,000円以下かどうかを判定し、1,000円以下であれば「1000円以下」、そうでなければ「1000円超」を表示するには、以下のような式を使用します。
=IF(VLOOKUP("マウスパッド", A2:B5, 2, FALSE) <= 1000, "1000円以下", "1000円超")IF関数の引数の「論理式」の中に、「VLOOKUP("マウスパッド", A2:B5, 2, FALSE) 」がそのまま含まれ、そのVLOOKUPの結果が「<= 1000」であるかの真偽を判定しています。
複数条件の設定方法
VLOOKUP関数の検索値は、1つの値しか指定できません。しかし、工夫次第で複数条件にも対応可能です。
2列以上の値を結合したセルを作成し、そのセルをVLOOKUP関数の検索値として使用することで、複数条件でのデータ検索を行います。
例えば、商品データベースでカテゴリーと商品名を条件に在庫数を検索する場合を考えます。下の表を確認してみましょう。以降の説明では、1行目を見出し行、1列目をA列とします。
| カテゴリー | 商品名 | 在庫数 |
|---|---|---|
| 文房具 | ノート | 50 |
| 文房具 | 鉛筆 | 100 |
| 家電 | テレビ | 20 |
| 家電 | 冷蔵庫 | 10 |
VLOOKUPでは検索値の指定は1つのため、「文房具」かつ「鉛筆」を検索値として指定できません。
そこで、「カテゴリー・商品名」という結合セルを追加し、検索値として使用します。
| カテゴリー | 商品名 | カテゴリー・商品名 | 在庫数 |
|---|---|---|---|
| 文房具 | ノート | 文房具ノート | 50 |
| 文房具 | 鉛筆 | 文房具鉛筆 | 100 |
| 家電 | テレビ | 家電テレビ | 20 |
| 家電 | 冷蔵庫 | 家電冷蔵庫 | 10 |
「カテゴリー・商品名」のC列には、「カテゴリー」と「商品名」を結合する式を入力します。C2に「=A2&B2」を入力し、フィルハンドルを使って下の行に適用しましょう。
これにより、「カテゴリー・商品名」の「文房具鉛筆」を検索値とすることで、複数条件を満たす検索を行えます。
別シートのデータ参照
VLOOKUP関数を使用してデータを検索する際、検索の対象になる表が別シートにあることがあります。
その場合、VLOOKUPの引数の「範囲」の値の前に、シート名を表す「‘シート名’!」を追加することで別シートを参照できます。
=VLOOKUP("マウスパッド", '商品マスタ'!C2:E30, 3, FALSE)上記の例では、「商品マスタ」シートのC2からE30の範囲内で、C列から「マウスパッド」という値を検索し、その行のE列にある値を取得します。
VLOOKUPを使えるシーン
ここでは、ExcelのVLOOKUP関数を実際の業務で活用できる具体的なシーンについて説明します。大量の顧客情報を素早く検索する方法や、別シートで一元管理された情報を参照する方法を紹介します。
顧客IDを使って大量の顧客情報を素早く検索する
VLOOKUPを使用することで、大量のデータの中から必要な情報を素早く検索することができます。
例えば、「顧客ID」、「顧客名」、「住所」、「電話番号」、「メールアドレス」などを含む顧客リストに情報が3,000件ほどあり、日々、特定の顧客IDに基づいて顧客情報を確認する業務を行うとします。
顧客ID「001746」の顧客のメールアドレスを取得したい場合、目視で確認するのは案外大変です。
表をスクロールして該当の顧客IDを探し、そのまま右方向に、行がずれないようにメールアドレスの列を見に行く必要があります。見間違いによるミスが発生する可能性もあるでしょう。
そこで、VLOOKUPを使えば素早く、間違いなくデータを取得できます。
=VLOOKUP(001746,A2:E3000, 5, FALSE)上記の例では、A2からE3000の範囲内で、A列「顧客ID」から「001746」の値を検索し、その行のE列「メールアドレス」にある値を取得します。
別シートで一元管理された情報を参照する
例えば、小売業で商品価格の変更が頻繁に行われる場合、「商品マスタ」シートにすべての最新価格情報を一元管理し、受注シートで常に最新の価格を参照することができます。
「商品マスタ」シートは以下の内容です。「価格」列は、それぞれの商品の現在の取り扱い価格を常時更新するようにします。
| 商品コード | 商品名 | 価格 |
|---|---|---|
| 001 | ペン | 100 |
| 002 | ノート | 200 |
| 003 | 消しゴム | 50 |
「受注」シートは以下の内容で、注文が入るとデータを追加します。価格を手入力する場合、商品の価格が変わったことに気付かないとミスが発生するため、一元管理されている「商品マスタ」シートから価格を参照するようにします。
| 受注No. | 商品コード | 売上数量 | 価格 | 売上金額 |
|---|---|---|---|---|
| 0294 | 002 | 10 | =C2*D2 |
「価格」のセルに、以下のVLOOKUP関数を入力します。
=VLOOKUP(A2, '商品マスタ'!A:C, 3, FALSE)これで、受注時の「商品コード」を検索値として、「商品マスタ」シートの「価格」を取得することができます。
範囲は「'商品マスタ'!A:C」にしてA列からC列の全ての行を範囲とすることで、「受注」シートでの行の違いにより、参照する行がずれることがないようにしています。
VLOOKUPのトラブル事例
ここでは、VLOOKUP関数を使用する際に発生しやすいトラブルについて説明します。「＃N/A」エラーや検索方法の誤りなど、よくあるケースを見ていきましょう。
検索値が存在しないため「＃N/A」エラーが出る
検索値がデータ範囲内に存在しない場合、「#N/A」エラーが表示されます。「#N/A」エラーは、「検索値が見つからない」ことを意味します。
検索値が正しく入力されているか、または検索値が検索範囲に含まれているかを確認してください。
よくあるケースとして、検索値や検索範囲の値に不要なスペースが含まれていると、「#N/A」エラーが表示されます。必要に応じて、TRIM関数を使って余分なスペースを削除すると良いでしょう。
範囲の指定の誤りで「＃N/A」エラーが出る
VLOOKUPの検索範囲の指定で、検索値を検索する対象の列は、範囲の左端に位置していなければなりません。
例えば、検索値が範囲の2列目や3列目にある場合、正しい結果が得られません。この場合も「#N/A」エラーが表示されます。
| 商品コード | 商品名 | 価格 |
|---|---|---|
| 001 | ペン | 100 |
| 002 | ノート | 200 |
| 003 | 消しゴム | 50 |
上記の表で、B列「商品名」の「ノート」を検索値として指定したい場合、以下のVLOOKUPの記述は誤りです。
=VLOOKUP("ノート", A2:C4, 3, FALSE)検索対象であるB列が、指定した範囲「A2:C4」の1番左端ではなく、2列目となっているため、範囲の指定が適切ではありません。
正しい記述は、以下の通りです。検索対象のB列が1番左端の列になるように、範囲を「B2:C4」としました。
=VLOOKUP("ノート", B2:C4, 3, FALSE)検索方法がFALSEになっていない
VLOOKUP関数で取得した結果が予想したものと異なる場合、VLOOKUP関数の4つ目の引数「検索方法」が、「TRUE」であることが理由の1つとして考えられます。
意図しない検索結果になることを防ぐため、特に理由がなければ検索方法は基本的に完全一致「FALSE」で使用すると良いことを前述しました。
しかし、検索方法のデフォルト値は、近似一致「TRUE」です。そのため、引数を省略すると「TRUE」になります。
=VLOOKUP("ノート", B2:C4, 3)上記は、VLOOKUPで4つ目の引数を書き忘れた例です。この場合、引数を省略したとみなされ、検索方法は「TRUE」になります。意図しない検索結果にならないように、「FALSE」の記載を忘れないように気を付けましょう。
VLOOKUPで効率よくデータを検索しよう
ここまで、ExcelのVLOOKUP関数について詳しく解説してきました。Excelでデータを検索・参照する際に便利な関数で、日常的なデータ処理において、さまざまなシーンで活用できるでしょう。
VLOOKUP使い方を正しく理解して必要なデータを素早く検索し、業務の効率化に繋げてください。
その他VLOOKUP関連記事
その他、VLOOKUPに関連する内容は、下記の記事でもご紹介しています。ぜひ参考にご覧ください。
VLOOKUP関数で複数条件を指定する？簡単にできる方法を図解
VLOOKUPで別シートを参照するには？記述方法や実践例を解説
VLOOKUP関数でエラーが出た？#REF!エラーなどの解決法を詳しく解説
VLOOKUP関数がうまく反映されない？その原因と対策を図解
VLOOKUPとIFの組み合わせで何ができる？便利な使い方を解説
VLOOKUPで部分一致検索するには？ワイルドカード使用法を解説
VLOOKUPとXLOOKUPの違いとは？使い方を図解で解説
VLOOKUPで0を表示しない方法とは？発生原因と対処法を解説
転職サポートを受ける
アンドエンジニアへの取材依頼、情報提供などはこちらから
