メインコンテンツへスキップ

Vlookup を実行し、XNUMX つまたは複数の基準に基づいて複数の値を返す

通常、Vlookup関数を使用して最初の対応する値を取得できますが、特定の基準に基づいて一致するすべてのレコードを返したい場合もあります。 この記事では、vlookupして、一致するすべての値を垂直方向、水平方向、またはXNUMXつのセルに返す方法について説明します。

Vlookupを実行し、対応するすべての値を垂直方向に返します

Vlookupを実行し、対応するすべての値を水平方向に返します

Vlookupを実行し、対応するすべての値をXNUMXつのセルに返します

Vlookupを実行し、対応するすべての値を垂直方向に返します

特定の基準に基づいてすべての一致する値を垂直方向に返すには、次の配列数式を適用してください。

1。 この数式を入力するか、結果を出力する空白のセルにコピーします。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Note：上記の式では、 C2：C20 列には、返す一致するレコードが含まれています。 A2：A20 列には基準が含まれています。 そして E2 に基づいて値を返したい特定の基準です。 必要に応じて変更してください。

2。 次に、 Ctrl + Shift + Enter キーを一緒に押して最初の値を取得し、塗りつぶしハンドルを下にドラッグして、必要に応じて対応するすべてのレコードを取得します。スクリーンショットを参照してください。

ヒント：

Vlookup を実行し、より具体的な値に基づいて一致するすべての値を垂直方向に返すには、以下の式を適用して、 を押してください。 Ctrl + Shift + Enter キー。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Vlookupを実行し、対応するすべての値を水平方向に返します

一致する値を水平方向に表示したい場合は、以下の配列数式が役立ちます。

1。 この数式を入力するか、結果を出力する空白のセルにコピーします。

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Note：上記の式では、 C2：C20 列には、返す一致するレコードが含まれています。 A2：A20 列には基準が含まれています。 そして F1 に基づいて値を返したい特定の基準です。 必要に応じて変更してください。

2。 次に、 Ctrl + Shift + Enter キーを一緒に押して最初の値を取得し、塗りつぶしハンドルを右にドラッグして、必要に応じて対応するすべてのレコードを取得します。スクリーンショットを参照してください。

ヒント：

Vlookup を実行し、より具体的な値に基づいて一致するすべての値を水平方向に返すには、以下の式を適用して、 Ctrl + Shift + Enter キー。

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Vlookupを実行し、対応するすべての値をXNUMXつのセルに返します

vlookupして、対応するすべての値をXNUMXつのセルに返すには、次の配列数式を適用する必要があります。

1。 以下の数式を空白のセルに入力またはコピーします。

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Note：上記の式では、 C2：C20 列には、返す一致するレコードが含まれています。 A2：A20 列には基準が含まれています。 そして F1 に基づいて値を返したい特定の基準です。 必要に応じて変更してください。

2。 次に、 Ctrl + Shift + Enter キーを合わせて、一致するすべての値をXNUMXつのセルにまとめます。スクリーンショットを参照してください。

ヒント：

単一のセル内のより具体的な値に基づいて一致するすべての値を Vlookup して返すには、以下の式を適用して、 Ctrl + Shift + Enter キー。

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

注： この数式は、Excel2016以降のバージョンでのみ正常に適用されました。 Excel 2016をお持ちでない場合は、ご覧ください こちら それを降ろすために。

より相対的なVlookupの記事：

  • VlookupとExcelのXNUMXつの値の間の一致するデータを返す
  • Excelでは、通常のVlookup関数を適用して、特定のデータに基づいて対応する値を取得できます。 しかし、場合によっては、vlookupして、XNUMXつの値の間で一致する値を返したいことがあります。このタスクを、Excelでどのように処理できますか？
  • Vlookupとドロップダウンリストから複数の値を返す
  • Excelで、ドロップダウンリストから複数の対応する値をvlookupして返すにはどうすればよいですか。つまり、ドロップダウンリストからXNUMXつの項目を選択すると、次のスクリーンショットのようにすべての相対値が一度に表示されます。 この記事では、ソリューションを段階的に紹介します。
  • Excelで0またはN / Aではなく空白を返すVlookup
  • 通常、vlookup関数を適用して対応する値を返す場合、一致するセルが空白の場合は0が返され、一致する値が見つからない場合はエラー＃N / A値が返されます。 0または＃N / Aの値を表示する代わりに、空白のセルを表示するにはどうすればよいですか？
  • Excelテーブルから複数の列を返すVlookup
  • Excelワークシートでは、Vlookup関数を適用して、XNUMXつの列から一致する値を返すことができます。 ただし、次のスクリーンショットに示すように、複数の列から一致する値を抽出する必要がある場合があります。 Vlookup関数を使用して、複数の列から対応する値を同時に取得するにはどうすればよいですか？
  • 複数のワークシートにわたるVlookup値
  • Excelでは、vlookup関数を簡単に適用して、ワークシートのXNUMXつのテーブルに一致する値を返すことができます。 しかし、複数のワークシートで値をルックアップする方法を考えたことはありますか？ データの範囲を含む次のXNUMXつのワークシートがあるとします。次に、これらXNUMXつのワークシートの基準に基づいて、対応する値の一部を取得します。
  • スーパーフォーミュラバー （複数行のテキストと数式を簡単に編集できます）; 読書レイアウト （多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
  • セル/行/列をマージする およびデータの保持。 分割セルコンテンツ; 重複する行と合計/平均を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 （すべてのセルは空です）; スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • お気に入りの数式をすばやく挿入する、範囲、チャート、写真; セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター （フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • ピボットテーブルのグループ化 週番号、曜日など... ロック解除された、ロックされたセルを表示する さまざまな色で; 式/名前を持つセルを強調表示する...
kteタブ201905
  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations