Nest IF Statements in Excel

Author:

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.

Excel Function: IF()

Question? Ask it in our Excel Forum

Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

Similar Content on TeachExcel
SWITCH() Function in Excel and 3 Alternatives
Tutorial: How to use the SWITCH() function for Excel. SWITCH() allows you to match a series of valu...
VBA IF Statement in Excel Macros
Tutorial: IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement...
IFS Function - Multiple IF Statements in One
Tutorial: Easily create nested IF statements using the new IFS() function for Excel! The IFS() func...
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 ...
Make Better IF Statements in Excel - Learn to Convert Complex Formulas into True False Values
Tutorial: How to change any formula to make it return a TRUE or FALSE value - you need this for usi...
Compare Values in Excel - Beginner to Advanced
Tutorial: How to compare text, numbers, and dates in Excel - including case sensitive text comparis...
Tutorial Details
Excel Function: IF()