Excel OR Function

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:

=OR(B2="blue",B2="yellow"). 

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.

Syntax:

=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.

TRUE Example

=OR(B2>0,B2>10,B2>100)
TRUE result
  • =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)
FALSE result
  • =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 and OR function example
=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)
OR function array BITOR

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
OR function FALSE or TRUE boolean response

You can use the IF function with your array results, giving you an array of 1 and 0 as OR.

For example:

=IF(((B3:B11>40)+(B3:B11<0)),1,0)

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

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.