Nesting the AND, OR, and IF Functions in Excel

Using logical functions to test multiple conditions

By

Updated on November 13, 2019

Build the Excel IF Statement

When using the IF, AND, and OR functions, one or all of the conditions must be true for the function to return a TRUE response. If not, the function returns FALSE as a value.

For the OR function (see row 2 in the image below), if one of these conditions is true, the function returns a value of TRUE. For the AND function (see row 3), all three conditions must be true for the function to returns a value of TRUE.

In the image below, rows 4 to 6 contain formulas where the AND and OR functions are nested inside the IF function.

Screenshot of Microsoft Excel showing an overview of conditional statements using the IF, AND, and OR functions

When the AND and OR functions are combined with the IF function, the resulting formula has much greater capabilities.

In this example, three conditions are tested by the formulas in rows 2 and 3:

Is the value in cell A2 less than 50?
Is the value in cell A3 not equal to 75?
Is the value in cell A4 greater than or equal to 100?

Also, in all of the examples, the nested function acts as the IF function's first argument. This first element is known as the Logical_test argument.

=IF(OR(A2<50,A3<>75,A4>=100),"Data Correct","Data Error")

=IF(AND(A2<50,A3<>75,A4>=100),1000,TODAY())

Change the Formula's Output

In all formulas in rows 4 to 6, the AND and OR functions are identical to their counterparts in rows 2 and 3 in that they test the data in cells A2 to A4 to see if it meets the required condition.

The IF function is used to control the formula's output based on what is entered for the function's second and third arguments. Examples of this output can be text as seen in row 4, a number as seen in row 5, the output from the formula, or a blank cell.

In the case of the IF/AND formula in cell B5, since not all three cells in the range A2 to A4 are true — the value in cell A4 is not greater than or equal to 100 — the AND function returns a FALSE value. The IF function uses this value and returns its Value_if_false argument — the current date supplied by the TODAY function.

On the other hand, the IF/OR formula in row four returns the text statement Data Correct for one of two reasons:

The OR value has returned a TRUE value — the value in cell A3 does not equal 75.
The IF function then used this result to return its Value_if_false argument: Data Correct.

Use the IF Statement in Excel

The next steps cover how to enter the IF/OR formula located in cell B4 from the example. These same steps can be used to enter any of the IF formulas in these examples.

Screenshot of Microsoft Excel showing the OR function nested inside the IF function

There are two ways to enter formulas in Excel. Either type the formula in the Formula Bar or use the Function Arguments dialog box. The dialog box takes care of the syntax such as placing comma separators between arguments and surrounding text entries in quotation marks.

The steps used to enter the IF/OR formula in cell B4 are as follows:

Select cell B4 to make it the active cell.
On the ribbon, go to Formulas.
Select Logical to open the function dropdown list.
Choose IF in the list to open the Function Arguments dialog box.
Place the cursor in the Logical_test text box.
Enter the complete OR function:
```
OR(A2<50,A3<>75,A4>=100)
```
Place the cursor in the Value_if_true text box.
Type Data Correct.
Place the cursor in the Value_if_false text box.
Type Data Error.
Select OK to complete the function.
The formula displays the Value_if_true argument of Data Correct.
Select cell B4 to see the complete function in the formula bar above the worksheet.

Was this page helpful?

Thanks for letting us know!

Tell us why!