Return a set value if any cell in a range equals a given #

[starrpnvm]

I have a range of cells (C8:G8) that looks like: 2, 7, 13, 14, 20

I have another cell (C5) that has the value of 13

I am trying to get a formula to enter the number "1" in another cell (C6) if any of the values in the range (C8:G8) equals the number 13.

Is there an easy way to do this?
I have tried if(C8:G8=C5, 1, 0) but it does not seem to work.

Any help is appreciated

 

[TrippyTom]

=IF(COUNTIF(C8:G8,C5),1)

The formula above will check the range for whatever number is in C5. So if you change it to say 2, 7, 14 or 20 it will still give you a 1. If it's any other number it will return FALSE.

 

[acw]

Hi

Try

=IF(ISNA(MATCH(C5,C8:G8,0)),0,1)


Tony

 

[Aladin Akyurek]

Re: Return a set value if any cell in a range equals a given

I have a range of cells (C8:G8) that looks like: 2, 7, 13, 14, 20

I have another cell (C5) that has the value of 13

I am trying to get a formula to enter the number "1" in another cell (C6) if any of the values in the range (C8:G8) equals the number 13.

Is there an easy way to do this?
I have tried if(C8:G8=C5, 1, 0) but it does not seem to work.

Any help is appreciated

C6:

=ISNUMBER(MATCH(13,C8:G8,0))+0

 

[starrpnvm]

Awsome guys......just awsome! Thank you all.

 

[XPZTX]

I had the same problem and it worked perfectly. Saved me like 10 days. You guys rock ty so much.