Excel Functions Tutorials

- Logical Functions in Excel
- AND Function in Excel
- False in Excel
- Excel Greater Than or Equal to
- IF Excel Function
- IF Formula in Excel
- IF AND in Excel
- Nested IF in Excel (Multiple IFs)
- Excel Nested If Function
- IFERROR Excel Function
- Logical Test in Excel
- Max IF in Excel
- NOT Excel Function
- OR Function in Excel
- SWITCH Excel Function
- True Excel Function

- Financial Functions in Excel (17+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)

Related Courses

In excel data, there are situations where we need to use more than one or two conditions to find out a specific data, meager using If function or if function with a logical function may not be helpful so we can use multiple If statements inside a single If statement, in nested if statement if the condition is met then the result Is displayed but if the condition is not met then next if statement is executed.

**Excel Nested If (Table of Contents)**

## Nested IF Excel Function (Multiple IF)

‘IF’ formula is used when we wish to test a condition and return one value if the condition is met and another value if it is not met.

Multiple IF or Nested If in excel is an IF statement inside another IF statement. We can include additional IF statements in the ‘value_if_true’ and ‘value_if_false’ arguments of normal IF formula in Excel.

Each subsequent IF is incorporated into the ‘value_if_false’ argument of the previous IF. So, the nested IF excel formula works as follows:

General Syntax for multiple/nested IF function in Excel is as follows:

**IF (condition1, result1, IF (condition2, result2, IF (condition3, result3,………..)))**

**Where to use Multiple if Statements in Excel?**

When we need to test more than one condition at the same time and return different values, we use Nested IF or Multiple IF’s in Excel. Microsoft Excel allows up to 64 different IF functions inside Nested IF in Excel.

A simple example would be to have 3 answers (Yes, No, Maybe) instead of 2 answers (Yes, No) for an exam question which cannot be done by a simple IF statement.

### Examples of Nested If Excel Function

Some examples of multiple if statements are provided below.

#### Nested IF Function in Excel Example #1

If we wish to find how a student scores in an exam. There are two exam scores of a student and we define the total score (sum of the two scores) as ‘Good’, “Average’ and ‘Bad’. A score would be ‘Good’ if it is greater than or equal to 60, ‘Average’ if it is between 40 and 60, and ‘Bad’ if it is less than or equal to 40.

Let’s say the first score is stored in column B, second in column C.

4.9 (1,353 ratings)

The following formula tells Excel to return ‘Good’, ‘Average’ or ‘Bad’:

**=IF(D2>=60,”Good”,IF(D2>40,”Average”,”Bad”))**

This Formula returns the Result as given below:

Drag the Formula to get Results to the rest of cells.

We can see that one nested IF Function is sufficient in this case as we need to get only 3 results.

#### Nested IF Function in Excel Example #2

Now, let’s say we want to test one more condition in the above examples: the total score of 70 and above is categorized as “Excellent”.

**=IF(D2>=70,”Excellent”,IF(D2>=60,”Good”,IF(D2>40,”Average”,”Bad”)))**

This Formula returns the Result as given below:

**Excellent: >=70**

**Good: Between 60 & 69**

**Average: Between 41 & 59**

**Bad: <=40**

Drag the Formula to get Results to the rest of cells.

We can add a number of ‘If’ conditions if required in a similar manner.

#### Nested IF Function in Excel Example #3

If we wish to test a few sets of different conditions, then those conditions can be expressed using logical OR & AND, nesting the functions inside IF statements, and then nesting the IF statements into each other.

For instance, if we have two columns containing the number of targets made by an employee in 2 quarters: Q1 & Q2, and we wish to calculate the performance bonus of the employee based on a higher target number.

We can make a formula with the logic:

- If either Q1 or Q2 targets are greater than 70, then employee gets 10% bonus,
- if either of them is greater than 60, then the employee gets a 7% bonus,

- if either of them is greater than 50, then the employee gets a 5% bonus,

- if either of them is greater than 40, then the employee gets a 3% bonus, else no bonus.

So, we first write a few OR statements like (B2>=70,C2>=70), and then nest them into logical tests of IF functions as follows:

**=IF(OR(B2>=70,C2>=70),10%,IF(OR(B2>=60,C2>=60),7%, IF(OR(B2>=50,C2>=50),5%, IF(OR(B2>=40,C2>=40),3%,””))))**

This Formula returns the Result as given below:

Drag the Formula to get Results of the rest of cells.

#### Nested IF Function in Excel Example #4

Now, let’s say we want to test one more condition in the above example as:

- If both Q1 and Q2 targets are greater than 70, then employee gets 10% bonus
- if both of them are greater than 60, then the employee gets a 7% bonus
- if both of them are greater than 50, then the employee gets a 5% bonus
- if both of them are greater than 40, then the employee gets a 3% bonus
- Else, no bonus.

So, we first write a few AND statements like (B2>=70,C2>=70), and then nest them: tests of IF functions as follows:

**=IF(AND(B2>=70,C2>=70),10%,IF(AND(B2>=60,C2>=60),7%, IF(AND(B2>=50,C2>=50),5%, IF(AND(B2>=40,C2>=40),3%,””))))**

This formula returns the Result as given below:

Drag the Formula to get Results to the rest of cells.

**Things to Remember**

**Order of nested IF functions in Excel**– The multiple IF function in Excel evaluates the logical tests in the order they appear in a formula, and as soon as one condition evaluates to true, subsequent conditions are not tested.- For instance, if we consider the second example discussed above, the nested IF formula in Excel evaluates the first logical test (D2>=70), and returns ‘Excellent’ because the condition is true in the below formula:

**=IF(D2>=70,”Excellent”,IF(D2>=60,,”Good”,IF(D2>40,”Average”,”Bad”))**

Now, if we reverse the order of IF functions in Excel as follows:

**=IF(D2>40,”Average”,IF(D2>=60,,”Good”,IF(D2>=70,”Excellent”,”Bad”))**

In this case, the formula tests the first condition, and since 85 is greater than or equal to 70, a result of this condition is also True, so the formula would return ‘Average’ instead of ‘Excellent’ without testing subsequent conditions.

**Correct Order**

**Incorrect Order**

**Note:** Changing the order of IF function in Excel would change the result.

**Evaluate the formula logic**– To see the step-by-step evaluation of multiple IF Conditions in Excel we can use the ‘Evaluate Formula’ feature in the Formula tab in Formula Auditing Group. Clicking the ‘Evaluate’ button will show all the steps in the evaluation process.- For instance, in the second example the evaluation of first logical test of nested IF formula will go as D2>=70; 85>=70; True; Excellent

**Balancing the parentheses**: If the parentheses do not match in terms of number and order, then the multiple IF formula would not work.- If we have more than one set of parentheses, then the parentheses pairs are shaded in different colors so that opening parentheses matches the closing ones.
- Also, on closing the parenthesis, the matching pair is highlighted.

**Numbers and Text should be treated differently**: In the multiple/nested IF formula, the text should always be enclosed in double quotes.**Multiple IF’s can often become troublesome**: It becomes difficult to manage so many true & false conditions and closing brackets in one statement. It is always good to use other tools like IF function or VLOOKUP in case Multiple IF’s is getting difficult to maintain in Excel.

### Recommended Articles

This has been a guide to Nested IF in Excel. Here we discuss how to use multiple if Conditions using Nested IF in excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

- How to use IF with OR Function in VBA?
- IFERROR Function in VBA
- Greater Than or Equal to
- Logical Test in Excel
- Countif not Blank in Excel
- Formula for Grade in Excel
- ISNA Excel Function
- Use COUNTIF in Excel
- Using IFERROR Excel Function
- How to use SUMIF Excel Function?

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion