how do I check if known number is in array

[Guest]

I want to check if a known number appears in a column of cells. If it does
then I want a "1" to appear if not then I want a "0" to appear.

In cell F23 I have used =IF(F2:F8="1",1,0) and =IF(F2:F8=D23,1,0), where
cells F2 to F8 contain various numbers to be checked against the number "1"
or the value of D23 (which in this case equals 1.)

All I get is a #VALUE in the

I would then check F2 to F8 against D24 (=2) or the number 2

I think the answer may be simple, but I just cannot see it

 

[Bob Phillips]

=IF(COUNTIF(F2:F8),1)>0,1,0)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob,

I tried but still does not work.......... any other ideas?

 

[Dana DeLouis]

Just another option...

=SIGN(COUNTIF(F2:F8,1))

 

[Guest]

Is this what you're looking for?

=--(SUMPRODUCT((F2:F8=1)+(F2:F8=D23))>0)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Cutter]

Bob's formula was correct but had an extra bracket that caused an error

Instead of:
=IF(COUNTIF(F2:F8),1)>0,1,0)

Use:
=IF(COUNTIF(F2:F8,1)>0,1,0)

 

[Guest]

Thanks Ron,

I would never have got that ....... have plugged in the formula..... Great!

Cheers Mate............. Iain