Calculated Forumla - help joining syntax

Question

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")))

Answer 1

Use this:

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

Answer 2

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","")))