Need formula to pull in names associated with RACI roles.

r0030

I have a Project Plan template that I would like my team members to save as new for each client and customize the information. Within the Project Plan, there's a RACI matrix with different roles. Please see below for an example:

When a new client is assigned and team members save the project template as new, I'd like to be able to automatically populate the employee names associated with the RACI roles. I started to create a reference sheet below, but not sure what function I'll use to pull in the names.

Any guidance is appreciated!

Julio S.

Hi @rmc0030,

It looks like using INDEX(MATCH()) formulas should accomplish what you intend once you have filled each pair of client names and specific roles as shown below. 

To bring out each of the relevant employees assigned to a client and role, you can use formulas in line with: =INDEX({PMO Project Lead}, MATCH(Clients@row, {Clients})). If you are looking to bring out multiple roles for each Column in a multi-select dropdown, you may want to use CHAR(10) to separate different formulas for each role so that these can form separate dropdown values. If the "Client Column" isn't present in each Project sheet, you may substitute MATCH(Clients@row, {Clients})) with MATCH(Clients@row, "Name of the relevant Client")) for example

FORMULA:

*Note that each reference between {} is a cross sheet reference to the columns with the same name in the source.

RESULT:

Since each Column seems to have role inconsistent role assignations, it looks like it might be less time consuming simply updating each Dropdown column with all names from the matrix and manually selecting them rather by using formulas. Otherwise, you will need to manually insert the relevant formula for each role assignment combination.

I hope this can offer some guidance that can help you with what you are trying to achieve.

Cheers!

Julio

Julio S.

Hi @rmc0030,

You may try converting your RACI columns to multi select dropdown columns and using the HAS() function. Then adding "+" to separate each IF statement and CHAR(10) to separate each returned value. Note, however, that the results will be returned as text instead of contacts, even if the lookup values under "reference" are actual Contacts.

I hope that this can be helpful.

Cheers!

Julio

Julio S.

Hi @rmc0030,

It looks like using INDEX(MATCH()) formulas should accomplish what you intend once you have filled each pair of client names and specific roles as shown below. 

To bring out each of the relevant employees assigned to a client and role, you can use formulas in line with: =INDEX({PMO Project Lead}, MATCH(Clients@row, {Clients})). If you are looking to bring out multiple roles for each Column in a multi-select dropdown, you may want to use CHAR(10) to separate different formulas for each role so that these can form separate dropdown values. If the "Client Column" isn't present in each Project sheet, you may substitute MATCH(Clients@row, {Clients})) with MATCH(Clients@row, "Name of the relevant Client")) for example

FORMULA:

*Note that each reference between {} is a cross sheet reference to the columns with the same name in the source.

RESULT:

Since each Column seems to have role inconsistent role assignations, it looks like it might be less time consuming simply updating each Dropdown column with all names from the matrix and manually selecting them rather by using formulas. Otherwise, you will need to manually insert the relevant formula for each role assignment combination.

I hope this can offer some guidance that can help you with what you are trying to achieve.

Cheers!

Julio

r0030

Thanks so much, Julio! This was incredibly helpful.

r0030

Hi @Julio S. - circling back to this as it has evolved a bit. :) Instead of having a separate column per row like in the previous screenshot, I've created a small stakeholder list at the top of the project plan. See below:

The Reference column shown above (Person 1, Person 2) is a contact column that we'll use to record team members once they're assigned to a project.

I then created 4 helper columns (R,A,C,I) where the role is specified. See below:

Lastly, I created 4 actual RACI columns (R-Assigned To, A-Assigned To, etc.) and inserted a formula to pull in the contact associated with the role notated in the RACI helper column.

This worked perfectly for this particular use case. However, I'm now realizing that there could potentially be multiple roles in the R,A,C,I helper columns, and my formula does not pull in multiple contacts in my actual RACI columns.

I've tried every INDEX/MATCH and VLOOKUP formula I can find, but am totally stumped. Do you know how I can accomplish pulling in multiple contacts based on the role & stakeholder list within my project plan?

Julio S.

Hi @rmc0030,

You may try converting your RACI columns to multi select dropdown columns and using the HAS() function. Then adding "+" to separate each IF statement and CHAR(10) to separate each returned value. Note, however, that the results will be returned as text instead of contacts, even if the lookup values under "reference" are actual Contacts.

I hope that this can be helpful.

Cheers!

Julio