ExcelでネストされたVLOOKUP関数を使う方法(3つの活用シーン)

VLOOKUP関数は、Microsoft Excelの中でも最も強力で柔軟性が高く、実用性の高い関数の一つです。対応する値を検索して、完全一致または近似一致の値を取得することができます。しかし、単一のVLOOKUPだけでは目的を達成できない場合もあります。そんなときは、複数のVLOOKUPを組み合わせることで解決できます。この記事では、ExcelでネストされたVLOOKUP(入れ子構造のVLOOKUP)を実装する方法をご紹介します。

ワークブックのダウンロード

練習用の無料Excelワークブックはこちらからダウンロードできます。

ExcelにおけるVLOOKUPとは

VLOOKUPは「Vertical Lookup(垂直検索)」の略称です。指定した列から特定の値を検索し、同じ行にある別の列の値を返す関数です。

基本構文:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

各引数の意味は以下の通りです。

引数	定義
lookup_value	照合したい値(検索値)
table_array	検索値を探すデータ範囲
col_index_num	lookup_valueに対応する列番号
range_lookup	TRUEまたはFALSEの論理値。 FALSE(または0)は完全一致、TRUE(または1)は近似一致を意味します。

ネストされたVLOOKUPを使う3つのケース

このセクションでは、ネストされたVLOOKUPを活用して、Excelで商品価格と売上金額を取得する方法を解説します。さらに、IFERROR関数とネストされたVLOOKUPを組み合わせる方法もご紹介します。

1. ネストされたVLOOKUPで商品価格を抽出する

以下のデータセットを例に考えます。結果テーブルでは、商品IDに基づいて価格を取得したいのですが、両者はどの単一のテーブルにも一緒には存在しません。IDは表1に、価格は表2にあります。そこで、まず表1でIDを検索し、一致した値をもとに表2から価格を抽出して、結果テーブルの価格列に表示します。

手順:

• 価格を表示したいセルをクリックします(例:結果テーブルのID A101の隣のセルI5)
• 次の数式を入力します。

=VLOOKUP(VLOOKUP(H5, $B$5:$C$9, 2, FALSE), $E$5:$F$9, 2, FALSE)

ここで、

H5 = A101。結果テーブルに入力した検索値(ID)
$B$5:$C$9 = 検索値を探す表1のデータ範囲
$E$5:$F$9 = 検索値を探す表2のデータ範囲
2 = 取得する値がある列番号
FALSE = 完全一致を求めるため、引数をFALSEに設定しています。

• キーボードでCtrl + Shift + Enterを押します。

結果セル(I5)にID A101の価格($50)が表示されます。

• フィルハンドルで下方向にドラッグして数式を残りの行に適用すると、結果テーブル内のすべての商品IDの価格を取得できます。

上の画像のように、ネストされたVLOOKUPの数式ひとつで、結果テーブル内のすべての商品IDの価格を取得できました。

数式の分解:

• VLOOKUP(H5, $B$5:$C$9, 2, 0)
  • 出力:「Football」
  • 説明: H5 = A101をデータ範囲$B$5:$C$9内で検索し、列番号2(B列)の値を、FALSEによる完全一致で取得することで、商品名「Football」が得られます。

• VLOOKUP(VLOOKUP(H5, $B$5:$C$9,2,0), $E$5:$F$9, 2, 0) → 変換後
  • VLOOKUP("Football", $E$5:$F$9, 2, 0)
  • 出力: $50
  • 説明: 「Football」をデータ範囲$E$5:$F$9内で検索し、完全一致(FALSE)で価格$50を取得します。

2. ネストされたVLOOKUPで売上金額を取得する

続いて、以下のデータセットを例に考えます。結果テーブルでは、商品名に基づいて売上金額を取得します。まず表1で商品名を検索し、一致した値をもとに表2から売上金額を抽出して、結果テーブルの売上列に表示します。

手順:

• 売上金額を表示したいセルをクリックします(例:結果テーブルの商品Footballの隣のセルJ5)
• 次の数式を入力します。

=VLOOKUP(VLOOKUP(I5,$B$5:$C$9,2,0),$E$5:$G$9,2,0)

ここで、

I5 = Football。結果テーブルに入力した検索値(商品名)
$B$5:$C$9 = 検索値を探す表1のデータ範囲
$E$5:$G$9 = 検索値を探す表2のデータ範囲
2 = 取得する値がある列番号
0 = 完全一致を求めるため、引数を0(FALSE)に設定しています。

• キーボードでCtrl + Shift + Enterを押します。

結果セル(J5)にFootballの売上金額($1,000)が表示されます。

• フィルハンドルで下方向にドラッグして数式を残りの行に適用すると、結果テーブル内のすべての商品の売上金額を取得できます。

上の画像のように、ネストされたVLOOKUPの数式ひとつで、結果テーブル内のすべての商品の売上金額を取得できました。

数式の分解:

• VLOOKUP(I5, $B$5:$C$9, 2, 0)
  • 出力:「David」
  • 説明: I5 = Footballをデータ範囲$B$5:$C$9内で検索し、列番号2(B列)の値を、0による完全一致で取得することで、営業担当者名「David」が得られます。

• VLOOKUP(VLOOKUP(I5, $B$5:$C$9, 2, 0), $E$5:$G$9, 2, 0) → 変換後
  • VLOOKUP("David", $E$5:$G$9, 2, 0)
  • 出力: $1,000
  • 説明: 「David」をデータ範囲$E$5:$G$9内で検索し、完全一致(0)で売上金額$1,000を取得します。

3. ネストされたVLOOKUPとIFERROR関数の組み合わせ

このセクションでは、ネストされたVLOOKUPとIFERROR関数を組み合わせて、1つの検索値をもとに複数のテーブルから特定の結果を抽出する方法を見ていきます。

以下のデータセットでは、商品が3つの異なるテーブルに分かれています。これら3つのテーブルの中から、ID(A106)を手がかりに特定の商品を検索し、結果テーブルの商品列に表示します。

手順:

• 商品名を表示したいセルをクリックします(例:結果テーブルのID A106の隣のセルL5)
• 次の数式を入力します。

=IFERROR(VLOOKUP(K5,$B$5:$C$7,2,0),IFERROR(VLOOKUP(K5,$E$5:$F$7,2,0),VLOOKUP(K5,$H$5:$I$7,2,0)))

ここで、

K5 = A106。結果テーブルに入力した検索値(ID)
$B$5:$C$7 = 検索値を探す表1のデータ範囲
$E$5:$F$7 = 検索値を探す表2のデータ範囲
$H$5:$I$7 = 検索値を探す表3のデータ範囲
2 = 取得する値がある列番号
0 = 完全一致を求めるため、引数を0(FALSE)に設定しています。

• キーボードでCtrl + Shift + Enterを押します。

結果セル(L5)に、検索ID A106に対応する商品名(Cricket Bat)が表示されます。

数式の分解:

• VLOOKUP(K5, $H$5:$I$7, 2, 0)
  • 出力: #N/A
  • 説明: K5 = A106をデータ範囲$H$5:$I$7内で検索し、列番号2で完全一致する商品ID A106を探します。しかし、$H$5:$I$7(表3)にはID A106の一致がないため、#N/Aエラーが返されます。

• VLOOKUP(K5, $E$5:$F$7, 2, 0)
  • 出力:「Cricket Bat」
  • 説明: K5 = A106をデータ範囲$E$5:$F$7内で、列番号2(FALSE)による完全一致で検索します。この範囲(表2)にID A106の一致が見つかったため、「Cricket Bat」が返されます。

• IFERROR(VLOOKUP(K5, $E$5:$F$7, 2, 0),VLOOKUP(K5, $H$5:$I$7, 2, 0)) → 変換後
  • IFERROR("Cricket Bat", #N/A)
  • 出力:「Cricket Bat」
  • 説明: IFERROR関数は数式の結果をチェックし、エラーが返された場合に、ユーザーが指定した別の値を返します。

• VLOOKUP(K5, $B$5:$C$7, 2, 0)
  • 出力: #N/A
  • 説明: K5 = A106をデータ範囲$B$5:$C$7内で検索し、列番号2で完全一致する商品ID A106を探します。しかし、$B$5:$C$7(表1)にはID A106の一致がないため、#N/Aエラーが返されます。

• IFERROR(VLOOKUP(K5,B5:C7,2,0),IFERROR(VLOOKUP(K5,E5:F7,2,0),VLOOKUP(K5,H5:I7,2,0))) → 変換後
  • IFERROR(#N/A, "Cricket Bat")
  • 出力:「Cricket Bat」
  • 説明: IFERROR関数は数式の結果をチェックし、エラーが返された場合に、ユーザーが指定した別の値を返します。

注意点

• 値を検索するデータテーブル配列の範囲は固定されているため、配列テーブルのセル参照の前にドル記号($)を付けることを忘れないでください。
• 配列値を扱う際は、結果を抽出するときにキーボードでCtrl + Shift + Enterを押すことを忘れないでください。Enterのみの入力で動作するのは、Microsoft 365を使用している場合だけです。

まとめ

この記事では、ExcelでネストされたVLOOKUPを3つの異なるケースで使用する方法について詳しく解説しました。この記事が皆さんのお役に立てば幸いです。このトピックに関するご質問があれば、お気軽にお尋ねください。

関連記事

• ExcelでVLOOKUPとSUM関数を組み合わせて使う方法(6つの方法)
• ExcelのVLOOKUPで最も近い値を検索する方法(5つの例)
• ExcelでVLOOKUPを大文字小文字を区別させる方法(4つの方法)
• Excelで複数条件のVLOOKUPを使う方法(6つの方法+代替案)
• Excel VBAでVLOOKUP関数を使う方法(4つの例)