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

 

[Oaktree]

Try this:

=if(and(or(D2="AA",D2="AB"),or(E2<>"Pass",isna(E2))),"Problem","OK")

 

[macleanb]

=if(or(and(or(d2="AA",d2=" "AB") ,E2<> "Pass"),isna(e2)),"Problem", "OK")


I think!

 

[poikl]

I don't know what I am doing wrong buttried both and they don't work1
If Cel-E2 equals N/A# then Cel-F2 also shows N/A# instead of "Problem?

Also I forgot, it needs to check in Cel-E2 for 3 things (not just the 2 as mentioned before), 1) not to equal "Pass' or 2) Not to equal "shipped' or 3) not to equal N/A#.

 

[Aladin Akyurek]

I don't know what I am doing wrong buttried both and they don't work1
If Cel-E2 equals N/A# then Cel-F2 also shows N/A# instead of "Problem?

Also I forgot, it needs to check in Cel-E2 for 3 things (not just the 2 as mentioned before), 1) not to equal "Pass' or 2) Not to equal "shipped' or 3) not to equal N/A#.

Are you saying that:

If E2 is AA or AB and E2 is not equal Pass or Shipped or E2 houses #N/A then F2 = Problem, otherwise F2 = OK.

 

[poikl]

Aladyn I'm so glad to hear from you.
What I need that Cel-F2 is to check if Cel-E2 equals either "AA" or "AB" and Cel-F2 does not equal any of the following, 1) Cel-F2 does NOT equal "Pass', 2) Cel-F2 does NOT equal "Shipped',or 3) Cel-F2 equals N/A#, then,then Cel-F2 should show "Problem" otherwise, it (Cel-F2) should show "OK" ?

 

[Aladin Akyurek]

...

What I need that Cel-F2 is to check if Cel-E2 equals either "AA" or "AB" and Cel-F2 does not equal any of the following, 1) Cel-F2 does NOT equal "Pass', 2) Cel-F2 does NOT equal "Shipped',or 3) Cel-F2 equals N/A#, then,then Cel-F2 should show "Problem" otherwise, it (Cel-F2) should show "OK" ?

You mean E2, not F2. This does not seem to be different from the recap I attempted, apart from the D2/E2/F2 mix up.

But what must happen when D2 is empty and E2 houses, say, Sent or when D2 and E2 are both empty?

 

[poikl]

Aladyn you're always sharp.
Yes, I meant Cel-D2 and Cel-E2 repectavly.
If both, Cel-D2 and Cel-E2 are empty that's ok so I guess '' (blank) would be the correct response.
I did not consider when Cel-E2 has "Shipped" and Cel-D2 is empty, that too would be a problem but it should show differently say, "Not Designated"
Thank you for picking it up.

 

[Aladin Akyurek]

Aladyn you're always sharp.
Yes, I meant Cel-D2 and Cel-E2 repectavly.
If both, Cel-D2 and Cel-E2 are empty that's ok so I guess '' (blank) would be the correct response.
I did not consider when Cel-E2 has "Shipped" and Cel-D2 is empty, that too would be a problem but it should show differently say, "Not Designated"
Thank you for picking it up.

Book4
	D	E	F	G	H	I	J	K
1							Sortkey
2		#N/A	Problem			Shipped	@Shipped	NotDesignated
3					AA	Pass	AA@Pass	OK
4					AA	Shipped	AA@Shipped	OK
5					AB	Pass	AB@Pass	OK
6					AB	Shipped	AB@Shipped	OK
7
8
Sheet1

How about setting up a table of diagnostics?

J2, copied down:

=H2&"@"&I2

The area H2:K6 is sorted in ascending order on column J.

J2:K6 is named Table (via the Name Box on the Formula Bar).

The formula for diagnosis of D2:E2 in F2 is:

=IF(ISNA(E2),"Problem",IF(LOOKUP(D2&"@"&E2,INDEX(Table,0,1))=D2&"@"&E2,LOOKUP(D2&"@"&E2,Table),"Problem"))

 

[poikl]

too fancy,
can't I just make a simple IF(AND(OR...
that is what I was hoping for.
Please?