Nesting the AND, OR, and IF Functions in Excel

Using Logical Functions to Test Multiple Conditions

Excel spreadsheet on Windows 10 desktop


The AND, OR and IF functions are some of Excel's better known logical functions. Depending on which function is used, 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, if one of these conditions is true, the function returns a value of TRUE in cell B2. For the AND Function, all three conditions must be true for the function to returns a value of TRUE in cell B3.

Nesting Multiple Functions in Excel

Screenshot of Microsoft Excel showing an overview of conditional statements.


Nesting functions in Excel refer to placing one function inside another. The nested function acts as one of the main function's arguments. In the image above, rows four to seven contain formulas where the AND or OR function are nested inside the IF function.

When one of these two functions is combined with the IF function, the resulting formula has much greater capabilities.

In our specific example, pictured above, three conditions are tested by the formulas in rows two and three:

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

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

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

Changing the Formula's Output

In all formulas in rows four to seven, the AND and OR functions are identical to their counterparts in rows two and three 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 four, a number as seen in row five, 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 data 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:

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

Employing the IF Statement in Excel

Screenshot of Microsoft Excel showing a closeup of our example formula.


The next steps cover how to enter the IF/OR formula located in cell B4 from the example image. The same steps here can be used for entering any of the IF formulas in our particular examples.

Although it is possible to just type the complete formula in by hand, many people find it easier to use the IF function's dialog box to enter the formula and arguments; the dialog box takes care of syntax intricacies such as 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:

  1. Click on cell B4 to make it the active cell.
  2. Click the Formulas tab of the ribbon.
  3. Click the Logical icon to open the function drop down list.
  4. Click IF in the list to open the IF function dialog box.
  5. Click the Logical_test line in the dialog box.
  6. Enter the complete AND function: OR(A2<50,A3<>75,A4>=100) into the Logical_test line using pointing for the cell references if desired.
  7. Click the Value_if_true line in the dialog box.
  8. Type in the text Data Correct (no quotation marks required).
  9. Click on the Value_if_false line in the dialog box.
  10. Type in the text Data Error.
  11. Click OK to close the dialog box and return to the worksheet.
  12. As already discussed above, the formula should display the Value_if_true argument or Data Correct.
  13. When you click on cell B4, the complete function appears in the formula bar above the worksheet.
=IF(OR(A2<50,A3<>75,A4>=100),"Data Correct","Data Error")