Aug 05 2021 12:48 PM
Hi,
I have a bunch of customer names that are assigned to an employee depending on what letter the customer name begins. So if the name begins with A-F or S-Z then it is assigned to Employee 1. If the letter begins with H-R then it is assigned to Employee 2.
ALSO - there are five customer names that are exceptions to this rule. If the customer's name is Red, Blue, Yellow, Green, or Brown then it is automatically assigned to Employee 3.
Is there a way to create a formula to identify which employee each customer's name should be? Please see the formula I have created so far below:
=IF(OR(E2="BLUE", "YELLOW", "GREEN", "RED", "BROWN"), Employee 3), IF(OR(LEFT(E2,1)="A-F", "S-Z"), "Employee 1", IF(LEFT(E2,1)="H-R", "Employee 2"))
Do I have to list out every letter one by one?
Thanks!
Aug 05 2021 01:01 PM
SolutionTry this:
=IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1"))
or
=IF(E2="", "", IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1")))
Aug 05 2021 01:01 PM
SolutionTry this:
=IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1"))
or
=IF(E2="", "", IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1")))