VLOOKUP or Match Formula for cross referencing sheets

Patrick360

I'm trying to create a report that outlines tasks by Department. When we created our project plan sheets, we didn't include a department column and only included an Assigned To column for resources. I do however have an Employee Info sheet that lists all of the employees who can be Assigned To and their corresponding department. Is there a way to cross reference this Employee Info sheet to help me pull all tasks across 7 project plan sheets?

If not, I'm thinking I need to create a Department column for all of the project plans and do a match or vlookup formula to reference from a Project Plan sheet to the Employee Info sheet to fill in a new Department column on the project plan sheet. I tried the following formula but it's not working:

=INDEX({Employee Info_Department_Range}, MATCH([@Assigned To], {Employee Info_Assigned To_Range}, 0))

BullandKhmer

=INDEX({Employee Info_Department_Range}, MATCH([Assigned To]@row, {Employee Info_Assigned To_Range}, 0))

Patrick360

Thanks for helping with the formula. I think my issue is that the two sheets are in different workspaces. The Employee Info sheet is in an HR workspace and the various project plans are each in separate team workspaces. I'd prefer to keep one HR sheet so we don't have to update across 8 different project workspaces which I understand may not be possible.