Need Help With Automating Names From One Table To Another If Certain Aspects Match

[keltzjd]

Good morning all! I'm in need of help with this scenario:

I need to have a formula that copies the name in the first table to the 2nd table if the project name matches. I currently have the cell copying the name from table 1 based on table 1's cell (=A1, etc), but the issue I've run into is if I sort the table differently than it's currently set up, my table 2 breaks because it's not accurate since it's pointing to a specific cell. 

It would also need to be for each column in table 1: PM, APM, MAPA, CM1 - CM5

Anyone know how to achieve this?

[#2 · Accepted solution]

@keltzjd 

Not sure this does exactly what you expect as I don't understand what you mean with "if the project name matches" + why your output shows Phase2 only for the various projects:

With Power Query: Unpivot columns PM, AsstPM...CM5

[#3]

Thanks for the response! So it's matching the project names in the "Name" column of the first table, and the "Project" column in the second table, then extracting the "person" assigned in the PM, APM, MAPA, and CM 1 - 5 columns in the first table to the "Assigned" column in the second table. Hopefully this clarifies my goal.

[#4]

I just learned my company now has PowerQuery in Excel. We previously only had PQ in PBI, but now that we have it in Excel, I can achieve this the way you mentioned. Thank you so much!