Nest IF Statements in Excel

Add to Favorites

How to nest IF statements inside of each other in Excel so that you can make more complex decision structures. This allows you to make a check like this: test if something is true and do x if it is true or, if it is not true, perform another check to see if something else is true and return y if it is and z if it is not.

This sounds confusing though so let's look at an example.

Sections:

IF Function

Nest IF Functions Example

Notes

IF Function

The IF() function allows you to add logic to Excel; this function is also often called an IF statement. In it, you test if something evaluates to true or false (yes or no) and do one thing if true and another thing if false.

Syntax

=IF(logical_test, [value_if_true], [value_if_false])

The last two arguments have brackets around them because they are optional; if you leave them blank, they will just output the values True or False as a result of the function.

If you would like to learn more about the IF function, check out our tutorial on IF statements in Excel. The rest of the tutorial assumes that you understand basic IF statements.

Nest IF Functions Example

To nest IF statements we simply put them inside of each other using regular nesting in Excel techniques.

The main issue is to understand the logic behind nesting, so let's work with an example:

=IF(A1>A2,"yes",IF(A3>A2,"ok","no"))

32bc1fffb38c804f472604e4b1d81451.png

Logic Explanation: IF cell A1 is greater than cell A2, output the text yes; otherwise, if cell A1 is NOT greater than cell A2, check IF cell A3 is greater than cell A2 and, if it is, output ok, but, if it is not, output no.

Here, a second IF statement/function was input in place of the "value_if_false" argument. This is what allows a second set of logical tests to be made and, as you can see, we have three possible outputs based on the result of these logical tests; the three potential outputs are yes, ok, and no, but you can set them to anything you want.

In this example I put the second IF statement inside of the "value_if_false" argument but I could just as easily have put it into the "value_if_true" argument or I could have put IF statements in for both arguments.

You can continue to nest IF statements as much as is needed in order to create a decision tree or structure that works for your situation.

Notes

If you get to the point that you have 5 or 10 or more IF statements nested within each other, it will be quite confusing to maintain the formula and troubleshoot it if anything goes wrong. In this case, it is often better to use a different spreadsheet setup, such as a Vlookup or Index/Match setup or one of many other potential setups. That said, using IF statements is often the easiest solution for users of Excel and, though it can get complex, there is nothing wrong with using this approach so long as you understand how it works and so long as it works in your situation.

Make sure to download the sample file to work with this example in Excel.


Excel Function: IF()
Downloadable Files: Excel File

Similar Content on TeachExcel
VBA IF Statement in Excel Macros
Tutorial: IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement...
Introducing Logic into Formulas and Functions in Excel
Tutorial: In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Tutorial: Lets learn how to put multiple functions and formulas in a single cell in Excel in order t...
How to Use the IF Function in Excel
Tutorial: The IF statement is a simple yet powerful tool. Today we will go through how the IF functi...
Logical Operators in Excel VBA Macros
Tutorial: Logical operators in VBA allow you to make decisions when certain conditions are met. They...
Logical Comparison Operators in Excel - How to Compare Things
Tutorial: Logical comparison operators allow you to compare values in Excel to see if they are equal...