エクセル Vlookupなどで複数の検索結果を表示させる方法

質問

エクセル Vlookupなどで複数の検索結果の表示

下記の様な２シートがあります。

【sheet1】
会社名 営業所1 電話番号1 営業所2 電話番号2 営業所3 電話番号3
A会社 北海道 011-1111-**** 東京 03-1111-**** 大阪 06-9999-****
B会社
C会社
D会社
E会社
・
・
・
・

【sheet2】
会社名 営業所 電話番号
A会社 北海道 011-1111-****
A会社 東京 03-1111-****
A会社 大阪 06-9999-****
C会社 神奈川 00-0000-****
E会社 名古屋 99-9999-****
E会社 岡山 01-2345-****
・
・
・
・

【sheet1】の会社名を検索値として、【sheet2】からそれに該当する全ての営業所・電話番号のデータを横に表示させたい場合は、どうしたら良いでしょうか？
vlookup関数だけでは、１番最初に該当するデータしか引っ張ってきてくれません。。。
ちなみに項目ごとにセルは分かれており会社名は、重複するものがないものとします。
データ件数的には、３万件ほどのデータ量があります。
マクロでも構いません。何かイイ方法はないでしょうか?
ご教授願います。

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ１です。項目が増える場合ですが項目が３つになれば次の式になります。
=IF(COUNTIF(Sheet2!$A:$A,$A2)>=ROUNDUP(COLUMN(A1)/3,0),INDEX(Sheet2!$A:$G,MATCH($A2,Sheet2!$A:$A,0)-1+ROUNDUP(COLUMN(A1)/3,0),MOD(COLUMN(A1)-1,3)+2),"")
項目が増える場合ですが、この式で数値が３になっているところを４項目になれば４に変えます。なお、Ｎｏ１ではINDEX(Sheet2!$A:$C,を今回はINDEX(Sheet2!$A:$G,に変更しています。上の式では６項目まで増加しても変える必要はないでしょう。

お礼

KURUMITOさんのおかげで無事、データが完成しました。
本当に助かりました。
ありがとうございました。

回答No.2

MackyNo1

関数ならB2セルに以下のような数式を入力し、右方向および下方向にオートフィルします。

ただし表示するデータ量が多いと動きが重くなりますので、そのような場合は一部範囲のみ数式を入力してデータ処理したら最後の行以外の数式は「値」に変換しながら作業するとよいと思います。

=INDEX($B:$C,SMALL(INDEX((Sheet2!$A$2:$A$30000<>$A2)*30000+ROW(Sheet2!$A$2:$A$30000),),INT((1+COLUMN(A1))/2)),MOD(COLUMN(B1),2)+1)&""

回答No.1

KURUMITO

ご質問はシート２のデータがあったときにシート１の表をＡ列に会社名があるときにそれを完成させたいとのことでしょうか？
シート２ではＡ列を重点に並べ替えが行われており同じ会社同士が連続した行に表示されているとします。
Ｂ２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(COUNTIF(Sheet2!$A:$A,$A2)>=ROUNDUP(COLUMN(A1)/2,0),INDEX(Sheet2!$A:$C,MATCH($A2,Sheet2!$A:$A,0)-1+ROUNDUP(COLUMN(A1)/2,0),MOD(COLUMN(A1)-1,2)+2),"")

補足

回答ありがとうございます。
きちんと複数の検索結果があっても、全て引っ張ってきてくれました。
感動です！！！
後、引っ張ってくる項目が【営業所】・【電話番号】の他に【住所】、【担当者】など増える場合は、どうしたら良いのでしょう？
全部で５～６項目ぐらいになりそうです。。。