ExcelのVLOOKUP関数でデータをマッピングする4つの簡単な方法

データのマッピングはExcel作業に欠かせないスキルです。便利なマッピング手法をいくつか身につけておくと、作業時間を大幅に短縮でき、業務フローも改善されます。この記事では、VLOOKUP関数を使ってExcelでデータをマッピングする4つの実用的な方法を解説します。さらに、値のN番目の出現を取得する方法や、VLOOKUP関数でエラーを非表示にするテクニックについてもご紹介します。

練習用ワークブックは以下のリンクからダウンロードできます。

ExcelでVLOOKUPを使ってデータをマッピングする4つの方法

この記事では、VLOOKUP関数をMATCH関数、COUNTIF関数、INDIRECT関数、IF関数と組み合わせて、データをマッピングする方法を解説します。それでは早速見ていきましょう。
ここでは Microsoft Excel 365 のバージョンを使用していますが、お使いの環境に合わせて他のバージョンでも問題なく操作できます。

方法1:VLOOKUP関数だけでデータをマッピングする

まずは最も基本的な方法である、VLOOKUP関数単体でのデータマッピングから始めましょう。
下図のように B4:D14 のセル範囲に、社員のID、氏名、所属部署の一覧データがあるとします。

手順:

• まず、セル G5 に以下の数式を入力します。

=VLOOKUP(G4,B5:D14,3,FALSE)

この数式では、セル G4 が検索対象のID(1008)を参照し、セル範囲 B5:D14 が ID・氏名・所属部署の各列を表しています。

数式の解説:

• VLOOKUP(G4,B5:D14,3,FALSE) → テーブルの左端の列から値を検索し、同じ行内の指定した列から値を返します。ここでは、G4(lookup_value:検索値)を B5:D14(table_array:範囲)の中から探します。次の 3(col_index_num:列番号)は返す値の列位置を示し、最後の FALSE(range_lookup:検索方法)は検索値との完全一致を指定しています。
  • 結果 → Marketing(マーケティング)

最終的な結果は下図のようになります。

方法2:VLOOKUPとMATCH関数を組み合わせたマッピング(双方向VLOOKUP)

2つ目の方法では、VLOOKUP関数とMATCH関数を組み合わせて、特定の行と列が交差する位置にある値を取得します。これは「双方向VLOOKUP(Two-way VLOOKUP)」とも呼ばれるテクニックです。
ここでは、B4:E12 のセル範囲にある売上一覧データを使用します。商品名と、1月・2月・3月それぞれの販売数量が記録されています。

手順:

• まず、商品名と月を選択します。ここでは例として「Television(テレビ)」と「March(3月)」を選びます。
• 次に、セル H6 に以下の数式を入力します。

=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)

ここで、セル H4 と H5 はそれぞれ商品名と月を参照し、B5:E5 は列見出しを表しています。

数式の解説:

• MATCH(H5, B5:E5, 0) → 配列内で指定した値と一致する項目の相対的な位置を返します。H5 はlookup_value(検索値)で、ここでは「3月」を指します。B5:E5 は照合元となるlookup_array(検査範囲)、最後の 0 は省略可能なmatch_type(照合の型)で、完全一致を意味します。
  • 結果 → 4
• VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → 上記より次のようになります
  • VLOOKUP(H4, B6:E10, 4, FALSE) → H4(lookup_value)を B6:E10(table_array)から検索し、4(col_index_num)で指定された列の値を返します。最後の FALSE(range_lookup)は完全一致の指定です。
  • 結果 → 243

最終的な結果は下図のようになります。

方法3:VLOOKUPとCOUNTIF関数を組み合わせたマッピング

Excelでデータをマッピングするもう一つの方法として、VLOOKUP関数の中にCOUNTIF関数を組み込むやり方があります。シンプルで分かりやすいので、以下の手順に従ってください。
ここでは、B4:C11 のセル範囲にあるベストセラー書籍データを使用します。ベストセラーの書籍名と、その価格(USD)が記録されています。

手順:

• まず、セル F5 に以下の数式を入力します。

=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)

この数式では、セル範囲 B5:C9 がベストセラー書籍と価格の列を表し、セル F4 は検索対象の書籍名(ここでは House of Wisdom)を参照しています。

数式の解説:

• COUNTIF(B5:B9,F4) → 指定した条件を満たすセルの個数を範囲内でカウントします。B5:B9 はrange(範囲)引数でベストセラー書籍のリストを指し、F4 は一致した値の出現回数を返すcriteria(条件)引数です。
  • 結果 → 1
• VLOOKUP(F4,B5:C9,2,TRUE) → F4(lookup_value)を B5:C9(table_array)から検索し、2(col_index_num)で指定された列の値を返します。最後の TRUE(range_lookup)は近似一致の指定です。
  • 結果 → 25
• IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → 上記より次のようになります
  • IF(1,25,0) → 条件が満たされているかどうかを判定し、TRUEなら一方の値を、FALSEならもう一方の値を返します。ここでは 1 がlogical_test(論理式)となり、IF関数に 25(value_if_true)を返させます。条件を満たさない場合は 0(value_if_false)が返されます。
  • 結果 → $25

その結果、下図のような表示になります。

方法4:VLOOKUPとINDIRECT関数を組み合わせたマッピング

INDIRECT関数とVLOOKUP関数を組み合わせることでも、Excelでデータをマッピングできます。それでは手順を見ていきましょう。
ここでは、B4:I10 のセル範囲にある食料品リストデータを使用します。同じ商品の価格が、アメリカの3都市別に記録されています。

手順:

• まず、セル B5 を選択し、データタブをクリックして、データの入力規則のドロップダウンを押します。

これにより、データの入力規則ダイアログボックスが開きます。

• 次に、入力値の種類ドロップダウンで「リスト」を選択し、元の値フィールドで B6:B10 のセル範囲を指定して、OKボタンをクリックします。

• 続いて、B6:C10 のセル範囲を選択し、数式タブに移動して、名前の定義オプションをダブルクリックします。

これにより、名前の編集ウィザードが開きます。

• ここで、データ範囲に適切な名前(この場合は Boston)を入力し、OKボタンをクリックします。

同様の手順で、Atlanta の名前付き範囲を定義します。

同じように、Denver の名前付き範囲も定義しましょう。

• 次に、セル C14 に移動し、データタブでデータの入力規則ボタンをクリックします。

• 同様に「リスト」オプションを選択し、下図のように名前付き範囲を入力します。

これで、ドロップダウンから商品名と地域を選択できるようになりました。ここでは例として Tomato(トマト) と Atlanta(アトランタ) を選んでいます。

• その後、セル D14 に以下の数式を入力します。

=VLOOKUP(B14,INDIRECT(C14),2,FALSE)

ここで、セル B14 と C14 はそれぞれ商品名と地域を参照しています。

数式の解説:

• INDIRECT(C14) → 文字列で指定された参照先を返します。ここでは C14 がref_text(参照文字列)引数で、名前付き範囲 Boston を指しています。
• VLOOKUP(B14,INDIRECT(C14),2,FALSE) → B14(lookup_value)を、INDIRECT(C14) で取得した名前付き範囲(table_array)から検索します。2(col_index_num)は返す値の列番号、最後の FALSE(range_lookup)は完全一致の指定です。
• 結果 → $1.2

以上の手順を完了すると、結果は下図のようになります。

さらに、この方法は複数のワークシートにデータが分散している場合にも活用できます。検索値に基づいて、特定のワークシートからデータをマッピングしたいときに非常に便利です。

VLOOKUP関数でN番目の出現を取得する方法

下図のように、B4:D13 のセル範囲に文房具売上リストがあるとします。ここで、「同じ顧客が購入した2番目や3番目の商品を知りたい」というケースを考えてみましょう。この課題を解決するには、COUNTIF関数とVLOOKUP関数を組み合わせます。詳しい手順を見ていきましょう。

手順:

• まず、B列に新しい列を挿入し、見出しを「Helper Column(作業列)」に変更します。そして、セル B5 に以下の数式を入力します。

=C5&COUNTIF($C$5:C5, C5)

📄 注意: 作業列は必ずデータセットの左端に挿入してください。デフォルトではVLOOKUP関数は左から右へ検索するためです。

ここで、セル C5 は氏名(John)を参照し、COUNTIF関数は指定範囲 $C$5:C5 内で John の出現回数をカウントします。最後に、アンパサンド(&)演算子が文字列と数値を結合します。

• 次に、氏名と出現回数を入力します。ここでは例として Julie と 3 を入力し、セル H6 に以下の数式を入力します。

=VLOOKUP(H4&H5, B5:E13, 3, FALSE)

この数式では、セル H4 と H5 がそれぞれ氏名と出現回数を表しています。

以上の手順を完了すると、結果は下図のようになります。

VLOOKUPとIF関数で#N/Aエラーを非表示にする方法

ISNA関数とVLOOKUP関数をIF関数の中で使うことで、無効な検索値が入力された際の#N/Aエラーを非表示にできます。それでは、具体的な手順をご紹介します。

手順:

• まず、セル G5 に以下の数式を入力します。

=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))

この数式では、セル G4 がID(1008)を参照し、セル範囲 B5:D14 が ID・氏名・所属部署の各列を表しています。

数式の解説:

• ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → 値が #N/A かどうかを判定し、TRUEまたはFALSEを返します。G4(lookup_value)を B5:D14(table_array)から検索し、3(col_index_num)で指定された列の値を確認します。最後の FALSE(range_lookup)は完全一致の指定です。
  • 結果 → FALSE
• IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE)) → 上記より次のようになります
  • IF(FALSE, "",VLOOKUP(G4, B5:D14,3,FALSE)) → 条件が満たされているかどうかを判定し、TRUEなら一方の値を、FALSEならもう一方の値を返します。ここでは FALSE がlogical_test(論理式)となり、IF関数にVLOOKUP関数の結果(value_if_true)を返させます。条件を満たす場合は空白 ""(value_if_false)が返されます。
  • 結果 → HR(人事部)

最終的に、下図のような結果が得られます。

さらに、存在しないID番号(ID 1012など)を入力すると、#N/Aエラーの代わりに空白が返されることが、下図のスクリーンショットで確認できます。

練習セクション

各シートの右側には練習用セクションを用意していますので、ぜひご自身で実際に試してみてください。

まとめ

この記事で紹介したVLOOKUP関数を使ったExcelでのデータマッピングの方法が、皆さんのスプレッドシート作業に役立てば幸いです。ご質問やフィードバックがありましたら、コメント欄でお気軽にお知らせください。また、当サイトにはExcel関数に関する他の記事も多数掲載していますので、ぜひ参考にしてみてください。