IF(AND(OR function

[poikl]

Could you please help me with this function so that it should work?
I would like to check in Cel-D2 if it equals either "AA", "AB" and Cel-E2 does not equal "Pass" or it (Cel-E2) equals N/A# then Cel-F2 should show "Problem" otherwise it (Cel-F2) should show "OK" ?

 

[poikl]

Phil, The formula in Cel-E2 (which is dragged down to 24,000 rows is =VLOOKUP(A2,Deliv.xls!Del,5,0)

I am having a problem with Pookies formula
"=IF(ISNA($E2),IF(OR($D2="AA",$D2="AB"),"Problem",IF(AND(OR($D2="AA",$D2="AB"),OR($E2<>"Pass",$E2<>"Shipped")),"Problem",IF(OR($D2<>"AA",$D2<>"AB",$D2<>"",AND($D2="",$E2="")),"",IF(AND($D2="",OR($E2="Shipped",$E2="Pass")),"Not Designated","OK")))),"OK")"
I think there might be a misprint in Row 3 above {$D2<>"",AND($D2="",}
but I don't know how to change it.
Thank you very much for helping me

 

[Von Pookie]

Could you actually describe the problem? Are the results it is returning incorrect?

Your comment about a 'misprint' is rather confusing.

 

[Phil Fluffdry]

Phil, The formula in Cel-E2 (which is dragged down to 24,000 rows is =VLOOKUP(A2,Deliv.xls!Del,5,0)

I am having a problem with Pookies formula
"=IF(ISNA($E2),IF(OR($D2="AA",$D2="AB"),"Problem",IF(AND(OR($D2="AA",$D2="AB"),OR($E2<>"Pass",$E2<>"Shipped")),"Problem",IF(OR($D2<>"AA",$D2<>"AB",$D2<>"",AND($D2="",$E2="")),"",IF(AND($D2="",OR($E2="Shipped",$E2="Pass")),"Not Designated","OK")))),"OK")"
I think there might be a misprint in Row 3 above {$D2<>"",AND($D2="",}
but I don't know how to change it.
Thank you very much for helping me

Hi poikl,

I turned around the order of the logic you presented to make it easier for me to follow and work with (and hopefully for you). It looks like there are only a couple circumstances that would result in "OK", two that would result in "Not Designated", one in a blank result, and a bunch that would result in "Problem". This is the formula I came up with and should work for all the possibilities I've seen mentioned in the other postings:

=(IF(ISERROR(E2),"Problem",IF(AND(D2="",E2=""),"",IF(AND(D2="",OR(E2="Pass",E2="Shipped")),"Not Designated",(IF(AND(D2="AA",OR(E2="Pass",E2="Shipped")),"OK",IF(AND(D2="AB",OR(E2="Pass",E2="Shipped")),"OK","Problem")))))))

Now let me break it down for you in the same order that this formula follows:

1) IF(ISERROR(E2),"Problem" - If the VLOOKUP statement in E2 has an error ANY error in it (not just the #N/A error) then the result is "Problem".

2) IF(AND(D2="",E2=""),"" - If D2 and E2 are both blank then F2 is blank.

3) IF(AND(D2="",OR(E2="Pass",E2="Shipped")),"Not Designated" - If D2 is blank and E2 = "Shipped" or "Pass" then F2 = "Not Designated".

4) IF(AND(D2="AA",OR(E2="Pass",E2="Shipped")),"OK" - If D2 = "AA" and E2 = "Pass" or "Shipped" then F2 = "OK".

5) IF(AND(D2="AB",OR(E2="Pass",E2="Shipped")),"OK" - If D2 = "AB" and E2 = "Pass" or "Shipped" then F2 = "OK".

6) ,"Problem"))))))) - If one of the preceeding five criteria are not met then F2 will default to "Problem".

Give it a try and let me know how it works for you,

Phil

p.s. Yes, the code for steps 4 & 5 could have been combined using another OR function but I elected to do it this way because it's just a little easier to follow.

 

[poikl]

Thank you for answering
Could you please provide me the code for:
IF Cel-E2 = N/A# and Cel-D2 also = N/A# then Blank,
IF Cel-E2 = N/A# and Cel-D2 Does NOT equal either "CK" or "BK" then "Return"
Thank you all for helping me all the way

 

[poikl]

Can you please answer my last question.
I am very greatful for all your help till now
IF Cel-E2 = N/A# and Cel-D2 also = N/A# then Blank,
IF Cel-E2 = N/A# and Cel-D2 Does NOT equal either "CK" or "BK" then "Return"

 

[fairwinds]

Hi,

Try:

=IF(ISNA(D2)*ISNA(E2),"",IF(ISNA(D2)*(E2<>"CK")*(E2<>"BK"),"Return","Something else"))

 

[poikl]

Thank you very much, it works!
Could you please explain me what the "*" (Multipication sign) does?
Is it instead of "OR" statement?

 

[fairwinds]

In this case they function as AND(). Meaning all conditions needs to be TRUE in order to get TRUE.

 

[Phil Fluffdry]

Can you please answer my last question.
I am very greatful for all your help till now
IF Cel-E2 = N/A# and Cel-D2 also = N/A# then Blank,
IF Cel-E2 = N/A# and Cel-D2 Does NOT equal either "CK" or "BK" then "Return"

Now we've got a problem with conficting conditions and results. In your messages you've stated that you wanted:
1) If E2 = #N/A then F2 = "Problem".
2) If D2 = "AA" or "AB" and Cel-E2 does not equal "Pass" or it (Cel-E2) equals N/A# then Cel-F2 should show "Problem".
3) IF Cel-E2 = N/A# and Cel-D2 Does NOT equal either "CK" or "BK" then "Return"

Now we have some conflicting statements. Why? Because [E2 = #N/A and D2 = "AA"] satisfies all three conditions above. So do you want the result to be "Return" or "Problem"? That's just one example of many conflicts.

In order to make this work you need some consistency in your comparison statements. Right now I know that D2 could equal AA, AB, BK, CK, or (blank) but what else could it equal? To me, the easiest way to accomplish this is to plot out all of the possible combinations of D2 and E2 and devise a formula for the results in F2 from there. Here's a sample of what I mean:

D2 E2 F2
-------------------------------------------------------
AA #N/A Problem
AA PASS OK
AA SHIPPED OK
AB #N/A Problem
AB PASS OK
AB SHIPPED OK
BK #N/A Problem
CK #N/A Problem

As you can see it's not quite as simple as you thought, but it can be done. If there are a lot more different combinations then another lookup table (as someone suggested earlier) might actually be easier. But if we've got them all, or there are only a few more, we can keep working on this formula.

Phil

 

[poikl]

I am perplexed?
In the first statement "=IF(ISNA(D2)*ISNA(E2)," it seems as it (the* symbol) works as the "AND statement
However in the second part "(E2<>"CK")*(E2<>"BK"),"Return","Something else")) it seems that it is a OR statement??