The Excel OR Function returns TRUE if at least one logical test is evaluated to TRUE and returns FALSE if all logical tests are FALSE.
This tutorial is a part of our definitive guide about Excel functions.
For example, to apply a logical test B2 for either “blue” or “yellow”, use:
You can use the OR function inside the IF Function to evaluate a logical test and write easy-to-read formulas instead of complicated nested IFs. Furthermore, you can combine it with other logical functions like NOT or AND.
Syntax, Arguments, and return value
We can test multiple conditions using the OR function; the function has two output types: TRUE or FALSE. The function uses one required and up to a maximum of 255 conditions.
=OR (logical1, [logical2], ...[logical255])
Logical1 is the first condition or logical value that Excel will evaluate.
How to use the OR function
We evaluate logical tests inside the IF function using OR. The function combines the basic formula with additional logical functions, like the AND Function. The function can check one or more (up to 255 conditions) at the same time simultaneously.
All arguments (logical test) will return TRUE or FALSE separately.
- =B2>0 = TRUE
- =B2>10 = FALSE
- =B2>100 = FALSE
The formula will return TRUE if one or more logical tests evaluate TRUE.
- =B2<10 = FALSE
- =B2=0 = FALSE
- =B2>10 = FALSE
The result will be FALSE if all logical tests evaluate solely and exclusively FALSE. Good to know that the AND and OR logical functions return a single value. Unfortunately, that means you can not use the function in array operations. A possible workaround is to use boolean logic.
IF and OR example
Use the IF function to add a descriptive name to the output of the OR formula. You can apply this trick instead of using 1 or 0 values.
The formula below will return “good” if the value in B2 is greater than 10 or less than 20; else gets “average”.
=IF(OR(B3>10,B3<20), "good", "average")
The result is “good” because the second logical test returns with TRUE.
How to apply a logical OR to an array in Excel
It looks like AND, OR, and XOR are not array-friendly functions; if you give them an array, you will get back a single result. What if you want to use an array for testing a condition? The easiest way is using the BITOR function. Let us see how it works with arrays.
Binary response: BITOR function
The BITOR function can process multiple values in an array formula. In the example, we want to test two conditions using an array.
The conditions are the following:
=(B3:B11>40) OR (B3:B11<0)
Enter the formula in D3:
In the array, three values met the criteria: 50, 94, and 51.
FALSE or TRUE boolean response
If you want to get boolean results directly, use the following formula:
You can use the IF function with your array results, giving you an array of 1 and 0 as OR.
Things to remember
- The function always returns TRUE and FALSE
- By default, the function does not support arrays
- Use the BITOR function for array support
- It works fine with the AND and IF functions