You have one too many right parentheses in each if statement. Try removing the right parentheses that you have in the middle of your if statements. For example, change
And remove all but one of the ",0)" at the end in the first statement you posted.
Give that a try and let us know if that works.
Now create another calculated field of type lookup , called , as a lookup field based on the values of . It should be straightforward that way, and I promise you won't have to count any pairs of brackets or worry about AND and OR conditions...
This is what finally worked:
Next time I need to do something like this, though, I'm going to use the lookup table approach that was recommended above.
Thanks so much for your help!