Nested IF(OR(AND) Statement with Two AND Conditions

[chiefs19]

I am using Excel 2003 and could use some assistance with a complex Nested IF
statement. Here is the criteria:

IF Salesorg=NAW1 AND Salesdivision=N4 AND SalesOffice=NWSA, then Group=UB
except when CustomerNbr=90000000 OR 90000001 OR 90000002, then Group=BA.

I have tried several different variations of Nested IF statements, but to no
avail. I do not know VBA, so this is not an option. Any help you can provide
would be greatly appreciated. Thank you.

 

[tompl]

A B C D E
1 Salesorg Salesdivision SalesOffice CustomerNbr Group
2 NAW1 N4 NWSA 90000000 BA

The formula in cell E2 would be something like:

=IF(AND(A2="NAW1",B2="N4",C2="NWSA"),IF(NOT(OR(D2=90000000,D2=90000001,D2=90000002)),"UB","BA"),"NotSpecified")

Without the word wrap.

 

[Joe User]

IF Salesorg=NAW1 AND Salesdivision=N4 AND
SalesOffice=NWSA, then Group=UB except when
CustomerNbr=90000000 OR 90000001 OR
90000002, then Group=BA.

If Salesorg, Salesdivision, SalesOffice and CustomerNbr are named ranges,
and Group is a parallel range of cells, you can write the following into the
first Group cell and copy down:

=if(AND(Salesorg="NAW1",Salesdivision="N4",SalesOffice="NWSA"),
if(OR(CustomerNbr={90000000,90000001,90000002},
"BA", "UB"), "")

Alternatively, replace the name Salesorg, Salesdivision, SalesOffice and
CustomerNbr with the proper cell references, for example:

=if(AND(A2="NAW1",B2="N4"C2="NWSA"),
if(OR(D2={90000000,90000001,90000002},
"BA", "UB"), "")

Note that formula returns the null string ("") if A2, B2 or C2 is not the
required condition, a case that you neglected to cover.


----- original message -----

 

[chiefs19]

I tried using this formula and I received "The formula you typed contains an
error message." The logical_test portion of the equation passes, but the
value_if_true portion does not. I understand the logic of your equation and
it will deliver the results I need if we can resolve the error.

 

[Joe User]

I tried using this formula and I received "The
formula you typed contains an error message."

My bad! Two typos in my previous posting. This time, I have
copy-and-pasted from the Formula Bar (always a good idea):

=IF(AND(A2="NAW1",B2="N4",C2="NWSA"),
IF(OR(D2={90000000,90000001,90000002}),
"BA", "UB"), "")


----- original message -----

 

[chiefs19]

If have not had a chance to fully test it yet, but this seems to work. I have
never seen a formula structured this way. I can definitely use this to go
back and shorten some of my current formulas. Thank you for all of your help!!