Auto Assign Contact Based on another column's content

Mallory McFall

Hello,

I would like help with creating a formula to auto assign someone to a row based on who was originally selected. In this example, I want to manually assign an officer and have the Team Lead & Front Office Support to auto populate answers based on who was selected as an officer.

Both Team Lead & Front Office Support allow for multi contacts within a cell

I pasted my example below and have an example of what I am specifically wanting.

What formula would I need in the Team Lead column to auto populate "Mr. Krabs" when Spongebob is selected as the officer? Additionally, if I picked Patrick Star instead of Spongebob as the officer, have Team Lead auto populate "Sandy Cheeks" ?

Thank you!

David Joyeuse

Hi @Mallory McFall

In your case I would strongly suggest you to have another sheet with Officers within it and Team lead & Front Office Support assigned to each officer.

Many reasons for this:

• It'll make your fomula much easier to grab and understand. Otherwise you'll be stuck in a long list of nested IF.
• It'll help in the future when new officers are added or removed. Same with Team Lead or Front Office support. You won't have to rewrite the whole formula.

Then, just do an INDEX/MATCH or VLOOKUP on the sheet where the officers are stored for both columns.

=INDEX({Team Lead Range},MATCH([Officer Assigned]@row,{Officer Range},0))

=INDEX({Front Office Support},MATCH([Officer Assigned]@row,{Officer Range},0))

Note that you'll always have the same Team Lead and Front Office assigned to the specified officer. But computers don't do randoms.

Mallory McFall

Before I start to develop the actual code, would I need to do this for each officer-team lead relationship or can I list all the officers that are affiliated with each team lead? I have about 30 officers and 7 team leads for what I will be actually using this for. Thank you

David Joyeuse

Just make sure you have a relationship between any officer and a team lead at least.

What you could have for example is a list of all your officers and on the next column list all the team lead possible for this officer. Or just put one as you like.

It mostly depends on what you want to return on your sheet. One team lead for any given officer or two or more team lead for any given officer.