Why does IF and OR combined not work

[basb87]

Hello all,

I have a question regarding a combination of IF + OR.

Here is what I want:

=IF(A1=OR("A","B"),1,2)

Why does this not work? I mean, the formula should be logically correct, right?

I know that it will work if I do it like this:

=IF(OR(A1="A",A1="B"),1,2)

But the problem is that this is longer. I have simplified this formula here, because my real formula is much much longer and I have a lot of formulas to get to the value that I want to use for my IF-statement.

Thanks in advance.

Best regards,
Bas

 

[mole999]

maybe there would be a more concise formula or use of VBA, care to demonstrate some real world logic

 

[basb87]

I don't understand what you mean? This is not really an answer to my question.

 

[AliGW]

Your formula:

=IF(A1=OR("A","B"),1,2)

is not syntactically correct. The OR function requires logical statements, hence this:

=IF(OR(A1="A",A1="B"),1,2)

I think what Mole is suggesting is that you give us an idea of your real scenario so that we can better advise - why so many OR statements and what is this long, complex formula? More pertinently, what exactly are you trying to get Excel to do?

 

[Aladin Akyurek]

=IF(OR(A1="A",A1="B"),1,2)

=IF(OR(A1={"A","B"}),1,2)

=IF(ISNUMBER(MATCH(A1,{"A","B"},0)),1,2)

=2-ISNUMBER(MATCH(A1,{"A","B"},0))

are equivalent and are all syntactically admissible, while

=IF(A1=OR("A","B"),1,2)

is not.

OR expects one or more evaluations that end up in logical results:

The OR("A","B") does not evaluate to either TRUE or FALSE. Text values do not have logical evaluation, while numbers on the other hand do.

=OR(1)
=OR(-0.2)

will evaluate to TRUE while

=OR(0)

will evaluate to FALSE. Put otherwise a non-zero number >> TRUE in AND, OR, IF, etc,, a zero to FALSE.

 

[inactiveuserps07]

It seems to me the easiest way to deal with this would be to simply make a list of all your "OR" possibilities (say on Sheet 2) and then just use a simple formula.

Let's say you have a list of 25 things you're checking for, and it resides on Sheet2 in A2:A26 (under a header in A1).

And let's say the list you're trying to check against your "OR" possibilities is on Sheet1 in A2:A100 (under a header in A1).

Then column B in Sheet1 (starting at B2) could be:

=NOT(ISERROR(MATCH(A2,Sheet2!$A$2:$A$26,0)))

All "TRUE" (or 1) listings would be a match against your "OR" list on Sheet2.

 

[basb87]

Thank you very much all!

 

[inactiveuserps07]

basb87, there are lots of great suggestions on this site. In this particular instance, I do believe the suggestion I made is going to leave you the most flexibility and room for growth. If you didn't understand clearly how to set it up, I'll be happy to explain in a different way, starting from where you started to get lost.

 

[Aladin Akyurek]

basb87, there are lots of great suggestions on this site. In this particular instance, I do believe the suggestion I made is going to leave you the most flexibility and room for growth. If you didn't understand clearly how to set it up, I'll be happy to explain in a different way, starting from where you started to get lost.

Why?

ISNUMBER(MATCH(...))

would be better than

NOT(ISERROR(MATCH(...)))

 

[inactiveuserps07]

No debate there, Aladin. I'm talking about its being more flexible to create a dedicated list of possible matches and then just use one formula to check entries against the list, rather than a million embedded IF statements.