Using 'IF' function with an 'OR' range

[jamie10]

Hi

I am getting stuck with something I thought would be more straightforward!

I am writing the following formula which I hoped would give me a 'yes' if cell B3 contains any one of the values I have in a range list (C5:C9). Then if it's not true, give me 'no'. I will be using the formula down column B.

=IF(B3=OR($C$5:$C$9),"yes","no")

Unfortunately, I receive the #VALUE! error.

How can I update this to give me what I'm after? I've attached a sheet that might make this ask more explicit.

Thanks very much for any advice!

[Sam Capricci]

maybe try this instead... =IFERROR(IF(VLOOKUP(B3,C5:C9,1,FALSE)=B3,"yes"),"no")

[torachan]

Please Login or Register  to view this content.

[AliGW]

That's not the way to do it. By the way, are you REALLY using Excel 2007 (apologies if I've asked this before)?

Try this instead:

=IF(ISERROR(MATCH(B3,$C$5:$C$9,0)),"No","Yes")

[Phuocam]

Or try this:

=IF(COUNTIF($C$5:$C$9,B3)>0,"Yes","No")

[AliGW]

Lots of ways to skin this cat, just not with the OR function!!!

[jamie10]

Wow, thanks all for the quick responses. I can see there are better options out there!

Why is it that the formula doesn't work - is it something to do with it being a range? It would just be nice to know why it went wrong.


AliGW, I am using a later version but perhaps the saved file is old. I have 2016 I believe.

[AliGW]

You have either 2016 (standalone) or 365 (subscription), but you now have both in your profile???

Have a look here: https://exceljet.net/excel-functions/excel-or-function

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Phuocam]

If you want to use the OR function, try an array formula**:

=IF(OR($C$5:$C$9=B3),"Yes","No")


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[jamie10]

that's exactly what I was trying to accomplish, nice to know it can be done with OR, it's just making it show as an array formula :

{=IF(OR($C$5:$C$9=B3),"Yes","No")}

[jamie10]

You have either 2016 (standalone) or 365 (subscription), but you now have both in your profile???

I need to amend that , it is the 365 subscription provided through my organisation

[AliGW]

Yes, please - the two versions do differ considerably!