Nest IF Statements in Excel

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"))

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.