OR Function

The OR function is one of Excel's logical functions. It is designed to test multiple conditions simultaneously and return TRUE if any condition is TRUE. If all conditions are FALSE, the OR function returns FALSE. The OR function is often combined with other functions like AND, NOT, and IF to construct more complex logical tests. It commonly appears in the logical test of the IF function and in formulas for conditional formatting and data validation. The OR function is a good way to simplify complicated formulas use many nested IFs.

OR function basics

The purpose of the OR function is to evaluate more than one logical test at the same time and return TRUE if any result is TRUE. Excel's OR function can handle up to 255 separate conditions, which are entered as arguments with names like "logical1", "logical2", and "logical3", etc. Each "logical" is a condition that can be evaluated as TRUE or FALSE. The arguments provided to the OR function can be constants, cell references, or logical expressions. OR will return TRUE if any condition is TRUE:

=OR(FALSE,FALSE,TRUE) // returns TRUE

If all conditions are FALSE, OR will return FALSE:

=OR(FALSE,FALSE,FALSE) // returns FALSE

Typically, logical arguments are provided to OR as logical expressions, for example:

=OR(A1>0,A1<5)
=OR(A1>0,B1>0)
=OR(A1="red",B1="small")

All expressions in the formulas above will be evaluated as TRUE or FALSE. Notice that text values used in comparisons must be enclosed in double quotes (""). Be aware that OR will also evaluate numbers as TRUE or FALSE, and treat any number except zero (0) as TRUE. You can see this behavior in the formulas below:

=OR(0,0,3) // returns TRUE
=OR(0,0,0) // returns FALSE

Let's look at some practical ways to use the OR function.

Example - value is x or y or z

You can use OR to test for one of several values. For example, in the worksheet below, we are using the OR function to test if the codes in column B are 115, 120, or 125. The formula in cell D5 is:

=OR(B5=115,B5=120,B5=125)

Notice that we are testing for numeric values so the numbers appear in their raw form without quotes ("").

Example - OR with the IF function

The OR function is often embedded inside the IF function as the logical test to simplify what would otherwise be a more complex formula. For example, in the worksheet below, the goal is to test scores in columns B and C. If either score is below 750, the result should be "Deny". If both scores are 750 or above, the result should be "Approve". The formula in cell E5 is:

=IF(OR(B5<750,C5<750),"Deny","Approve")

As the formula is copied down, it returns "Approve" or "Deny" for each row in the data.

Example - IF this OR that

The worksheet below is a variation of the example above. This time, the goal is to flag rows where the color is "red" and the size is "small". The formula in cell D5 is:

=IF(OR(B5="red",B5="green"),"x","")

You are free to replace "x" with any other value. Notice that the OR function is not case-sensitive. The lowercase "red" and "small" text strings equal the "Red" and "Small" text on the worksheet.

Example - OR with AND

You can combine the OR function with the AND function to build more advanced conditions. In the worksheet below, the goal is to identify records where the color is "red" or "green" and the quantity is over 100. The logical test inside the IF function is created with AND and OR:

AND(OR(B5="red",B5="green"),C5>100)

The complete formula in E5 is:

=IF(AND(OR(B5="red",B5="green"),C5>100),"x","")

Notice that the text values "red" and "green" are enclosed in double quotes ("") but the number 100 is not.

Example - OR with conditional formatting

The OR function is often used in the rules that trigger conditional formatting. In the worksheet below, we have adapted the formula in the example above to apply conditional formatting to rows where the color is "red" or "green" and the quantity is over 100. The conditional formatting is applied to the range B5:C15, and the formula to trigger the rule looks like this:

=AND(OR($B5="red",$B5="green"),$C5>100)

Notice $B5 and $C5 are mixed references with the column fixed in order to highlight entire rows.

Example - OR with a range

It is possible to use OR with a range of values. In the worksheet below, the formula in cell J5 is:

=IF(OR(C5:H5<65),"Fail","Pass")

Note: this is an array formula and must be entered with Control + Shift + Enter in Excel 2019 and earlier. In Excel 2021 or later, the formula "just works" without special handling.

In the example above, the AND function is used inside the IF function as the logical test. This formula will return "Pass" only if the score for all six subjects is greater than 65. Otherwise, the result is "Fail". See also Must pass 4 out of 6 subjects.

Notes

• Each logical condition must evaluate to TRUE or FALSE.
• Text values or empty cells supplied as arguments are ignored.
• The OR function will return #VALUE if no logical values are found
• The OR function can handle up to 255 conditions in Excel.
• If any condition is TRUE, OR returns TRUE
• If all conditions are FALSE, OR returns FALSE
• The OR function is not case-sensitive.
• The OR function does not support wildcards.