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

[Guest]

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?

 

[Biff]

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

 

[Guest]

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

 

[Biff]

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