## 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.

- Making nested IF easy to read
- The order of nested IF functions
- Numbers and text should be treated differently
- Limitations of nested IF

### 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:
**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".**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".**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".**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".- "
**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:
**B2**: This represents the sales amount for the salesperson, which is used as the base to calculate the commission.**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%.**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:

**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.**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+".

**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:

**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.**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+".

**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".

#### 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**:

- To know more about the
**MATCH function**, visit this page. - To know more about the
**CHOOSE function**, visit this page.

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.

### Related Articles

**Use IF function with AND, OR, and NOT in Excel**

When combined with logical operators like AND, OR, and NOT, the IF function's capabilities significantly expand. The power of the combination lies in their ability to process multiple conditions simultaneously, providing results that can adapt to varied and complex scenarios. In this tutorial, we will explore how to effectively leverage these powerful functions in Excel to unlock new dimensions of data analysis and enhance your decision-making process.

**Conditional Drop-Down List with IF Statement**

This tutorial demonstrates 5 methods that will assist you in creating a conditional drop-down list in Excel step-by-step.

**Power Query: If statement - nested ifs & multiple conditions**

In Excel Power Query, the IF statement is one of the most popular functions to check a condition and return a specific value depending on whether the result is TRUE or FALSE. There are some differences between this if statement and the IF function of Excel. In this tutorial, I will introduce the syntax of this if statement and some simple and complex examples for you.

### Best Office Productivity Tools

**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...**

#### 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!

### Table of contents

- IF function vs. Nested IF statements
- Usage of nested IF
- Syntax of nested IF
- Practical examples of nested IF
- Nested if with AND / OR condition
- Tips and tricks for nested IF
- Making nested IF easy to read
- The order of nested IF functions
- Numbers and text should be treated differently
- Limitations of nested IF
- Alternatives to Nested IF
- Using VLOOKUP
- Using IFS
- Using CHOOSE and MATCH
- Related Articles
- The Best Office Productivity Tools
- Comments