lookup all the sheet names that contain an item

[utud]

Hello all,
I am new around, and also kind of a newbie to excel.
I have a document with multiple sheets.
On the first column there is a list with all the unique items contained in them.
Starting with column B, I would like to get all the sheet names that contain the individual items, in separate columns.
I have found a way to lookup the sheetnames for each item, but this only get me the first result.
Could anyone provide a solution?

Thank you

 

[bosco_yip]

1] What's wrong with your column B formula results?

2] What's your column B expected results?

 

[utud]

Column B is fine but I would like to see the other sheet names in C, D, so on.
Most of the items show up in more than one sheet.

 

[bosco_yip]

Maybe,

In C2, formula copies across right and down:

=IFERROR(OFFSET(INDIRECT("'"&$B2&"'!A500"),MATCH($A2,INDIRECT("'"&$B2&"'!$A500:A600"),0)-1,AGGREGATE(15,6,COLUMN($A$1:$AZ$1)/(INDIRECT("'"&$B2&"'!A500:AZ500")="R Mat"),COLUMN(A$1))-1),"")

 

[utud]

Sorry,
I wasn't specific enough.
The result I mean to get in columns C-H is similar to the result I get in column B.
Below one example of what I mean with find (of course find only finds the results in the area B2:B498, because the A500:A550 is a spill list.
So basically I would want the formula to return all the sheet names for the all items in column A of sheet "TH1 Master", like in the second picture.




Thanks a lot.