comparing a value to a list

[mark kubicki]

i need to check if the value of a cell is contained within a list
ex:
cell.value is "apple"
the list is stored as a range of cells, and is something like: "apple",
"pear", "lemon"
so the result would be TRUE (apple is in the list)
however, if the cell value was "grapefruit', the result would be FALSE
------------------------------------
i would normally go thru the list using Find(), and FindNext(); if the value
is found, =TRUE; if nothing =FALSE...

the problem is that i'm already inside a FindNext() loop; so i get a
conflict between the search string in the inner loop and the outer loop,
which is being re-defined by the inner loop
-------------------------------------
the list is not long, so i could do:
if cell = range(a1)... or cell = range(a2)... or cell = range(a3)... or ...
this seems to not be the most efficient way to do this
-------------------------------------
i could also loop through each cell in the list comparing it's value to the
searach string
but, again, i think that there must be a more elegant solution (i think ?)

mark

 

[Beto]

i need to check if the value of a cell is contained within a list
ex:
cell.value is "apple"
the list is stored as a range of cells, and is something like: "apple",
"pear", "lemon"
so the result would be TRUE (apple is in the list)
however, if the cell value was "grapefruit', the result would be FALSE
------------------------------------
i would normally go thru the list using Find(), and FindNext(); if the value
is found, =TRUE; if nothing =FALSE...

the problem is that i'm already inside a FindNext() loop; so i get a
conflict between the search string in the inner loop and the outer loop,
which is being re-defined by the inner loop

What about using VLOOKUP and check if the result is #N/A with ISNA?

Regadrs,