Look up or Match

Copper Contributor

I have a list of names in the main worksheet. Then 20 worksheets with the names of people with specific skills. On the main worksheet i have set up columns with each of the specific skills. I would like to see on the main worksheet a Y in the column for a specific skill if that name is listed on the worksheet with the specific skill. Im sure excel can do this quite easily but im very rusty and cant find that way. (IF, Matches, lookups etc)

2 Replies
TBH and IMHO your workbook is set up backwards. It would be better to have a master list with all the information and then have a 'reporting tab' or just use filtering to show the skills of a specific person. But lets assume this is out of your control because those tabs come from some other app or something. Assuming the tab names match the names in the list you can use INDIRECT to 'build' the link/address to look up but I don't recommend that. Assuming you can 'bracket' the list of tabs (i.e. have a starting tab name and an ending tab name) then you can create a 3D formula that can pull data from all the tabs and do a lookup using that data but is there a cell on each tab with that employee's name?
Can you share a sample of the workbook structure without personal info so we can see the structure and create appropriate formula? if you can't attach here then share a link to it in Onedrive, Sharepoint, or other online sharing site.

@mtarler  Thanks for your reply. Ive inserted a column in each worksheet with Y against each name. Then used VLookUp to return Y if it can see the name. Not elegant put it works with the various worksheets sent. I will use your reply to go learn some more. Thanks again.