ExcelのVLOOKUP関数で複数の値を返す8つの方法【関数・フィルター活用】

別のワークシートや同じシート内から値を取り出したいとき、真っ先に思い浮かぶのがExcelのVLOOKUP関数ではないでしょうか。しかし、VLOOKUP関数には大きな弱点があります。それは一度に返せる値が1つだけという点です。この関数を使って複数の値を検索・抽出するには、ひと工夫が必要になります。本記事では、その具体的な方法を順を追って解説します。

また、INDEX、SMALL、MATCH、ROW、COLUMNSなどの他のExcel関数を組み合わせれば、複数の値を柔軟に抽出できます。さらに、詳細フィルター（Advanced Filter）、オートフィルター、テーブルとして書式設定といったExcel標準機能を活用する方法もあります。このチュートリアルでは、VLOOKUP関数を使う方法1つ、他の関数を組み合わせる方法4つ、Excelの機能・オプションを使う方法3つの合計8つの方法を紹介します。

練習用のExcelファイルをダウンロードして、実際に手を動かしながら学ぶことをおすすめします。

ExcelでVLOOKUPして複数の値を返す8つの方法

このチュートリアルでは、全8つの方法の説明を通じて社員データベース（Employee Database）を使用します。

それでは、さっそく各方法をひとつずつ見ていきましょう。

方法1：VLOOKUP関数だけで複数の値を返す

VLOOKUP関数は一度に1つの値しか返せないことは前述の通りですが、データセットを少し工夫すれば、VLOOKUP関数単体でも複数の値を返すことが可能です。ここではCOUNTIF関数とVLOOKUP関数を組み合わせます。以下の手順に従ってください。

🔗 手順：

まず、すべての部署名を一意（ユニーク）なものにする必要があります。そのために、

❶ セルE5を選択します。

❷ 次の数式を入力します。
=B5&COUNTIF(B5:B$13,B5)

❸ Enterキーを押します。

❹ フィルハンドルを下方向へドラッグし、「Dept._Unique」列全体にコピーします。

❺ セルC16を選択し、次の数式を入力します。
=VLOOKUP(B16,E5:F13,2,0)

❻ Enterキーを押します。

❼ 最後に、フィルハンドルを「Employee」列の末尾までドラッグします。

これで完成です。

方法2：INDEX・SMALL・MATCH・ROWで縦方向に複数の値を抽出する

例えば、「Engineering（エンジニアリング）」部署に所属する社員名をすべて抽出し、列方向（縦）に並べたいとします。その場合は、INDEX、SMALL、MATCH、ROWの各関数を組み合わせます。以下の手順で実行できます。

🔗 手順：

❶ まず、結果を表示するセルF5を選択します。

❷ 次の数式を入力します。
=INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROWS($A$1:A1)))

❸ Enterキーを押します。

❹ フィルハンドルを「Employee」列の下方向へドラッグします。

␥ 数式の解説

• ROW($B$5:$B$13) ▶ 行番号を配列形式で返します：{5;6;7;8;9;10;11;12;13}
• MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) ▶ 上記の配列を連番に変換します：{1;2;3;4;5;6;7;8;9}
• IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ 条件に一致する行番号を返し、一致しない場合は空文字を返します：{"";2;"";4;"";"";"";"";9}
• SMALL(...) ▶ 配列内の小さい方から順番に数値を取り出します。
• INDEX($C$5:$C$13, ...) ▶ SMALL関数が返した行インデックス番号をもとに、該当する社員名を返します。

方法3：横方向（行方向）に複数の値を返す

今度は、「Engineering」部署の社員名を行方向（横）に並べて抽出したい場合の方法です。使用する関数はINDEX、SMALL、MIN、ROWです。

🔗 手順：

❶ まず、結果を表示するセルC16を選択します。

❷ 次の数式を入力します。
=INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, ""), COLUMNS($A$1:A1)))

❸ Enterキーを押します。

❹ フィルハンドルを「Employee」行の右方向へドラッグします。

␥ 数式の解説

• ROW($B$5:$B$13) ▶ 行番号を配列形式で返します：{5;6;7;8;9;10;11;12;13}
• SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, "")) ▶ 配列内の小さい方から順番に数値を取り出します。
• INDEX($C$5:$C$13, ...) ▶ SMALL関数が返した行インデックス番号をもとに、該当する社員名を返します。

方法4：複数の条件を指定して複数の値を取得する

このセクションでは、「Engineering」部署に所属しており、かつ「Morning（朝）」シフトで勤務している社員名をすべて抽出します。使用する関数はINDEX、SMALL、IFERROR、ROWです。

🔗 手順：

❶ まず、結果を表示するセルH5を選択します。

❷ 次の数式を入力します。
=IFERROR(INDEX($C$5:$C$13,SMALL(IF(1=((--($F$5=$B$5:$B$13)) *(--($G$5=$D$5:$D$13))), ROW($C$5:$C$13)-4,""), ROW()-4)),"")

❸ Enterキーを押します。

❹ フィルハンドルを「Employee」列の下方向へドラッグします。

␥ 数式の解説

この数式は前の2つの方法とよく似ています。主な違いは以下の通りです。

• ROW($C$5:$C$13)-4 ▶ 配列内の行番号を返します：{5;6;7;8;9;10;11;12;13}。ここでの「4」は、列見出し「Employee」がある行番号を指します。
• ROW()-4 ▶ ここでの「4」は、出力行の直前の行番号を指します。
• IFERROR ▶ エラーが発生した場合に、カスタマイズした出力を表示するために使用しています。

方法5：TEXTJOINで複数の値を1つのセルにまとめる

次に、「Engineering」部署に所属するすべての社員名を、1つのセルにまとめて表示する方法を紹介します。使用する関数はTEXTJOINとIFです。

🔗 手順：

❶ まず、結果を表示するセルH5を選択します。

❷ 次の数式を入力します。
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))

❸ Enterキーを押します。

これで完成です。

␥ 数式の解説

ここでは、IF関数が条件に一致するすべての社員名を返し、TEXTJOIN関数がその社員名をカンマ区切りで1つのセルに結合しています。

関連記事：Excelで1つのセルにVLOOKUPで複数の値を返す方法（2つの簡単な方法）

方法6：オートフィルターを使って複数の値を抽出する

Excelの数式を使いたくない場合は、オートフィルター機能を使えば、数式なしでデータテーブルから複数の値を簡単に抽出できます。ここでは、「Engineering」部署に所属する社員名をすべて絞り込みます。

🔗 手順：

❶ まず、データテーブル全体を選択します。

❷ リボンの「データ」タブに移動します。

❸ 「フィルター」をクリックします。

❹ 「Department（部署）」列見出しの下にあるフィルターアイコンをクリックします。

❺ ポップアップメニューから「Engineering」にチェックを入れます。

❻ 「OK」ボタンをクリックします。

これで完了です。結果は次のように表示されます。

方法7：詳細フィルター（Advanced Filter）を使って複数の値を抽出する

Excelには詳細フィルター（Advanced Filter）という機能があり、データテーブルを縦方向に検索して、複数の値を一度に抽出することができます。次の例では、「Department」列から「Engineering」を条件として設定し、データテーブル全体から該当する社員を検索します。手順は以下の通りです。

🔗 手順：

❶ まず、データテーブル全体を選択します。

❷ リボンの「データ」タブに移動します。

❸ 「詳細設定（Advanced）」をクリックします。

❹ 「フィルターオプションの設定（Advanced Filter）」ダイアログボックスが表示されます。

❺ 「リスト範囲（List range）」欄に $B$4:$C$13 を、「検索条件範囲（Criteria range）」欄に 'Advanced Filter'!$E$4:$E$5 を入力します。

❻ 「OK」ボタンをクリックします。

すべての手順が完了すると、次のような出力が得られます。

方法8：「テーブルとして書式設定」を使って複数の値を取得する

この方法は、オートフィルターや詳細フィルターの代わりとして使えます。「テーブルとして書式設定」機能を使って、「Engineering」カテゴリに該当する社員名をすべて絞り込みます。手順は以下の通りです。

🔗 手順：

❶ まず、データテーブル全体を選択します。

❷ リボンの「ホーム」タブに移動します。

❸ 「テーブルとして書式設定」を選択します。

❹ お好みのテーブルスタイルを選択します。

すると、データテーブルは次のような見た目になります。

❺ 「Department（部署）」列見出しの下にあるフィルターアイコンをクリックします。

❻ ポップアップメニューで「Engineering」のみにチェックを入れます。

❼ 「OK」ボタンをクリックします。

すべての手順が完了すると、結果は次のようになります。

注意点

📌 VLOOKUP関数単体では、複数の値を返すことはできません。

📌 各関数の構文（書式）には十分注意してください。

📌 数式にデータ範囲を入力する際は、範囲指定を慎重に行いましょう。

まとめ

今回は、ExcelでVLOOKUPのように複数の値を検索・抽出するための8つの異なる方法を紹介しました。記事に添付されている練習用ワークブックをダウンロードして、すべての方法を実際に試してみることをおすすめします。ご不明な点があれば、下のコメント欄でお気軽にお尋ねください。できるだけ早く回答いたします。

関連記事

• Excelで複数行に対してVLOOKUPを実行する方法（5つの方法）