IF Statement

Mimi H

Hello! I was wondering if anyone could help get me started on carrying out what I think will be a very long IF/AND statement. See attachments - I've created a formula to read the department column and the output will be the employee that has been entered into the top grey section in the Assigned To column. My issue is that some cells in our Department column will contain more than one department for instance, IT and Business Development), therefore I will need the output to produce more than one employee in the Assigned To column. I started adding additional IF statements to the formula but kept getting Unparsable. Can someone help me get started in the right direction? Thanks!

Devin Lee

Hey @Mimi H

You can use the CONTAINS function in combination with the IF to check for each Department. The issue you are going to have is that Contact columns only support one contact per cell so if you have any automation setup around the Assigned To they won't work. If you don't mind that the formula below will work.

=IF(CONTAINS(Department1, Department@row), [Assigned To]1 + " ", "") + IF(CONTAINS(Department2, Department@row), [Assigned To]2 + " ", "") + IF(CONTAINS(Department3, Department@row), [Assigned To]3 + " ", "") + IF(CONTAINS(Department4, Department@row), [Assigned To]4 + " ", "") + IF(CONTAINS(Department5, Department@row), [Assigned To]5 + " ", "") + IF(CONTAINS(Department6, Department@row), [Assigned To]6 + " ", "") + IF(CONTAINS(Department7, Department@row), [Assigned To]7 + " ", "") + IF(CONTAINS(Department8, Department@row), [Assigned To]8 + " ", "") + IF(CONTAINS(Department9, Department@row), [Assigned To]9 + " ", "") + IF(CONTAINS(Department10, Department@row), [Assigned To]10 + " ", "")

The easy solution is to add more columns and only allow for single selection for each department matched with a column for Assigned To for each Department selected. Then just use the INDEX/MATCH function to pull the right person from each department.

=INDEX([Assigned To]1:[Assigned To]10, MATCH(Department@row, Department1:Department10))