エクセルでHLOOKUP関数の選択範囲について

質問

エクセルでHLOOKUP関数を使って、検索したいのですが、

シートは、一覧表のシートと
データが入っているA101、B203、C305、．．．シートは300シートくらいあります。

一覧表のシートには、下のような表になっていて、
　　　　　A列　　Ｂ列　　Ｃ列　　Ｄ列　．．．
　　　　　　　　　1003、1004、1005、1006、．．．
2行目　A101　
3行目　B203
4行目　C305
　　　　　　．
　　　　　　．
　　　　　　．

データのはいっているシート、A101は下の表になっています。
　　　　　Ｂ列　Ｃ列　Ｄ列、・・・、Ｚ列
2行目　1004、1005、1006、．．．
3行目　100、　200、　150、．．．

一覧表のＢ列の2行目には
HLOOKUP(B2、シートA2のＢ2：Ｚ3、2行目、FALSE）
という感じで、シート名をセルＡ２のものを参照にして
探して表示させ、Ｂ列、Ｃ列、Ｄ列の2行目から下の行も
表示させたいのですが、うめくできませんでした。
INDIRECT関数を使ってみましたが、セル範囲が無効という
エラーがでてしまいます。↓こんな感じで入力してみたのですが．．．
SUMPRODUCT((INDIRECT($A2&"!$B$2:$Z$3"))=$B$1,(INDIRECT($A2&"!$B$2:$Z$3")))

1つづつデータを見て手打ちはデータが多く、
どんどんデータが増えていくので
できれば関数を使って表示させたいと思っています。

詳しい方いらっしゃいましたら、どうか教えてください
よろしくお願いします。

回答No.1

KURUMITO · ベストアンサー

一覧表シートのB2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF($A2="","",IF(COUNTIF(INDIRECT($A2&"!B2:Z2"),B$1)=0,"",HLOOKUP(B$1,INDIRECT($A2&"!B2:Z3"),2,FALSE)))

お礼

回答ありがとうございました。

さっそく式を入力してみたしたら、#REF!とういうエラーがでてしまいました．．．

せっかく教えていただいたのに申し訳ないです．．．

補足

お礼欄にエラーがでると記入したのですが、
そのあと、他の人に確認してもらったところ、
シート名の大文字小文字の違いでエラーに
なっていました。すみません。

点数が多かったので、教えていただいて
すごく助かりました。

ありがとうございました。