Excel IF OR statement with formula examples

This tutorial demonstrates how to use Excel's IF and OR functions together to evaluate multiple conditions. The IF function is powerful on its own, but combining it with logical functions like OR significantly expands its capabilities.

IF OR Statement in Excel

The core of the IF OR statement is embedding the OR function within the IF function's logical test. This allows you to check multiple conditions and return one result if any condition is true, and a different result if all conditions are false. The basic structure is:

IF(OR(condition1, condition2,...), value_if_true, value_if_false)

A simple example:

=IF(OR(B2="delivered", B2="paid"), "Closed", "Open")

This checks if cell B2 contains "delivered" or "paid". If either is true, it returns "Closed"; otherwise, "Open". To return nothing if false, use an empty string:

=IF(OR(B2="delivered", B2="paid"), "Closed", "")

A more compact version using an array constant:

=IF(OR(B2={"delivered","paid"}), "Closed", "")

Note: The OR function is case-insensitive. For case-sensitive comparisons, use the EXACT function within the OR statement.

Excel IF OR Formula Examples

Here are several examples illustrating the versatility of IF OR:

1. Multiple OR Conditions: You can include many OR conditions (up to 255 in Excel 2007 ). For example, to check for blank cells in A2, B2, and C2:

=IF(OR(A2="",B2="",C2=""),"Incomplete","")

2. Calculations Based on OR Conditions: Instead of returning text, you can perform calculations. For example, applying a 10% discount if quantity (B2) is >=10 or unit price (C2) is >=$5:

=IF(OR(B2>=10, C2>=5), B2*C2*0.9, B2*C2)

You could also indicate discounted orders with a simple "Yes" or "No":

=IF(OR(B2>=10, C2>=5),"Yes", "No")

3. Case-Sensitive IF OR: To perform case-sensitive checks, use the EXACT function:

=IF(OR(EXACT(A2, "AA-1"), EXACT(A2, "BB-1")), "x", "")

4. Nested IF OR Statements: For multiple sets of OR criteria, nest IF statements:

=IF(OR(A2="apple", A2="orange"), "Fruit", IF(OR(A2="tomato", A2="cucumber"), "Vegetable", ""))

5. IF AND OR Statement: Combine AND and OR for complex conditions. For example, flagging rows where the item is "apple" or "orange" AND the quantity is >10:

=IF(AND(OR(A2="apple",A2="orange"), B2>10), "x", "")

This tutorial provides a comprehensive overview of using IF and OR functions together in Excel. Remember to consider the limitations on the number of arguments and character length within your formulas. Downloadable example workbooks are available for further study.

The above is the detailed content of Excel IF OR statement with formula examples. For more information, please follow other related articles on the PHP Chinese website!