Prevent Excel from evaluating unneeded expressions in OR()

Question

IF(OR(ISNA(MATCH(8,B10:B17,0)),MATCH(8,B10:B17,0)>8),"",...BLAH...)

I understand how to fix this problem by rearranging my formula. I have it the way it is to show this point. You can see the OR() statement checks to see if the first MATCH() returns NA. When it does, OR() should automatically return TRUE and not evaluate the second MATCH() because conditions have been met for the OR() to return true no matter what other arguments there are. You'll notice that the first and second MATCH() functions do the same thing. What's happening is the entire function is returning NA because the second MATCH() is executing even though it doesn't have to, the OR() has been satisfied with one TRUE, therefore the function should return "".

Is this a bug or is this intentional?

Answer 1

In Excel Formulae OR does not shortcircuit evaluation (neither does AND), but IF does. I guess its intentional since it has always been that way.

Answer 2

To expand on what Charles said, OR and AND cannot be shortcircuited because of what they are. The formula evaluates all inputs to come up with a result. That being said, you could probably write a UDF that would do what you are asking.