Help with with Lookups etc.

Hi

I’m looking for help as I’ve exhausted my knowledge and cannot sort this out.

I have a spreadsheet with columns A-L.  The sheet goes from A2:L90 (see attachment)

Columns A and B are surname and first name. Columns C and G:H contain the roles held by those people. They may have several roles. A particular role may be in any of those six columns. All the different roles have their own individual empty worksheet, with a tab naming the role, that will hold the data found in the LookUp exercise.

I want to have a macro that enables me to click on a role name (e.g. “Band Member” or “Role A”) from a dropdown list containing all the different roles.

Then I need a function (I have tried HLookup, VLookup and Xlookup with no success, but I’m obviously doing something wrong) that can look across the master spreadsheet, and  identify, say, all band members (or whatever role I click on from the dropdown list) that have that role shown in any of the columns C or G-K.

It then needs to copy those names onto (in this case) the “Band Leader” worksheet (see attachment).

I’m totally flummoxed on how to do this after hours of trying to the best of my limited ability. I would be most grateful to be pointed in the right direction.