Skip to main content

Mastering Nested IF Statements in Excel – A Step-by-Step Guide

In Excel, while the IF function is essential for basic logical tests, complex conditions often require nested IF statements for enhanced data processing. In this comprehensive guide, we'll cover the basics of nested IF in detail, from syntax to practical applications, including combinations of nested IF with AND/OR conditions. In addition, we'll share how to improve the readability of nested IF functions as well as some tips about nested IF, and explore powerful alternatives such as VLOOKUP, IFS, and more to make complex logical operations easier to use and more efficient.


Excel IF function vs. Nested IF statements

The IF function and nested IF statements in Excel serve similar purposes but differ significantly in their complexity and application.

IF Function: The IF function tests a condition and returns one value if the condition is true and another value if it is false.
  • The Syntax is:
    =IF (logical_test, [value_if_true], [value_if_false])
  • Limitation: Can only handle one condition at a time, making it less suitable for more complex decision-making scenarios that require multiple criteria to be assessed.
Nested IF statements: Nested IF functions, meaning one IF function inside of another, allow you to test multiple criteria and increases the number of possible outcomes.
  • The Syntax is:
    =IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))
  • Complexity: Can handle multiple conditions but can become complex and hard to read with too many layers of nesting.

Usage of nested IF

This section demonstrates the basic usage of nested IF statements in Excel, including syntax, practical examples, and how to use them with AND or OR conditions.


Syntax of nested IF

Understanding the syntax of a function is the foundation for its correct and effective application in Excel. Let’s start with the syntax of nested if statements.

Syntax:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Arguments:

  • Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
  • Result1: This is the value returned if Condition1 is TRUE.
  • Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
  • Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
  • Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
    In short, this expression can be interpreted as follows:
    Test condition1, if TRUE, return result1, if FALSE,
    test condition2, if TRUE, return result2, if FALSE,
    test condition3, if TRUE, return result3, if FALSE,
    return result4

Remember, in a nested IF structure, each subsequent condition is only evaluated if all previous conditions are FALSE. This sequential checking is critical to understanding how nested IFs work.


Practical examples of nested IF

Now, let's dive into the use of nested IF with two practical examples.

Example 1: Grading System

As shown in the screenshot below, suppose you have a list of student scores and want to assign grades based on these scores. You can use nested IF to accomplish this task.

Note: The grading levels and their corresponding score ranges are listed in the range E2:F6.

Select a blank cell (C2 in this case), enter the following formula and press Enter to get the result. Then drag the Fill Handle down to get the rest of the results.

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))
Notes:
  • You can directly specify the grade level in the formula, so the formula can be changed to:
    =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
  • This formula is used to assign a grade (A, B, C, D, or F) based on a score in cell A2, using standard grading thresholds. It's a typical use case for nested IF statements in academic grading systems.
  • Explanation of the formula:
    1. A2>=90: This is the first condition the formula checks. If the score in cell A2 is greater than or equal to 90, the formula returns "A".
    2. A2>=80: If the first condition is false (the score is less than 90), it checks if A2 is greater than or equal to 80. If true, it returns "B".
    3. A2>=70: Similarly, if the score is less than 80, it checks if it's greater than or equal to 70. If true, it returns "C".
    4. A2>=60: If the score is less than 70, the formula checks if it's greater than or equal to 60. If true, it returns "D".
    5. "F": Finally, if none of the above conditions are met (meaning the score is less than 60), the formula returns "F".
Example 2: Sales Commission Calculation

Imagine a scenario where sales representatives receive different commission rates based on their sales achievements. As shown in the screenshot below, you want to calculate the commission of a salesperson based on these different sales thresholds, and nested IF statements can help you with this.

Note: The commission rates and their corresponding sales ranges are listed in the range E2:F4.
  • 20% for sales above $20,000
  • 15% for sales between $10,000 and $20,000
  • 10% for sales below $10,000

Select a blank cell (C2 in this case), enter the following formula and press Enter to get the result. Then drag the Fill Handle down to get the rest of the results.

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

Notes:
  • You can directly specify the commission rate in the formula, so the formula can be changed to:
    =B2*IF(B2>20000, 20%, IF(B2>=10000, 15%, 10%))
  • The formula provided is used to calculate a salesperson's commission based on their sales amount, applying different commission rates for different sales thresholds.
  • Explanation of the formula:
    1. B2: This represents the sales amount for the salesperson, which is used as the base to calculate the commission.
    2. IF(B2>20000, "20%", ...): This is the first condition checked. It checks whether the sales amount in B2 is greater than 20,000. If it is, the formula uses a commission rate of 20%.
    3. IF(B2>=10000, "15%", "10%"): If the first condition is false (sales are not greater than 20,000), the formula checks if the sales are equal to or exceed 10,000. If true, it applies a 15% commission rate. If the sales amount is less than 10,000, the formula defaults to a 10% commission rate.

Nested if with AND / OR condition

In this section, I modify the above first example "the grading system" to demonstrate how to combine nested IF with AND or OR condition in Excel. In the revised grading example, I introduced an additional condition based on "Attendance rate".

Using nested if with AND condition

If a student meets both the score and attendance criteria, they will receive a grade boost. For example, a student whose score is 60 or higher and whose attendance rate is 95% or above will have their grade upgraded by one level, such as from A to A+, B to B+ and so on. However, if the attendance rate is below 95%, the grading will follow the original score-based criteria. In such cases, we need to use a nested IF statement with an AND condition.

Select a blank cell (D2 in this case), enter the following formula and press Enter to get the result. Then drag the Fill Handle down to get the rest of the results.

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Notes: Here's an explanation of how this formula works:
  1. AND condition check:
    AND(B2>=60, C2>=95%): The AND condition first checks if both conditions are met — the student's score is 60 or higher, and their attendance rate is 95% or more.
  2. New grade assignment:
    IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))): If both conditions in the AND statement are true, the formula then checks the student's score and raises his or her grade by one level.
    • B2>=90: If the score is 90 or above, the grade is "A+".New grade assignment:
    • B2>=80: If the score is 80 or above (but less than 90), the grade is "B+".
    • B2>=70: If the score is 70 or above (but less than 80), the grade is "C+".
    • B2>=60: If the score is 60 or above (but less than 70), the grade is "D+".
  3. Regular Grade Assignment:
    IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))): If the AND condition is not met (either the score is below 80 or attendance is below 95%), the formula assigns standard grades.
    • B2>=90: Score 90 or above gets an "A".
    • B2>=80: Score 80 or above (but less than 90) gets a "B".
    • B2>=70: Score 70 or above (but less than 80) gets a "C".
    • B2>=60: Score 60 or above (but less than 70) gets a "D".
    • Scores below 60 get an "F".
Using nested if with OR condition

In this case, a student's grade will be raised one level if their score is 95 or higher, or if their attendance rate is 95% or more. Here's how we can accomplish it using nested IF and OR conditions.

Select a blank cell (D2 in this case), enter the following formula and press Enter to get the result. Then drag the Fill Handle down to get the rest of the results.

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Notes: Here's a breakdown of how the formula works:
  1. OR Condition Check:
    OR(B2>=95, C2>=95%): The formula first checks if either of the conditions is true — the student's score is 95 or higher, or their attendance rate is 95% or higher.
  2. Grade Assignment with Bonus:
    IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))): If either condition in the OR statement is true, the student’s grade will be raised one level.
    • B2>=90: If the score is 90 or above, the grade is "A+".
    • B2>=80: If the score is 80 or above (but less than 90), the grade is "B+".
    • B2>=70: If the score is 70 or above (but less than 80), the grade is "C+".
    • B2>=60: If the score is 60 or above (but less than 70), the grade is "D+".
    • Otherwise, the grade is "F+".
  3. Regular Grade Assignment:
    IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))): If neither of the OR conditions is met (the score is below 95 and attendance is below 95%), the formula assigns standard grades.
    • B2>=90: Score 90 or above gets an "A".
    • B2>=80: Score 80 or above (but less than 90) gets a "B".
    • B2>=70: Score 70 or above (but less than 80) gets a "C".
    • B2>=60: Score 60 or above (but less than 70) gets a "D".
    • Scores below 60 get an "F".

Tips and tricks for nested IF

This section covers four useful tips and tricks for nested IF.


Making nested IF easy to read

A typical nested IF statement might look compact but can be hard to decipher.

In the following formula, it's challenging to quickly identify where one condition ends and another begins, especially as the complexity increases.

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
Solution: Adding Line Breaks and Indentation

To make nested IF easy to read, you can break the formula into multiple lines with each nested IF on a new line. In the formula, simply place the cursor before the IF and press the Alt + Enter keys.

After breaking the above formula, it will shown as follows:

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

This format makes it clearer where each condition and corresponding output is, enhancing the formula's readability.


The order of nested IF functions

The order of logical conditions in a nested IF formula is crucial because it determines how Excel evaluates these conditions and thus affects the final outcome of the formula.

Correct formula

In the Grading System example, we use the following formula to assign grades based on scores.

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

Excel evaluates the conditions in a nested IF formula sequentially, from the first condition to the last. This formula checks the highest score threshold first (>=90 for an "A") and then moves to the lower thresholds. It ensures that a score is compared against the highest grade it qualifies for. If the first condition is true (A2>=90), it returns "A" and does not evaluate any further conditions.

Incorrect ordered formula

If the order of conditions were reversed, starting with the lowest threshold, it would return incorrect results.

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

In this incorrect formula, a score of 95 would immediately meet the first condition B2>=60 and be incorrectly assigned a grade "D".


Numbers and text should be treated differently

This section will show you how numbers and text are treated differently in nested IF statements.

Numbers

Numbers are used for arithmetic comparisons and calculations. In nested IF statements, you can directly compare numbers using operators like >, <, =, >=, and <=.

Text

In nested IF statements, the text should be enclosed in double quotes. See the A, B, C ,D and F in the following formula:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Limitations of nested IF

This section lists several limitations and disadvantages of nested IF.

Complexity and Readability:

Although Excel allows you to nest up to 64 different IF functions, it’s not at all advisable to do so. The more levels of nesting, the more complex the formula becomes. This can lead to formulas that are difficult to read, understand, and maintain.

Error-Prone:

Moreover, Complex nested IF statements can become prone to errors and challenging to debug or modify.

Hard to Extend or Scale:

If your logic changes or you need to add more conditions, deeply nested IFs can be hard to modify or extend.

Understanding these limitations is key to using nested IF statements effectively in Excel. Often, combining nested IFs with other functions or seeking alternative approaches can lead to more efficient and maintainable solutions.


Alternatives to Nested IF

This section lists several functions in Excel that can be used as alternatives to nested IF statements.


Using VLOOKUP

You can use the VLOOKUP function instead of nested IF statements to accomplish the above two practical examples. Here's how you can do it:

Example 1: Grading System with VLOOKUP

Here I will show how to use VLOOKUP to assign grades based on scores.

Step 1: Create a Lookup Table for Grades

Firstly, you need to create a lookup table (such as E1:F6 in this case) for the score range and the corresponding grades. Note: Scores in the first column of the table must be sorted in ascending order.

Step 2: Apply the VLOOKUP function to assign grades

Select a blank cell (C2 in this case), enter the following formula and press the Enter key to get the first grade. Select this formula cell and drag its Fill Handle down to get the rest of the grades.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Notes:
  • The value 95 in cell B2 is what VLOOKUP searches for in the first column of the lookup table ($E$2:$F$6). If found, it returns the corresponding grade from the second column of the table, located in the same row as the matched value.
  • Remember to make the lookup table reference absolute (add the dollar signs ($) before the references), which means the reference will not change if the formula is copied to another cell.
  • To know more about the VLOOKUP function, visit this page.
Example 2: Sales Commission Calculation with VLOOKUP

You also can use VLOOKUP to accomplish the sales commission calculation in Excel. Please do as follows.

Step 1: Create a Lookup Table for Grades

Firstly, you need to create a lookup table for the sales and the corresponding commission rate, such as E2:F4 in this case. Note: Sales in the first column of the table must be sorted in ascending order.

Step 2: Apply the VLOOKUP function to assign grades

Select a blank cell (C2 in this case), enter the following formula and press the Enter key to get the first commission. Select this formula cell and drag its Fill Handle down to get the rest of the results.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

Notes:
  • In both examples, VLOOKUP is used to find a value in a table based on a lookup value (score or sales amount) and returns a value in the same row from a specified column (grade or commission rate). The fourth parameter TRUE indicates an approximate match, which is suitable for these scenarios where the exact lookup value might not be present in the table.
  • To know more about the VLOOKUP function, visit this page.

Using IFS

The IFS function simplifies the process by eliminating the need for nesting and makes the formulas easier to read and manage. It enhances readability and streamlines the handling of multiple conditional checks. To utilize the IFS function, ensure you're using Excel 2019 or later, or have an Office 365 subscription. Let's see how it can be applied in practical examples.

Example 1: Grading System with IFS

Assuming the same grading criteria as before, the IFS function can be used as follows:

Select a blank cell, such as C2, enter the following formula and press Enter to get the first result. Select this result cell and drag its Fill Handle down to get the rest of the results.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")

Notes:
  • Each condition is evaluated in order. As soon as a condition is met, its corresponding result is returned, and the formula stops checking further conditions. In this case, the formula is used to assign grades based on the score in B2, following a typical grading scale where a higher score corresponds to a better grade.
  • To know more about the IFS function, visit this page.
Example 2: Sales Commission Calculation with IFS

For the sales commission calculation scenario, the IFS function is applied as follows:

Select a blank cell, such as C2, enter the following formula and press Enter to get the first result. Select this result cell and drag its Fill Handle down to get the rest of the results.

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)


Using CHOOSE and MATCH

The CHOOSE and MATCH approach can be more efficient and easier to manage compared to nested IF statements. This method simplifies the formula and makes updates or changes more straightforward. Below I will demonstrate how to use a combination of the CHOOSE and MATCH functions to handle the two practical examples in this article.

Example 1: Grading System with CHOOSE and MATCH

You can use the combination of the CHOOSE and MATCH functions to assign grades based on different scores.

Step 1: Create a Lookup array with search values

Firstly, you need to create a range of cells containing the threshold values that MATCH will search through, such as $E$2:$E$6 in this case. Note: The numbers in this range must be sorted in ascending order for the MATCH function to work correctly when using an approximate match type.

Step 2: Apply CHOOSE and MATCH to assign grades

Select a blank cell (C2 in this case), enter the following formula and press the Enter key to get the first grade. Select this formula cell and drag its Fill Handle down to get the rest of the results.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

Notes:
  • MATCH(B2, $E$2:$E$6, 1): This part of the formula looks for the score (95) in cell B2 within the range $E$2:$E$6. The 1 indicates that MATCH should find an approximate match, which means it finds the largest value in the range that is less than or equal to B2.
  • CHOOSE(..., "F", "D", "C", "B", "A"): Based on the position returned by the MATCH function, CHOOSE selects a corresponding grade.
  • To know more about the MATCH function, visit this page.
  • To know more about the CHOOSE function, visit this page.
Example 2: Sales Commission Calculation with IFS

Using the CHOOSE and MATCH combination for a Sales Commission Calculation can also be effective, especially when the commission rates are based on specified sales thresholds. Let’s see how we can do.

Step 1: Create a Lookup array with search values

Firstly, you need to create a range of cells containing the threshold values that MATCH will search through, such as $E$2:$E$4 in this case. Note: The numbers in this range must be sorted in ascending order for the MATCH function to work correctly when using an approximate match type.

Step 2: Apply CHOOSE and MATCH to get the results

Select a blank cell (C2 in this case), enter the following formula and press the Enter key to get the first grade. Select this formula cell and drag its Fill Handle down to get the rest of the results.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

Notes:

In conclusion, mastering nested IF statements in Excel is a valuable skill that enhances your ability to handle complex logical scenarios in data analysis and decision-making processes. While nested IFs are powerful for complex logical operations, it's important to be mindful of their limitations. Simpler alternatives like VLOOKUP, IFS, and CHOOSE with MATCH can provide more streamlined solutions in certain scenarios. Armed with these insights, you can now confidently apply the most appropriate Excel techniques to your data analysis tasks, ensuring clarity, accuracy, and efficiency in your spreadsheets. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations