IF AND OR statement

[sadiejay]

Hi, I have been working on this formula for hours and cannot get it right. Please help! Column C has either TRUE or FALSE. Columns L and M have either a number or #N/A (depending on whether there was a number on the source data - this data has come from a vlookup). What I want, in column N is to say if column C says TRUE there is a number in either column L or M. So of C says TRUE and either/and L and M holds a number, N to say MATCH. This is what I have tried: =IF(OR(AND(C16="TRUE",ISNUMBER(L16)),AND(C16="TRUE",ISNUMBER(M16))),"MATCH","NOTMATCH") but all my results are showing as NOTMATCH. What am I doing wrong? Thanks so much!

 

[steve the fish]

Hi

How about this:

=IF(AND(C1=TRUE,OR(ISNUMBER(L1),ISNUMBER(M1))),"MATCH","NOTMATCH")

 

[sadiejay]

Thank you so much - this worked like a dream! However, I am having further problems. I need to add a condition to this formula, where if C1 says FALSE and both L1 and M1 are blank I want N to say MATCH, but if C1 says FALSE and either L or M is not blank, then I want N to say NOTMATCH. This is my formula: =IF(OR(AND(C2=TRUE,OR(ISNUMBER(L2),(ISNUMBER(M2)))),(AND(C2=FALSE,ISBLANK(L2),ISBLANK(M2)))),"MATCH","NOTMATCH") Are you able to help with what this formula should be?

 

[oldbrewer]

in your formula C1= false goes to "notmatch" so replace that with if(and(l1="",m1=""),"match","notmatch")
remember to keep the last )

 

[sadiejay]

This worked great! Thank you both SO MUCH!

 

[sadiejay]

in your formula C1= false goes to "notmatch" so replace that with if(and(l1="",m1=""),"match","notmatch")
remember to keep the last )

Hi. I have now realised this is working, except that I need a "0" or "0.00" to also be considered blank. Is anyone able to help with this formula? Either amending the original formula or in fact changing all "0" and "0.00" to be returned Blank

 

[steve the fish]

Your 'and' part would become something like:

AND(OR(L1="",L1=0),OR(M1="",M1=0))