If then unless

[JOEYNICE1978]

I am not sure if any of this makes sense, but here is the definition of the formula that I need (image attached for reference)


C1 IS FALSE IF B1 CONTAINS ANY ITEMS FROM F COLUMN IF NOT THEN TRUE - ALSO - C1 IS FALSE IF B1 CONTAINS ANY ITEM FROM E COLUMN UNLESS B2 CONTAINS ITEM FROM F COLUMN

Capture.JPG

[etaf]

it would be easier to load a sample spreadsheet rather than an image

C1 IS FALSE IF B1 CONTAINS ANY ITEMS FROM F COLUMN IF NOT THEN TRUE - ALSO - C1 IS FALSE IF B1 CONTAINS ANY ITEM FROM E COLUMN UNLESS B2 CONTAINS ITEM FROM F COLUMN

in C1

=IF ( countif(F:F, B1)>0, FALSE, IF( AND( countif(F:F, B1)=0 , countif(E:E, B1)>0) , FALSE, TRUE))

Not sure what the ALSO means in your question

I have set-out so that it tests for the first condition
if B1 is in column F - then it puts FALSE
if B1 id not in column F
then it tests the next condition
IF B1 is in column E AND B2 is NOT in column F -

[Vikas_Gautam]

Try this formula in C1 and drag it down...

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Don't forget to click *

[sktneer]

Mine little bit lengthy.....

=IF(OR(ISNUMBER(MATCH(B1,F2:F5,0)),AND(ISNUMBER(MATCH(B1,E1:E5,0)),ISNA(MATCH(B2,F2:F5,0)))),"FALSE","TRUE")