How do I have a formula check if a value occurs within a range?

I need to have a formula check whether or not a particular value occurs
within a range of data. For example, given the data set below:
A
1 red
2 yellow
3 green
4 blue

I need to be able to check whether that list contains a particular word. So
something like: =ifcontains(A1:A4,"green","Yes","No"). So in this case, the
"ifcontains" function would give me a "Yes", and
=ifcontains(A1:A4,"orange","Yes","No") would evaluate to "No".

Should be a pretty simple matter, if the "ifcontains" function exists. Any
ideas?

Hi!

Try this

=IF(COUNTIF(A1:A4,"green"),"Yes","No")

Or use a cell to hold the criteria, green:

B1 = green

=IF(COUNTIF(A1:A4,B1),"Yes","No")

Biff

:"afgncaap" <[email protected]> wrote in message
news:[email protected]...
>I need to have a formula check whether or not a particular value occurs
> within a range of data. For example, given the data set below:
> A
> 1 red
> 2 yellow
> 3 green
> 4 blue
>
> I need to be able to check whether that list contains a particular word.
> So
> something like: =ifcontains(A1:A4,"green","Yes","No"). So in this case,
> the
> "ifcontains" function would give me a "Yes", and
> =ifcontains(A1:A4,"orange","Yes","No") would evaluate to "No".
>
> Should be a pretty simple matter, if the "ifcontains" function exists.
> Any
> ideas?

Now why didn't I think of that? Thanks, works like a charm!

"afgncaap" <[email protected]> wrote in message
news:[email protected]...
> Now why didn't I think of that? Thanks, works like a charm!

You did. You just misspelled countif !

=ifcontains(A1:A4,"orange","Yes","No")

Thanks for the feedback.

Biff