checking for a value in a list

[kcrad]

I'm looking for a way to see if there is there is a value in a list or not.
I think I understand all of the lookup functions - my problem is that I just
want a function that will search a list for a value I specify and return
TRUE if the value is there and FALSE if the value is not. Every different
way I try to use a lookup function, I get an NA error if the value is not
there and I don't want to report a value from another column if it is there.

This can't be that hard ... What am I missing?

 

[Mike]

this will return TRUE or FALSE. If you leave the >0 bit out it will return
the number of times the value is in the list. The value being looked for is
in A1

=COUNTIF(B1:B100,A1)>0

Mike

 

[Vergel Adriano]

Return TRUE if the value is there, otherwise FALSE

=IF(ISNA(VLOOKUP("some_value", A1:A100, 2, FALSE)), FALSE, TRUE)

 

[Gary''s Student]

Say the list is in A1 thru A20 and the value is in cell G7. Then:

=IF(COUNTIF(A1:A20,G7)>0,TRUE,FALSE)

 

[Farhad]

Hi,

you can use =isnumber(A1) it works for the negative values too

Farhad Hodjat

 

[kcrad]

OK, that was great, Mike! Now, let me expand on this. I'm trying to use
conditional formating to color the background of the cell one color if the
value is in my list and another color if it is not. The problem with
conditional formating is that it only knows TRUE. I don't know how to tell
it to do one thing for TRUE and another for FALSE. Any ideas?