Help with Automatically Assigning Contacts to Tasks

Taylor Riley

Hi!

I'm attempting to use the below index/match formula to automatically assign contacts based on 1 - 2 criteria. However, I keep getting #UNPARSEABLE error. The [Assigned To] Column is setup as a contacts column, and I have the contacts listed (First Name, Last Name, Email) in the Values table. The below formula is to only look at the [Assigned Back Offer Team] column, but I'll also need something that looks at both [Assigned Back Offer Team] and [Region].

=IF([Assigned Back Office Team]@row = "Care", INDEX(Contacts:Contacts, MATCH("Taylor Riley", Contact:Contacts, 0)))

David Joyeuse

Hi @Taylor Riley

I guess you miss an "s" on your last range: Contacts:Contacts in the MATCH formula.

That being said, I don't really get your INDEX/MATCH Formula.

INDEX is looking to return a value in the Contacts column.

MATCH is looking for "Taylor Riley" in the Contacts Column, the first one he'll find.

Your INDEX/MATCH will obviously then return Taylor Riley (I believe it's just an example, but whatever you'd put here is what you'll get).

There's really no point in having the same column evaluated when using INDEX/MATCH.

Finally, if you want to also look at [Region] as well as [Assigned Back Office Team], your formula would start like this:

=IF(AND([Assigned Back Office Team]@row="Care",[Region]@row="XXXX"), INDEX(Contacts:Contacts,MATCH(....)))

Taylor Riley

Thanks, David!

I used: =IF(AND([Assigned Back Office Team]@row="Marketing Operations", [Region]@row = "Canada"), INDEX(Contacts:Contacts, MATCH("Taylor Riley")))

and I still got the #UNPARSEABLE error

Taylor Riley

I essentially have 2 Back Office teams. One team will have all of their requests routed to an email address. The second team, with about 6 contacts, will be assign based on the region that is selected. Some contacts are responsible for multiple regions.

David Joyeuse

You obviously missed the range for the MATCH formula.

But considering your second post, you do not need an IF/AND type of thing.

What you're looking for is:

=IF([Assigned Back Office Team]@row="Marketing Operations","Marketing-Operations-email-here",INDEX({Contacts},MATCH([Regions]@row,{Regions},0)))

Here, if Marketing Operations are the Team where you route email, it'll be your default option. Otherwise, (hence not Marketing Operations), you put the contact name in the cell depending on the region in this row.

Also you could create a user "Marketing Operations" to display in this cell so the values remains the same within the column.

If you don't have one already, you should create a sheet where contacts are assigned regions. And link the INDEX/MATCH ranges {Contacts} and {Regions} to these columns.

Taylor Riley

It's working!

Thank you so much. I was clearly struggling with that one!