Calculated Forumla - help joining syntax

I'm not sure if it can be done, please note I am not a novice, I am trying to join multiple syntax for a calculated column.

I have column 1 as 'state' its values are either California or New York and in Column 2 as 'region' its values are East, West or Special and I need to assign an employee. Employee Jane gets all of California as long as the region is not special and John gets all of New York as long as the region is not special but Joe is assign the special region regardless of state.

My first syntax works great but when I try to join them, it is not being accepted.

Primary Syntax:

``````=IF(AND([State]="California",[Region]<>"Special"),"Jane","""")
``````

My failed attempt at joining them.

``````=IF(AND([State]="California",[Region]"Special"),"Jane","""",=IF(AND([State]=
"New York",[Region]"Special"),"John","""",=IF(AND([region]="Special","Joe")))
``````
• There's a few syntax issues here... But nothing too major. You don't need all the `=` signs, and you've got 4 terms in each `IF` statement, not 3, and your last IF statement just has one boolean term saying `If Region is Special AND Joe` where I think you want `If Region is Special, THEN Joe`. I wrote an answer below giving what I think should work for your situation. Commented Dec 6, 2018 at 18:38

Use this:

``````=IF(AND([State]="California",[Region]<>"Special"),"Jane", IF(AND([State]="New York",[Region]<>"Special"),"John", IF([region]="Special","Joe", "")))
``````

I think you may be over thinking everything. Try something like this:

``````=IF(
[region]="Special",
"Joe",
IF(
[State]="California",
"Jane",
IF(
[State]="New York",
"John",
""
)
)
)
``````

This should make all `Specials` go to Joe, otherwise `Californias` go to Jane. If it's neither `Special` nor `California`, but the State is `New York` go to John. If it doesn't follow any of those requirements then it'll be left blank.

If your states were exclusively `California` and `New York` you can replace the section `IF([State]="New York","John","")` with just `"John"` and it should act the same. But if you have more states involved, then you'll need to keep that IF statement as-is.

Here's the version without spaces if you'd ranter:

``````=IF([region]="Special","Joe",IF([State]="California","Jane",IF([State]="New York","John","")))
``````
• Your answer has logical issues. By my opinion. If State will be Texas and Region not Special then you get John .... John only in New York. Why ? You must get empty value....
– user80188
Commented Dec 6, 2018 at 18:50
• @VsevolodVasilev It would, except that the column `State` was stated to be exclusively `California` or `New York`. I'll change it to be more general, but it does solve OP's question. Commented Dec 6, 2018 at 19:56