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.
The OR function in Excel checks if at least one of the multiple conditions is true. It returns TRUE if any specified conditions are TRUE and FALSE only if all conditions are FALSE. This makes it helpful when you want to validate data that meets one criterion out of several possible options.
How the OR logic works in Excel
In logical terms OR tests each argument independently and only needs a single argument to return TRUE for the entire function to output TRUE. For example, if you’re checking a cell to see if it contains either “blue” or “yellow,” OR will return TRUE if either condition is met. Here’s how it’s structured:
If you want to check if cell B2 contains either “blue” or “yellow,” you could use:
=OR(B2 = “blue”, B2 = “yellow”)
This formula will return TRUE if B2 contains “blue” or “yellow.” Otherwise, it will return FALSE. You can also combine OR with IF to display a custom message when one of the conditions is met. 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
The OR function can test multiple conditions. It has two output types: TRUE or FALSE. The function uses one condition at most and can handle up to 255 conditions.
Syntax:
=OR (logical1, [logical2], …[logical255])
Arguments:
“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. It can check one or more (up to 255 conditions) simultaneously. All arguments (logical test) will return TRUE or FALSE separately.
TRUE example:
=OR(B2>0,B2>10,B2>100)
- =B2>0 = TRUE
- =B2>10 = FALSE
- =B2>100 = FALSE
The formula will return TRUE if one or more logical tests evaluate TRUE.
FALSE Example:
=OR(B2>11,B2=0,B2<0)
- =B2<10 = FALSE
- =B2=0 = FALSE
- =B2>10 = FALSE
The result will be FALSE if all logical tests evaluate solely and exclusively FALSE. It is 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. Here is a possible workaround: in this case, we recommend using the 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; you will get back a single result if you give them an array. What if you want to use an array to test 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:
=BITOR(B3:B11>40, B3:B11<0)
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:
=BITOR(B3:B11>40,B3:B11<0)=1
You can use the IF function with your array results, which will give you an array of 1 and 0 as OR.
For example:
=IF(((B3:B11>40)+(B3:B11<0)),1,0)
Things to remember
Here are some essential points to remember about Excel’s logical functions, particularly AND and OR:
- Output: These functions only return TRUE or FALSE, providing a clear binary output for logical tests.
- Array Support: By default, AND and OR do not support arrays. If you need to work with arrays, consider using other methods or functions compatible with array handling.
- Alternative for Arrays: The BITOR function can be used for array support if needed, particularly in bitwise operations, though it’s less common for standard logical checks.
- Compatibility: Both AND and OR integrate seamlessly with other functions like IF, allowing for complex conditional formulas. This makes them versatile tools for various logical operations in Excel.
Related resources: