If Formula

**JGrange** · 05-30-2010, 12:20 PM

Hi all

I know this is quite a simple formula, but for some reason will not work in the capacity I want it to.

The formula is:

If(M8883=BD65425:BD65439,"Yes",A8883)

Any ideas what would stop this formula from working? I can do it in a blank workbook using IF(B1=A1:A10,"Yes",C10) and it works fine however generates the #VALUE! when I put it into practice as above.

Any help would be appreciated

J

**zbor** · 05-30-2010, 01:42 PM

Maybe this?

=IF(COUNTIF(BD65425:BD65439, M8882)>0, "YES", A8883)

**shg** · 05-30-2010, 01:44 PM

What is the formula supposed to do?

In the second example,

=IF(B1=A1:A10, "Yes", C10)

... there is an implied intersection, so the formula is equivalent to

=IF(B1=A1, "Yes", C10)

If the first formula, there is no such intersection.

aiwnjoo · 05-30-2010, 01:45 PM

It works fine, what are you trying to achieve, i replicated it and was ok... See attached.

**JGrange** · 05-30-2010, 01:52 PM

Originally Posted by zbor

Maybe this?

=IF(COUNTIF(BD65425:BD65439, M8882)>0, "YES", A8883)

Brilliant. No idea why I didn't think of that - Brain is fried!!

Thanks for your help

**JGrange** · 05-30-2010, 01:53 PM

Originally Posted by aiwnjoo

It works fine, what are you trying to achieve, i replicated it and was ok... See attached.

It works fine in any other spreadsheet than the one I want to use it in.. No idea why. Resolved now though.

Thanks for your suggestions everyone

**zbor** · 05-30-2010, 01:56 PM

Originally Posted by JGrange

No idea why I didn't think of that - Brain is fried!!

As Homer Simpson would say:
“Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.”