助けてください。VLOOKUPやOFFSETでは解決できません（＞＜）

質問

助けてください。。
仕事でレシピを作成しているのですが、
メニュー名や材料、手順をデータとして入力をしていて、その中から必要に応じて3つぐらいのメニューを選んで別シートにレシピを作成をしています。
今までは、コピー、貼り付けをして対応してましたが、仕事量が増え、負担になってきているので、なんとか数式で簡単にデータを引っぱってこれないかなあと思っています。

データの入力は↓のようにしてあります。

　　　A　　　　　　　　　B　　　　　　　C

1　メニュー名　　　　　材料　　　　　　手順
2　ぶりしゃぶ　　　　　ぶり（薄切り）　(1)ぶりは
3　ぶりしゃぶ　　　　　ほうれん草　　　(2)次に
4　ぶりしゃぶ　　　　　酒　　　　　　　(3)ほうれ
5　ぶりしゃぶ　　　　　ポン酢　　　　　(4)最後に
6　きのこのホットパイ　きのこ　　　　　(1)パイシ
7　きのこのホットパイ　パイシート　　　(2)きのこ
8　きのこのホットパイ　ホワイトソース　(3)鍋に、　
9　きのこのホットパイ　白ワイン　　　　(4)オーブン
10　きのこのホットパイ　玉ねぎ　　　　　(5)皿に　
.
.
.
.

別シートには↓のように、A1セルにメニュー名を入力するとA4、B4以下に自動で入力されるようにしたいです。
材料は、4つとは限らず、2個の事も10個のこともあります。手順も同様です。また、材料の個数＝手順の行程数とは限りません。材料は2個なのに、手順は4行程ということもあります。

　　A　　　　　　　　　B
1　ぶりしゃぶ
2
3
4　ぶり（薄切り）　(1)ぶりは
3　ほうれん草　　　(2)次に
4　酒　　　　　　　(3)ほうれ
5　ポン酢　　　　　(4)最後に

VLOOKUPやOFFSETを使用して作ってみたのですが、うまくいきませんでした。HITする文字列が複数ある場合は、どのようにすればいいのでしょうか。。
是非ご教授頂ければと思っております。
よろしくお願い致します☆

回答No.2

shiotan99

こんにちは～

データシートが Sheet1
レシピシートが Sheet2 だとします。

■ 作業列を使わない方法 ■
Sheet2 の A4セルに
=IF(COUNTIF(Sheet1!$A$1:$A$100,$A$1)<ROW(A1),"",INDEX(Sheet1!B$1:B$100,100-LARGE(INDEX((Sheet1!$A$1:$A$100=$A$1)*100-ROW($A$1:$A$100),0),ROW(A1))))

と入れ（ ↑をそのままコピー＆ペーストしてください ）、
B4セルにフィルコピー
A4：B4 を必要なだけ下にフィルコピーしてください。

◆ データシートの最終行が 100行目と仮定しています。
それ以上ある場合は、数式の 100を修正してください。

■ 作業列を使う方法 ■

作業列はどの列でもかまいません。
ここでは仮に Sheet1 の F列を作業列にします。

Sheet1 の F2セルに
=IF(AND(Sheet2!$A$1<>"",A2=Sheet2!$A$1),ROW(),"")

と入れ、データ最終行までフィルコピー
今後もデータが増えつづけるなら多めにコピーしておいてください。

Sheet2 の A4セルに
=IF(COUNT(Sheet1!$F:$F)<ROW(A1),"",INDEX(Sheet1!B:B,SMALL(Sheet1!$F:$F,ROW(A1))))

と入れて、右の B4セルにフィルコピー
A4：B4 を必要なだけ下にフィルコピーしてください。

◆ 作業列が F列以外なら $F:$F（ 2ヶ所 ）をその列記号にしてください。
◆ 作業列が目障りなら非表示にしてください。
◆ データが多い場合は、作業列を使う方法をおすすめします。

お礼

早速のご回答ありがとうございます！　作業列を追加する方法で、試してみたのですが、うまくいきませんでした（＞＜）
作業列の方は、A1に対応するものに番号がふられてうまくいっているようなのですが、
レシピシートの方に表示されません。
ＩＮＤＥＸの所で、使用する引数を選択してくださいのＢＯＸが出て？？でしたし、ＩＦ関数の偽の部分が＃ＮＵＭ！赤字になってしまいました。私の訂正の仕方が多分おかしかったのだと思います。。

すみませんが、↓のようにしたいので、もう一度ご教授いただけますでしょうか？
よろしくお願い致します☆

シート名は、料理データ最新と料理教室用白紙で、
作業列はＯ、
メニュー名入力箇所はＢ12、
抽出箇所はＢ16～Ｋ16までで、
下に10行ぐらいフィルコピー。