VLOOKUP関数の使い方を徹底解説！新入社員が知っておくべきExcelの基本

はじめに

Excelで大量のデータから必要な情報を抽出したい！事務処理で日常的にExcelを利用していると、そのような場面に頻繁に遭遇します。

そんな時に便利なのが「VLOOKUP関数」です。この関数を使えば、大量で複雑なデータの中からでも、たった一つの式で必要な情報を簡単に見つけ出すことができます。この記事では、VLOOKUP関数の基本的な使い方から応用的なテクニックまでを詳しく解説します。

VLOOKUP関数は、Excelを業務で本格的に使い始める新入社員には、最低限知っておいてほしい関数の一つです。仕事を始めたばかりの新入社員でも、この記事を読めば、今日からVLOOKUP関数を使ってExcel作業を効率化できるようになるでしょう。

VLOOKUP関数とは？

VLOOKUP関数とは、Excelの表の中から、特定の値を探し出し、対応する値を取り出すための関数です。まるで辞書で単語の意味を調べるように、Excelの表から必要な情報を引き出すことができます。

なぜVLOOKUP関数を使うのか？

• 時間短縮: 手作業でデータを探す時間を大幅に削減できます。例えば、1000件のデータの中から特定の商品情報を検索するのに、VLOOKUP関数を使えば数秒で完了します。
• ミス防止: 手作業での入力ミスを減らし、データの正確性を高めます。
• 業務効率化: 複雑なデータ処理を自動化することで、業務全体の効率化に貢献します。

VLOOKUP関数の構造

VLOOKUP関数は、以下の4つの引数から構成されています。

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

• 検索値: 探したい値を指定します。
• 検索範囲: 検索する範囲を指定します。
• 戻り値の列番号: 戻したい値が含まれる列の番号を指定します。
• 検索の型:
  • FALSE: 完全一致で検索します。指定した値と完全に一致するデータのみを検索します。
  • TRUE: 近似一致で検索します。指定した値とほぼ一致するデータを探します。ただし、検索範囲は昇順にソートされている必要があります。

VLOOKUP関数は、検索範囲の一番左側の列から検索値を探し出し、その行の戻り値の列番号のデータを返します。検索値が見つからない場合には、#N/Aエラーを返します。

検索の型の違い

完全一致と近似一致

• 完全一致: 指定した値と完全に一致するデータのみを返します。
  • 例えば、学生IDが “S002″ の生徒の英語の点数を検索する場合、”S002” と完全に一致するデータのみが対象となります。
• 近似一致: 指定した値とほぼ一致するデータの中で、最も近い値を返します。ただし、検索範囲は数値または文字列の昇順にソートされている必要があります。
• 近似一致の注意点:
  • 検索範囲が昇順にソートされていないと、意図しない結果になることがあります。
  • 数値の近似一致では、小数点以下の桁数に注意が必要です。

数値型と文字列型の近似一致の違い

下の図は近似一致の動作を検証したものです。検索値が「数値型」と「文字列型」とでは明らかに動きが異なります。

数値型の近似一致

数値に対して近似一致を指定した場合、完全一致したデータが見つからなければ「検索値以下の最大値」が戻り値となります。このため数値型では、”1000″ までは#N/Aエラーを戻し、”1005″ 以降のデータは全て「中村美咲」を戻しています。

文字列型の近似一致

文字列型でも同様に完全一致したデータが見つからなければ「検索値以下の最大値」が戻り値となります。ただし、文字列型の比較は、1文字目から各文字を文字コードで順に比較することで行われます。

例えば、 検索値 “1006” と “1005” を比較する場合、まず、1文字目を比較します。これはどちらも “1” なので違いがありません。続いて2文字目、3文字目もどちらも “0” なので違いがなく、4文字目の “6” と “5”を比較した時に初めてを違いが生じて大小が判別できます。よって、”1001″～ “1005” は、すべて検索値 “1006” 以下であるといえます。「検索値以下の最大の値」が戻り値なので “1005” の「中村美咲」を戻します。

検索値 “999” と “1005” を比較する場合はどうでしょう。1文字目の “9” と “1” の比較により、この時点で “999” は “1005” より大きいと判別できます。よって、”1001″～ “1005” は、すべて検索値 “999” 以下であるといえますので “1005” の「中村美咲」を戻します。

さらに、検索値 “100” と “1001”との比較を見ていきます。1文字目から3文字目までは違いがありません。その次の4文字目の比較では “100” には文字が存在しません。一方 “1001” には “1” があります。この時点で “100” は文字列の終わりに達しているため、”100″ は “1001” より小さいと判別します。「検索値以下の最大の値」が戻り値ですがそのような値は存在しないので #N/Aエラーを戻しています。

VLOOKUP関数の実践例

実践例１:IDと一致する行の任意の列のデータを返す

左側の表には、700名分の「生徒ID」「生徒氏名」と「数学」「英語」「国語」の点数が入力されています。右側の表の「生徒ID」のセル G2 に生徒ID を入力すると、一致する生徒の氏名及び成績を左側の表から検索してそれぞれのセルH2 から K2 に表示するというものです。

セル H2 から K2 には次のとおり VLOOKUP関数が入力されています。

H2 : =VLOOKUP($G$2,$A$2:$E$701,COLUMN(B:B),FALSE)
I2 : =VLOOKUP($G$2,$A$2:$E$701,COLUMN(C:C),FALSE)
J2 : =VLOOKUP($G$2,$A$2:$E$701,COLUMN(D:D),FALSE) 
K2 : =VLOOKUP($G$2,$A$2:$E$701,COLUMN(E:E),FALSE) 

• $G$2 : 検索する値です。この例では、セルG2にある生徒ID「1005」を指します。列G と行2 の両方を固定しているため、コピーしても参照先が変わりません。
• $A$2:$E$701 : 検索範囲です。左側の表全体を絶対参照で固定しています。これにより、範囲が固定され、コピーしても変わりません。
• COLUMN(B:B) : 返す列の番号を指定します。この場合、「生徒名」の列を指します。COLUMN関数を使うことで、現在のセルの列番号を動的に取得できます。この式では、COLUMN(B:B) が “2” を返すため、3列目の値が返されます。この式を横にコピーすると、自動的に参照する列が変更されます。
• FALSE: 完全一致を要求します。検索値と完全に一致するデータのみを返します。

実践例２:IDをキーに別シートからデータを転記する

「学生名簿」シートと「点数一覧」シートの2つがあります。学生名簿シートには生徒の基本情報が登録されており、点数一覧シートには各教科の点数、合計点、平均点が入力されています。

学生名簿シートに合計得点を入力する欄を用意し、VLOOKUP関数を使って一致する生徒ID を点数一覧シートから検索し、合計点数を入力します。

F2 : =VLOOKUP(A2,点数一覧!$A$2:$F$701,COLUMN(E:E),FALSE)
F3 : =VLOOKUP(A3,点数一覧!$A$2:$F$701,COLUMN(E:E),FALSE)
F4 : =VLOOKUP(A4,点数一覧!$A$2:$F$701,COLUMN(E:E),FALSE)
・・・
F701 : =VLOOKUP(A701,点数一覧!$A$2:$F$701,COLUMN(E:E),FALSE)

このように、VLOOKUP関数を使うと、ID をキーにして複数のシートを連結することができます。

実践例３: 近似一致の活用事例

VLOOKUP関数で ID と一致するデータを検索する場合など、完全に一致することを想定するときに検索の型に近似一致を使うと意図しないデータを返してしまうので注意が必要です。

検索の型は、特段必要がない限りは、「完全一致」を使用することが基本であると理解していますが、ここでは近似一致が有効なケースのひとつを紹介します。

上部の表（A2:B6）: この表には、基準点（列A）とそれに対応する評価（列B）が示されています。基準点は昇順に並んでおり、それぞれの範囲に対して評価が決められています。

例えば、0点から59点までは「不可」、60点から 69点までは「可」、70点から 79点までは「良」、80点から 89点までは「優」、90点以上は「秀」となっています。

下部の表（A9:C18）: この表には、生徒の名前（列A）とその素点（点数、列B）が示されています。評価（列C）は、VLOOKUP関数を使って自動的に算出します。

セルC9には、次のという数式が入力されています。

C9 : =VLOOKUP(B9,$A$2:$B$6,2,TRUE)

この関数は、セルB9にある素点（65点）を基に、上部の基準表から評価を「近似一致」（基準値以下で最大の値）で検索し、その結果をセルC9に表示します。以下、セルC10 ～ C18 まで同様です。

例えば、田中 太郎の点数（65点）は60点以上70点未満の範囲に入るので、評価「可」が返されます。

VLOOKUP関数でよく起こるエラーとトラブル

VLOOKUP関数を使用する際によく発生するエラーとトラブルについて、以下にまとめます。

#N/Aエラー

• 原因: 検索値が指定した範囲内で見つからない場合に発生します。
• 対処法:
  • 検索値が範囲内に存在するか確認します。
  • データが一致しているか（スペースや不要な文字がないか）を確認します。
  • データ型の違い（文字列と数値の不一致など）を確認します。数値型のデータを検索しているのに検索値が文字列型になっていないか確認します。

#REF!エラー

• 原因:
  • VLOOKUP関数で指定した戻り値の列番号が、検索範囲の列数を超えている場合に発生します。
  • また、指定した範囲が存在しない場合にも発生することがあります。
• 対処法:
  • 戻り値の列番号が検索範囲内の正しい列数であるか確認します。
  • 例えば、=VLOOKUP(A1, B1:C10, 3, FALSE)のように、範囲B1:C10の中で3列目を指定するとエラーになります（範囲には2列しかないため）。
  • 検索範囲を正しく設定し直すか、列番号を見直します。

#NUM!エラー

• 原因:
  • VLOOKUP関数で近似一致（TRUEまたは省略）を使用している際に、検索範囲のデータが昇順にソートされていない場合に発生します。
• 対処法:
  • 検索範囲のデータを昇順にソートします。近似一致を使用する場合は、検索範囲の最初の列が必ず昇順に並んでいる必要があります。
  • もし近似一致を使用したくない場合は、VLOOKUP関数の最後の引数をFALSEに設定します。

データ型の不一致

• 原因:
  • #N/Aエラーの項でも触れましたが、検索範囲のデータ型と検索値のデータ型が一致しない場合、VLOOKUP関数はデータを正しく検索できません。
  • 例えば、検索範囲の生徒IDが数値型で登録されているのに、検索値が文字列型（例: “1005”）である場合、検索が正しく機能しません。
• 対処法:
  • 検索値と検索範囲のデータ型を一致させます。数値型と文字列型の不一致がないかを確認し、必要に応じてTEXT関数やVALUE関数を使用して型を変換します。

範囲の参照ミスや絶対参照の忘れ

• 原因:
  • VLOOKUP関数の検索範囲を絶対参照（例: $A$2:$B$6）にしていない場合、範囲がずれてしまい、正しい検索結果が得られなくなることがありますので注意が必要です。
• 対処法:
  • 参照範囲を絶対参照に設定します（例: $A$2:$B$6のように $ を使って固定する）。

複数の同一検索値が存在する場合

• 原因:
  • VLOOKUP関数は最初に見つかった検索値に対応する値を返します。検索範囲に同じ検索値が複数存在する場合、正しい結果が返らないことがあります。
• 対処法:
  • 検索値がユニークになるようにデータを整理するか、INDEX関数とMATCH関数の組み合わせを使用して、より柔軟な検索を行います。

VLOOKUP関数を正しく使用するためには、これらのエラーを理解し、適切に対処することが重要です。データの整合性を保ち、検索範囲を適切に設定することで、VLOOKUPの使用がより効果的になります。

VLOOKUP関数の制約とその代替案

VLOOKUP関数の制約

VLOOKUP関数は、Excelで最もよく使用される検索関数の一つですが、いくつかの重要な制約も存在します。

• 左から右への検索のみ可能:
  VLOOKUPは検索範囲の最左列で検索値を探し、その右側の指定した列から値を返します。これにより、検索値が右側の列にある場合は使用できません。
• 一致しない場合のエラー処理が不十分:
  VLOOKUPは検索値が見つからない場合、#N/Aエラーを返します。エラーを処理するための追加の関数（例: IFERROR）が必要です。
• 大きなデータセットでのパフォーマンス問題:
  大量のデータを持つシートでVLOOKUPを頻繁に使用すると、処理速度が遅くなる可能性があります。特に計算が多い場合や、大規模なデータセットを扱う場合、パフォーマンスに影響を与えます。
• 部分一致やワイルドカード検索が難しい:
  VLOOKUPは完全一致（FALSE）または近似一致（TRUE）しかサポートしていません。部分一致やワイルドカードを使った柔軟な検索には対応していません。

VLOOKUP関数の代替案

VLOOKUP関数の制約を強く感じたら、INDEXとMATCH関数の組み合わせやXLOOKUP関数などで代替することを検討してください。より強力で柔軟なデータ操作が可能になります。

INDEX関数とMATCH関数の組み合わせ

INDEX関数とMATCH関数を組み合わせると、VLOOKUPの柔軟性の欠如を補うことが可能です。

例） 商品の売上データを検索

• 商品コードが列A、商品名が列B、売上額が列Cに入力されているとします。
• セルD1に「パソコン」の売上額を取得するための式を入力します。

D1 : =INDEX(C2:C6, MATCH("パソコン", B2:B6, 0))

• MATCH関数は、指定された照合の種類（1:以下、0:完全一致、-1:以上）に従って検査範囲内を検索し、検索値と一致する要素の配列内での相対的な位置を表す数値を返します。
• INDEX関数は、指定された行と列が交差する位置にある値またはセルの参照を返します。
• 結果 : 50000

この式は、B1 から B5 の範囲で「パソコン」を検索し、その行の C列（売上額）のデータを返します。
MATCH関数で任意の列や行を検索し、その結果をINDEX関数に使用することで、任意の列や行から値を返すことができます。

XLOOKUP関数

概要: XLOOKUPはExcel 365およびExcel 2021以降のバージョンで使用できる強力な検索関数です。VLOOKUPの多くの制限を克服し、追加の機能を提供します。

利点:

• 双方向の検索: 左右どちらの方向にも検索が可能です。
• 部分一致とワイルドカード検索: 部分一致やワイルドカードを使用した検索が可能です。
• エラー処理が組み込まれている: 見つからない場合の代替値を指定できます。
• 複数の検索条件: 複数の検索条件を指定できるため、より柔軟です。

使用例:

=XLOOKUP(D1, A2:A10, B2:B10, "見つかりません", 0, -1)

これは、範囲A2:A10からD1の値を探し、範囲B2:B10から対応する値を返し、見つからない場合には「見つかりません」と表示します。

まとめ

VLOOKUP関数は、Excelでデータを効率的に扱うための強力なツールです。現場の人間にとっては、新入社員にはぜひ知っておいてほしい関数です。

VLOOKUP関数を使いこなすことで、Excelの活用範囲が大きく広がり、データ分析や集計作業が効率化されます。ぜひ、日々の業務をより効果的に進めるために、この関数を活用してください。

ポイントをおさらいすると：

• 時間短縮: 手作業でデータを探す時間を大幅に削減できます。
• ミス防止: 手作業での入力ミスを減らし、データの正確性を高めます。
• 業務効率化: 複雑なデータ処理を自動化することで、業務全体の効率化に貢献します。

また、VLOOKUP関数の制約や代替案についても理解しておくことで、より柔軟にExcelを活用することができます。INDEX関数とMATCH関数の組み合わせやXLOOKUP関数など、環境や状況に応じて最適な方法を選択してください。