IF AND OR statement

New Member
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!

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

steve the fish

Well-known Member
Hi

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

New Member
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

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

New Member

This worked great! Thank you both SO MUCH!

New Member
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

Well-known Member
Your 'and' part would become something like:

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

Replies
27
Views
490
Replies
19
Views
196
Replies
3
Views
162
Replies
12
Views
202
Replies
1
Views
133

1,141,300
Messages
5,705,577
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back