IF AND OR statement

sadiejay

New Member
Joined
Dec 4, 2014
Messages
7
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,516
Office Version
  1. 365
Platform
  1. Windows
Hi

How about this:

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

sadiejay

New Member
Joined
Dec 4, 2014
Messages
7
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
Joined
Apr 11, 2010
Messages
11,005
in your formula C1= false goes to "notmatch" so replace that with if(and(l1="",m1=""),"match","notmatch")
remember to keep the last )
 

sadiejay

New Member
Joined
Dec 4, 2014
Messages
7

ADVERTISEMENT

This worked great! Thank you both SO MUCH!:)
 

sadiejay

New Member
Joined
Dec 4, 2014
Messages
7
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
Joined
Oct 20, 2009
Messages
8,516
Office Version
  1. 365
Platform
  1. Windows
Your 'and' part would become something like:

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

Forum statistics

Threads
1,136,317
Messages
5,675,047
Members
419,546
Latest member
RobWayCot

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top