Forum Discussion
IF a cell starts with a letter then "value"
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!
Try 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")))
2 Replies
Try 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")))
- Worked like a charm. Thanks!
