空白以外のセルを複数抽出して表示させる方法

質問

[シート１]
　　Ａ　Ｂ　Ｃ　Ｄ　Ｅ　Ｆ
１　＿　ａ　ｂ　ｃ　ｄ　ｅ
２　ア　あ　　　い
３　イ　　　　　　　　　う
４　ウ
５　エ　　　　　　　え
６　オ　お　　　　　か

↓

[シート２]
　　Ａ　Ｂ　C D E　Ｆ
1　ア　a　あ
2　＿ ｃ い
3　
4
5　
6　

シート１で入力したものを、シート２で上記のように抽出したいのですが、どのようにすればいいのでしょうか？
シート２ではセルＡ１をカウンターボックスにして、値が変わるとＢ、Ｃの結果も変わるようにしたいです。
関数やオートフィルタは使用せず関数のみで出来る方法はありますでしょうか。
宜しくお願い致します。

回答No.1

MackyNo1

データベースシートがSheet1なら以下の数式を入力して下方向にオートフィルしてください。

B1セル
=INDEX(Sheet1!$1:$1,,SMALL(INDEX((INDEX(Sheet1!$B$1:$F$10,MATCH($A$1,Sheet1!$A:$A,),)="")*100+COLUMN(Sheet1!$B$1:$F$1),),ROW(A1)))&""

C1セル
=INDEX(Sheet1!$1:$10,MATCH($A$1,Sheet1!$A:$A,),SMALL(INDEX((INDEX(Sheet1!$B$1:$F$10,MATCH($A$1,Sheet1!$A:$A,),)="")*100+COLUMN(Sheet1!$B$1:$F$1),),ROW(A1)))&""