Generate worksheet name based on cell lookup across multiple worksheets

[Darren_workforce]

Hello,

Apologies as I think this requires an INDIRECT formula and I struggle with those.

I'm hoping to have an employee lookup across our lines of business. If the employee name is added into B3 of tab 'Lookup', it would search Column A of all other tabs and if the employee name does appear in any worksheets, those worksheet names would generate in the 'Lookup' range B6:Bxx.

 

[Darren_workforce]

=INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$a$1:$a$50"),B3)>0),0))

I attempted to use the above formula. The worksheets are just listed as they appear down the primary tab and grouped as SheetList. I had to research this. While it does work, it only generates the first instance where the employee name appears in a tab. As some employees may be assigned to multiple queues, I would need additional tab names to populate. What is my formula missing?