how to see if any cell in this range equals a value?

[bigdan]

I have this formula in one of my worksheets:
=IF(OR(D5=H5,E5=H5,F5=H5,G5=H5,H5=H5,I5=H5),0,"NO")

It's actually even longer and more complicated but this is a good example.
Instead of writing H5 like 5-10 times is there a way to make this easier? Ie if any cell in D5:I5 = H5, 0, else "No"

Thanks!

 

[Joe4]

I have this formula in one of my worksheets:
=IF(OR(D5=H5,E5=H5,F5=H5,G5=H5,H5=H5,I5=H5),0,"NO")

Your code won't work as you want, as a value will ALWAYS equal itself.
You don't want the value you are matching to to be included in your range.

Try this:

=IF(COUNTIF(D5:G5,H5)+COUNTIF(I5:I5,H5)>0,0,"NO")

 

[mumps]

You're going to have a problem with the part in red: =IF(OR(D5=H5,E5=H5,F5=H5,G5=H5,H5=H5,I5=H5),0,"NO")
H5 will always be equal to itself.

Oops. It looks like I was a little late in posting.

 

[Fluff]

How about
=IF(COUNTIF(D5:G5,H5),0,"no")
With your description you will always get 0 as H5 will = H5

 

[ValuationMan]

Couple of things, your formula will always come up as 0 because H5 does equal H5.

If that H5 referring to itself is accurate, you could do this: =IF(CountIF(D5:I5,H5)>0,0,"No")

 

[Fluff]

Another option, assuming you don't want to count H5=H5
=IF(COUNTIF(D5:I5,H5)>1,0,"no")