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

別のワークシートまたは同じワークシート内から値を取得する必要があるときはいつでも、VLOOKUP 関数を考えずにはいられません。 エクセルで。しかし、VLOOKUP の主な問題は 関数は、単一の値を 1 回だけ返すように設計されていることです。そのため、この記事で説明するこの関数で複数の値を vlookup するために、少し独創的な方法を考える必要があります。

INDEX などの他の Excel 関数も使用できます。 、小さい 、マッチ 、行 、列 など、それらを適切に組み合わせて vlookup し、複数の値を返します。 高度なフィルター など、いくつかの Excel 機能も使用できます。 、オートフィルタ 、表としてフォーマット 道具。したがって、このチュートリアルでは合計 1 について説明します VLOOKUP 関数を使用した方法 、4 その他の Excel 関数を使用する方法、および 3 さまざまな Excel ツールとオプションを使用して vlookup し、Excel で複数の値を簡単に返すメソッド。

Excel ファイルをダウンロードして練習することをお勧めします。

Excel で VLOOKUP して複数の値を返す 8 つのメソッド

このチュートリアルでは、従業員データベースを使用します 記事全体で 8 つの方法すべてを示します。

それでは、これ以上の議論はせずに、8 つの方法すべてを 1 つずつ見ていきましょう。

1. Excel で VLOOKUP 関数を使用して複数の値を返す

VLOOKUP 関数は一度に 1 つの値のみを返すことができます。ただし、複数の値を返す必要があります。はい、そうするために利用できる他のオプションがあります。しかし、特に VLOOKUP を使用したい場合 機能、希望を失わないでください。抜け道があります。このセクションでは、VLOOKUP の使用方法を学習します。 データセットを少し調整して複数の値を返す関数。このメソッドで使用する関数は COUNTIF です と VLOOKUP .以下の手順に従ってください:

🔗 手順:

まず、すべての部門名を一意にする必要があります。そのために、

❶ 選択 セル E5 .

❷ 今入力 式

=B5&COUNTIF(B5:B$13,B5)
セル内。

❸ その後、ENTER を押します ボタン。

❹ 塗りつぶしハンドル をドラッグ Dept._Unique までのアイコン

❺ セル C16 を選択します とタイプ 式

=VLOOKUP(B16,E5:F13,2,0)
それと。

❻ その後、ENTER を押します ボタン。

❼ 最後に、フィル ハンドルをドラッグします Employee の最後にあるアイコン

以上です。

2. Vlookup と Excel で垂直方向に複数の値をプル

エンジニアリングの下で働いている従業員の数を調べたいとします。 それらの名前を列に垂直に整理します。その場合は、以下の手順に従ってその方法を学んでください。このメソッドで使用する関数は INDEX です 、小さい 、マッチ 、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)), "") ▶ 条件に一致する行番号を返し、それ以外の場合は null を返します:{“”;2;””;4;””;””;””;””;9}
• SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “ 」) ▶ 配列内の最初の小さい数値を返します:{“”;2;””;4;””;””;””;””;9}
• INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13),MATCH(ROW($B$5:$B$13)), ROW($ B$5:$B$13)), "") ▶ SMALL によって返された行インデックス番号に基づいて従業員名を返します。

3. Vlookup と Excel で水平方向に複数の値を返す

たとえば、エンジニアリング の下で働いている従業員の数を調べたいとします。 それらの名前を水平方向の行に整理します。このメソッドで使用する関数は INDEX です 、小さい ,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 を押します ボタン。

❹ フィル ハンドルをドラッグします。 従業員の右側にあるアイコン 行。

␥ 式の内訳

• 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(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5) :$B$13))+1, “”) ▶ SMALL によって返された行インデックス番号に基づいて従業員名を返します。

4.基準を使用して Excel で Vlookup と複数の値を取得する

このセクションでは、Employee 内のすべての従業員名を返します。 エンジニアリングの下で働くコラム 朝も働いています シフト。このメソッドで使用する関数は INDEX です 、小さい 、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 ▶ ここに IFERROR 関数は、エラーが発生した場合にカスタマイズされた出力を合成するために使用されます。

5. Vlookup と Excel での複数の値の抽出 (すべて 1 つのセル内)

ここで、エンジニアリング部門の下で働く同じセル内のすべての従業員名をまとめます。このメソッドで使用する関数は TEXTJOIN です と IF .

🔗 手順:

❶ まず、選択します セル H5 ▶ 式の結果を保存します。

❷ その後、タイプ 式

=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))
セル内。

❸ 次に ENTER を押します ボタン。

以上です。

␥ 式の内訳

ここでは、 IF 関数は、条件と TEXTJOIN に一致するすべての従業員名を返します 関数は、 IF によって返されたすべての従業員名を結合します 関数。

続きを読む: Excel の 1 つのセルで複数の値を VLOOKUP する方法 (2 つの簡単な方法)

6. AutoFilter を使用して Excel で複数の値を返す Vlookup

Excel の数式を使用したくない場合は、データ テーブルを vlookup し、オートフィルター機能を使用して複数の値を引き出すことができます。ここでは、エンジニアリング部門の下で働くすべての従業員の名前を並べ替えようとします。

🔗 手順:

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

❷ 次に データ に移動します

❸その後、フィルタをクリックします オプション。

❹ Department のすぐ下にあるフィルター アイコンをクリックします。 列ヘッダー。

❺ 今すぐチェック エンジニアリングにマークを付けます ポップアップ メニューからオプションを選択します。

❻ 次に OK を押します ボタン。

ブラボー！あなたはそれで終わりです。結果は次のようになります:

7.高度なフィルターを使用して Excel で Vlookup と複数の値を抽出する

Excel には高度なフィルターと呼ばれる機能があり、データ テーブルを垂直方向に検索して複数の値を同時に抽出するために使用できます。次の例では、Department 列ヘッダーの下にある Engineering 部門を選択しています。これを使用して、データ テーブル全体を検索し、エンジニアリング部門で働く従業員を見つけます。次に、従うべき手順は次のとおりです。

🔗 手順:

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

❷ 次に データ に移動します

❸ その後、Advanced をクリックします。 オプション。

❹ 最後のステップが完了すると、[高度なフィルタ] という名前のダイアログ ボックスが表示されます が画面に表示されます。

❺

を入力してください $B$4:$C$13
リスト範囲内: バーと 'Advanced Filter'!$E$4:$E$5
基準の範囲内: バー。

❻ その後、OK を押すことができます ボタン。

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

8. Format as Table を使用して Excel で複数の値を取得するための Vlookup

AutoFilter の代わりにこのメソッドを使用できます。 と高度なフィルタ オプション。この方法では、Format as Table を使用します。 Engineering の下にあるすべての従業員名を並べ替えるオプション カテゴリー。従うべき手順は次のとおりです。

🔗 手順:

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

❷ 次に ホーム に移動します

❸ その後、[表としてフォーマット] を選択します オプション。

❹ 次に選択します 好きなテーブル スタイルを選択できます。

これで、データ テーブルは次の図のようになります。

❺ この段階で、フィルタ アイコン をクリックします。 部門の下 列ヘッダー。

❻ ポップアップ メニューから、チェック エンジニアリングのみにマークを付けます オプション。

❼ [OK] をクリックします。 ボタン。

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

覚えておくべきこと

📌 VLOOKUP 関数だけで複数の値を取得することはできません。

📌 関数の構文に注意してください。

📌 データ範囲を数式に慎重に挿入してください。

結論

まとめとして、Excel で vlookup して複数の値を返す 8 つの異なるメソッドについて説明しました。この記事に添付されている練習用ワークブックをダウンロードし、それを使用してすべての方法を練習することをお勧めします。以下のコメントセクションで質問をすることを躊躇しないでください.関連するすべてのクエリにできるだけ早く対応するよう努めます。

関連記事

• Excel で複数行の VLOOKUP を実行する方法 (5 つの方法)