スキルアップ・問題解決なら、モーグにおまかせ！

こんばんは、
let関数とかで作成したのですが、うまくいきません！
宜しければご協力お願いします。
 
 
別ブックでシートが１から10まであります。
A1にリストがあり、1から10の変更で、各シートのデータを引っ張りたいのですが、どうしたらよいのでしょうか？
 
=XLOOKUP(B2,[元データ.xlsx]1′!$B:$B,[元データ.xlsx]1′!$AD:$AD,0,0,-1)
　　　　　　　　　　　　　↑
   　　　　　　　　　　　この数字の2か所をリストで変更したいのですが、
 
よろしくお願いします。

INDIRECT関数を噛ませるとことになりますかねぇ。
 
=LET(s,"'[元データ.xlsx]"&A$1&"'!",XLOOKUP(B2,INDIRECT(s&"B:B"),INDIRECT(s&"AD:AD"),0,0,-1))

半平太様
ありがとうございます。
 
元データを立ち上げないとエラーになるので、フルパスをｓに代入しようとしたのですが、うまくいきませんでした。
 
"とかの付け方が悪いのでしょうか？
引き続きよろしくお願いいたします。

右のほうあいているところに必要な列だけすべてまとめて取り出しておいて、その範囲を参照しましょう。
 
例えばZ1に
=HSTACK('E:\Office\Excel\[元データ.xlsx]1:10'!$B1:$B1000,'E:\Office\Excel\[元データ.xlsx]1:10'!$AD1:$AD1000)
 
としておいて、
=XLOOKUP(B2,CHOOSECOLS($Z$1#,$A$1),CHOOSECOLS($Z$1#,$A$1+COLUMNS($Z$1#)/2),0,0,-1)
 
 
取り出さない場合は
=LET(s,HSTACK('E:\Office\Excel\[元データ.xlsx]1:10'!$B1:$B1000,'E:\Office\Excel\[元データ.xlsx]1:10'!$AD1:$AD1000),XLOOKUP(B2,CHOOSECOLS(s,$A$1),CHOOSECOLS(s,$A$1+COLUMNS(s)/2),0,0,-1))

皆様ご協力ありがとうございました。
まだ、上手く出来ていませんが、初めての関数を確認にながら構成してみます。
関数の組み合わせが勉強になって良かったです。
今後もよろしくお願いします。