Do I use the OR function, or something else? (2000)

Topic

New Reply

The short version: How do I tell Excel that if a cell contains one of a group of values, it should perform some sort of operation? I can’t seem to make OR work; am I on the wrong track?

The long version: If a cell contains a specific value (A, B, C or D), I want Excel to indicate “yes”, otherwise indicate “no”. In quasi Excel-English it would look like this:
=IF(A1=(“A” or “B” or “C” or “D”), “yes”,”no”)
which, of course, doesn’t work at all.

When I try to use Excel language:
=IF(A1=OR(“A”, “B”, “C”, “D”), “yes”,”no”)
I get an error. From what I can figure out, it will only tell me “yes” if A1 contains all four letters, but that doesn’t make any sense. My brain hurts.

How should I write the formula so that it will tell me if cell A1 contains any of those letters.

Reply | Quote

Replies

I think you have your OR parameters messed up ..
If you want this:
IF(A1=OR(“A”, “B”, “C”, “D”), “yes”,”no”)
Try this:
=IF (OR(A1=”A”,A1=”B”,A1=”C”,A1=”D”),”YES”,”NO”)

kinda cumbersome, but that’s how it works.

–CAT

Reply | Quote

You can also use:

=IF(ISERROR(FIND(A1,"A,B,C,D")),"No", "yes")

for case sensitive

Or for case insensitive:

=IF(ISERROR(Search(A1,"A,B,C,D")),"No", "yes")

This is easier to enter, if you have a lot of items to OR

Steve

Reply | Quote

This is another way (which is similar) to your original scheme

=IF(ISNA(MATCH(A1,{"A","B","C","D"},FALSE)),"No", "yes")

Steve

Reply | Quote

This is another way (which is similar) to your original scheme

=IF(ISNA(MATCH(A1,{"A","B","C","D"},FALSE)),"No", "yes")

Steve

Reply | Quote

You can also use:

=IF(ISERROR(FIND(A1,"A,B,C,D")),"No", "yes")

for case sensitive

Or for case insensitive:

=IF(ISERROR(Search(A1,"A,B,C,D")),"No", "yes")

This is easier to enter, if you have a lot of items to OR

Steve

Reply | Quote

I think you have your OR parameters messed up ..
If you want this:
IF(A1=OR(“A”, “B”, “C”, “D”), “yes”,”no”)
Try this:
=IF (OR(A1=”A”,A1=”B”,A1=”C”,A1=”D”),”YES”,”NO”)

kinda cumbersome, but that’s how it works.

–CAT

Reply | Quote