Testing for Multiple Conditions

[Steve]

If I have a string variable, a, and I want to test it for a variety of
things, is there an easy way to do it? I've tried if (a = or("this", "that",
"etc")). That didn't work. I've tried if (a="this" or "that" or "etc") and
that didn't work. Of course if a="this" or a="that" or a="etc" works but if
I have a whole list to test I wonder if there's an easier way. Thanks.

[Trevor Shuttleworth]

Steve

maybe you could put your variables in a list in a worksheet somewhere and
use VLOOKUP ?

For example:

=IF(ISNA(VLOOKUP(A1, LookupRange,1,FALSE)),"not found","found")

Where LookupRange contains a list of "this", "that" and the "other", "etc"

Regards

Trevor


"Steve" <Steve@discussions.microsoft.com> wrote in message
news:343C3D6B-B8AE-4417-BA8B-FE57FB5A72FB@microsoft.com...
> If I have a string variable, a, and I want to test it for a variety of
> things, is there an easy way to do it? I've tried if (a = or("this",
> "that",
> "etc")). That didn't work. I've tried if (a="this" or "that" or "etc")
> and
> that didn't work. Of course if a="this" or a="that" or a="etc" works but
> if
> I have a whole list to test I wonder if there's an easier way. Thanks.

[David McRitchie]

VLOOKUP looks like a better solution, since it is easily expanded but
getting back to the original not working.

You seem to have a mixture of macro and worksheet terminology
and syntax.

Excel:
=OR(A1="this", "that", "etc")
which returns True or False

=IF(OR(A1="this", "that", "etc"), "found", "not found")

the test is on the entire content of a cell and the test is not
case sensitive.

VBA: (case sensitive)
dim val as string, cell as range
val = LCASE(cell.value)
if val = "this" or val = "that" or val = "etc" then
msgbox "Found"
else
msgbox "not found"
end if

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Trevor Shuttleworth" <Trevor@Shucks.demon.co.uk> wrote in message news:OEIcV8KjFHA.320@TK2MSFTNGP09.phx.gbl...
> Steve
>
> maybe you could put your variables in a list in a worksheet somewhere and
> use VLOOKUP ?
>
> For example:
>
> =IF(ISNA(VLOOKUP(A1, LookupRange,1,FALSE)),"not found","found")
>
> Where LookupRange contains a list of "this", "that" and the "other", "etc"
>
> Regards
>
> Trevor
>
>
> "Steve" <Steve@discussions.microsoft.com> wrote in message
> news:343C3D6B-B8AE-4417-BA8B-FE57FB5A72FB@microsoft.com...
> > If I have a string variable, a, and I want to test it for a variety of
> > things, is there an easy way to do it? I've tried if (a = or("this",
> > "that",
> > "etc")). That didn't work. I've tried if (a="this" or "that" or "etc")
> > and
> > that didn't work. Of course if a="this" or a="that" or a="etc" works but
> > if
> > I have a whole list to test I wonder if there's an easier way. Thanks.
>
>