• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

lookup all the sheet names that contain an item


New Member
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


  • TH1 mat map.xlsx
    542.8 KB · Views: 11
1] What's wrong with your column B formula results?

2] What's your column B expected results?
Last edited:
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.

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),"")



  • TH1 mat map (BY).xlsx
    563.3 KB · Views: 9
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.