IF function – nested formulas and avoiding pitfalls (2024)

The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False.

=IF(Something is True, then do something, otherwise do something else)

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

IF statements are incredibly robust, and form the basis of many spreadsheet models, but they are also the root cause of many spreadsheet issues. Ideally, an IF statement should apply to minimal conditions, such as Male/Female, Yes/No/Maybe, to name a few, but sometimes you might need to evaluate more complex scenarios that require nesting* more than 3 IF functions together.

* “Nesting” refers to the practice of joining multiple functions together in one formula.

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false.

Syntax

IF(logical_test, value_if_true, [value_if_false])

For example:

=IF(A2>B2,"Over Budget","OK")
=IF(A2=B2,B4-A4,"")

Argument name	Description
logical_test (required)	The condition you want to test.
value_if_true (required)	The value that you want returned if the result of *logical_test* is TRUE.
value_if_false (optional)	The value that you want returned if the result of *logical_test* is FALSE.

Remarks

While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?

Examples

Following is an example of a relatively standard nested IF statement to convert student test scores to their letter grade equivalent.

IF function – nested formulas and avoiding pitfalls (1)

=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

This complex nested IF statement follows a straightforward logic:

If the Test Score (in cell D2) is greater than 89, then the student gets an A
If the Test Score is greater than 79, then the student gets a B
If the Test Score is greater than 69, then the student gets a C
If the Test Score is greater than 59, then the student gets a D
Otherwise the student gets an F

This particular example is relatively safe because it’s not likely that the correlation between test scores and letter grades will change, so it won’t require much maintenance. But here’s a thought – what if you need to segment the grades between A+, A and A- (and so on)? Now your four condition IF statement needs to be rewritten to have 12 conditions! Here’s what your formula would look like now:

=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
See Also
Home

It’s still functionally accurate and will work as expected, but it takes a long time to write and longer to test to make sure it does what you want. Another glaring issue is that you’ve had to enter the scores and equivalent letter grades by hand. What are the odds that you’ll accidentally have a typo? Now imagine trying to do this 64 times with more complex conditions! Sure, it’s possible, but do you really want to subject yourself to this kind of effort and probable errors that will be really hard to spot?

Tip:Every function in Excel requires an opening and closing parenthesis (). Excel will try to help you figure out what goes where by coloring different parts of your formula when you’re editing it. For instance, if you were to edit the above formula, as you move the cursor past each of the ending parentheses “)”, its corresponding opening parenthesis will turn the same color. This can be especially useful in complex nested formulas when you’re trying to figure out if you have enough matching parentheses.

Additional examples

Following is a very common example of calculating Sales Commission based on levels of Revenue achievement.

IF function – nested formulas and avoiding pitfalls (2)

=IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

This formula says IF(C9 is Greater Than 15,000 then return 20%, IF(C9 is Greater Than 12,500 then return 17.5%, and so on...

While it’s remarkably similar to the earlier Grades example, this formula is a great example of how difficult it can be to maintain large IF statements – what would you need to do if your organization decided to add new compensation levels and possibly even change the existing dollar or percentage values? You’d have a lot of work on your hands!

Tip:You can insert line breaks in the formula bar to make long formulas easier to read. Just press ALT+ENTER before the text you want to wrap to a new line.

Here is an example of the commission scenario with the logic out of order:

IF function – nested formulas and avoiding pitfalls (3)

Can you see what’s wrong? Compare the order of the Revenue comparisons to the previous example. Which way is this one going? That’s right, it’s going from bottom up ($5,000 to $15,000), not the other way around. But why should that be such a big deal? It’s a big deal because the formula can’t pass the first evaluation for any value over $5,000. Let’s say you’ve got $12,500 in revenue – the IF statement will return 10% because it is greater than $5,000, and it will stop there. This can be incredibly problematic because in a lot of situations these types of errors go unnoticed until they’ve had a negative impact. So knowing that there are some serious pitfalls with complex nested IF statements, what can you do? In most cases, you can use the VLOOKUP function instead of building a complex formula with the IF function. Using VLOOKUP, you first need to create a reference table:

IF function – nested formulas and avoiding pitfalls (4)

=VLOOKUP(C2,C5:D17,2,TRUE)

This formula says to look for the value in C2 in the range C5:C17. If the value is found, then return the corresponding value from the same row in column D.

IF function – nested formulas and avoiding pitfalls (5)

=VLOOKUP(B9,B2:C6,2,TRUE)

Similarly, this formula looks for the value in cell B9 in the range B2:B22. If the value is found, then return the corresponding value from the same row in column C.

Note:Both of these VLOOKUPs use the TRUE argument at the end of the formulas, meaning we want them to look for an approxiate match. In other words, it will match the exact values in the lookup table, as well as any values that fall between them. In this case the lookup tables need to be sorted in Ascending order, from smallest to largest.

VLOOKUP is covered in much more detail here, but this is sure a lot simpler than a 12-level, complex nested IF statement! There are other less obvious benefits as well:

VLOOKUP reference tables are right out in the open and easy to see.
Table values can be easily updated and you never have to touch the formula if your conditions change.
If you don’t want people to see or interfere with your reference table, just put it on another worksheet.

Did you know?

There is now an IFS function that can replace multiple, nested IF statements with a single function. So instead of our initial grades example, which has 4 nested IF functions:

=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

It can be made much simpler with a single IFS function:

=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

The IFS function is great because you don’t need to worry about all of those IF statements and parentheses.

Note:This feature is only available if you have a Microsoft 365 subscription. If you are a Microsoft 365subscriber, make sure you have the latest version of Office.

Buy or try Microsoft 365

Need more help?

You can always ask an expert in the Excel Tech Communityor get support inCommunities.

FAQs

What is the better option than nested IF in Excel? ›

VLOOKUP instead of nested IF in Excel

By setting the last argument of your Vlookup formula to TRUE, you tell Excel to search for the closest match - if an exact match is not found, return the next largest value that is smaller than the lookup value.

Show Me More ›

How do you use if function in a nested way? ›

If you clicked IF, the Function arguments dialog box displays the arguments for the IF function. To nest another function, you can enter it into the argument box. For example, you could enter SUM(G2:G5) in the Value_if_true box of the IF function. Enter any additional arguments that are needed to complete your formula.

Learn More ›

What is the alternative solution for nested if? ›

Alternatives to nested IF in Excel

To test multiple conditions and return different values based on the results of those tests, you can use the CHOOSE function instead of nested IFs. Build a reference table and a use VLOOKUP with approximate match as shown in this example: VLOOKUP instead of nested IF in Excel.

Learn More ›

How do you avoid nested IF functions? ›

Nested IFs are powerful, but they become complicated quickly as you add more levels. One way to avoid more levels is to use IF in combination with the AND and OR functions. These functions return a simple TRUE/FALSE result that works perfectly inside IF, so you can use them to extend the logic of a single IF.

Read On ›

What are the disadvantages of nested if? ›

Overuse of nested if-else statements can make code more complex, harder to read, harder to debug and increase execution time and memory usage.

Find Out More ›

What is the advantage of nested IF function? ›

One IF function has one test and two possible outcomes, TRUE or FALSE. Nested IF functions, meaning one IF function inside of another, allows you to test multiple criteria and increases the number of possible outcomes.

Learn More Now ›

Why are nested if statements bad? ›

Nested if statements can quickly become unwieldy, leading to what is commonly referred to as "arrow code" due to the shape it forms in your editor. This pattern makes it hard to follow the logic and understand the conditions being checked. It's also easy to introduce errors when modifying deeply nested code.

Show Me More ›

How many nested ifs is too much? ›

While Excel will allow you to nest up to 64 different IF functions, it's not at all advisable to do so. Why? Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end.

Know More ›

What is the difference between nested IF and IFS? ›

Well, there really is no difference between a “regular” IF statement and a nested IF statement. The difference is that, with a nested IF statement, the FALSE option/parameter is another condition. The nested IF statement just asks a sequence of questions rather than a single question.

Know More ›

What is a strategy for avoiding nested conditionals? ›

# Describe a strategy for avoiding nested conditionals. # We are able to use operators (and, or, not) to give our conditional statements greater flexibily and this flexibility as you can see from above will sometimes allow for fewer nested conditionals. This is one accepted strategy.

See Details ›

What can I use instead of nested IF statements sheets? ›

You can employ the Google Sheets SWITCH function instead of the nested IFs. The SWITCH function checks if your expression corresponds to a case list. It returns a corresponding value when the expression does.

Keep Reading ›

What is the difference between nested if and else if? ›

A if-else if chain does the same as a switch statement. Nested ifs test combinations of conditions.