VLOOKUP to look through multiple sheets and return the sheet names

Hello. I have an excel workbook that has about 15 worksheets in it. Each worksheet contains a parts list for a particular engine build. The first worksheet is a compilation of all of the parts that are used in all 15 of the other individual parts list. What I'm trying to do is determine which worksheet each part is used in.

I've tried using vlookup, but I can't get the function to return the name of the worksheet for which the part resides. And I'm also not able to figure out how to get the function to look at all of the worksheets. the end result I want is to have a list of each of the worksheet names for which the part number on that row is used, separated by commas.

Can you help?

Can you put everything on one sheet and include an additional column to indicate the sheet number, and run a pivot table off of it?

Thanks for your response!

Well, I basically took all of the separate sheets and combined them into one. Most of the parts are actually used in more than one sheet, so I'm trying to get a list of which worksheets they're each in.

And I actually don't know what a pivot table is....

See the attached file. Does your data in the summary sheet look like columns A:B?

See if the approach in the attachment can be applied to your own worbook.