Formula with And isnumber and or

[rhombus4]

have the following formula so If A1 is No and A2 contains Apple and either A3 or A4 contain Yes then the result is Yes

This works fine whenever I change any of the cells.

=IF(AND(A1="No",ISNUMBER(SEARCH("apple",A2,OR(A3="Yes",A4="Yes")))),"Yes","No")

However If I change the formula from isnumber to iserror to make it so that it doesn't want Apple (see below)

It works fine as the result changes to NO, but when I change apple to banana or just delete apple from in A2 the result is yes as it matches apple not being in A2 which is right.

What confuses me is If I change A1 from no to something else the result changes to NO which is fine but If I change A3 or A4 so none of them are yes it still stays as Yes. (Should change to NO)

=IF(AND(A1="No",ISERROR(SEARCH("apple",A2,OR(A3="Yes",A4="Yes")))),"Yes","No")

 

[tyija1995]

Hey I *think* it's because of the parenthesis in the wrong positions, try this:

=IF(AND(A1="No",ISERROR(SEARCH("apple",A2)),OR(A3="Yes",A4="Yes")),"Yes","No")

 

[rhombus4]

Thanks. That seems to be fine although not sure why the isnumber one worked ok with brackets in a different place. Isnumber also works when I swap bracket positions to match yours as well

 

[tyija1995]

You were passing in a boolean statement as the start number for the search function (0 or 1) so it would prompt as normal on ISNUMBER but for ISERROR it's not liking it as it's just searching for the string apple in cell A2 - the constraint OR(A3="yes",A4="yes") is essentially a starting position for the search, sort of hard to explain...! Glad it works now though.

 

[rhombus4]

Thanks again ?

 

[tyija1995]

No problem! My explanation wasn't the clearest, but basically if you pass through a search function the start number of 0 (a FALSE boolean for example) it will error, so ISERROR catches it, but ISNUMBER just errors, i.e. FALSE, that might be why the answers seem different