Trying to pull PM name from Intake Sheet when Certain Criteria is Met

Jon L

I have tried a handful of different options, index(collect... index(match... tried each a few different ways. I either am completely missing something, or what I want I cannot do, and will need a work around.

I have been editing the PMO Template, and am getting it where I need it. but on the Intake Form named "Shell Project Tracker - Template" Columns "Sub Contractor Contact", "BGIS ITS PreCon Contact", "BGIS Coordinator", "Shell Project Manager" , I have multiple names that I want to assign tasks too at various times in the sheet named "EV Construction Plan Template" Column "Assigned To", as there is a PM from my company, a PM from the engineering company, a PM from the company sub contracting from my company, a coordinator from my company, and a PM from the client company that I need to add to the project plan at various steps along the way. There are over 160 steps in my project plan, and I am not wanting to have to manually enter these names each time, so I have a column in the project plan that has the "title" of each "type" of person. I want to pull the persons name from the intake form for the specific project when the column specifies that "type" of person.

Hopefully this makes some sense. My sheets are currently not as clean as I would like as I have been trying many different options. Once I find a solution that will work I will put everything back the way it is meant to be. The names in the intake form I will actually make contacts once I get the formula figured out, right now I am just trying to get it to pull the name that exists.

See Screenshots Attached. First 2 are the intake form.

Any Help is Appreciated!

Andrée Starå

Hi @Jon L

I hope you're well and safe!

• You'd use an INDEX/MATCH formula, but because you have multiple different contact columns, you'd need to have a formula that checks the first range, the second, the next, and so on.
• My recommendation would instead be to create a so-called Profile Data section at the top of the Project Sheet and then have one row for each contact and other relevant data, and then you can INDEX/MATCH locally in the sheet with just one range.

Make sense?

Would one of those options work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!