# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  How do I have a formula check if a value occurs within a range?

## afgncaap

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

:"afgncaap" <afgncaap@discussions.microsoft.com> wrote in message
news:CBEC6D2A-5505-4599-BFD6-003200C330E0@microsoft.com...
>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?

----------


## afgncaap

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

----------


## Biff

"afgncaap" <afgncaap@discussions.microsoft.com> wrote in message
news:7B1699AB-34CB-4FCE-A324-5D9ABA05A3E0@microsoft.com...
> 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

----------

