Multiple IFs in Excel

Multiple IF Condition in Excel

Multiple IF conditions in Excel are IF statements contained within another IF statement. They are used to test multiple conditions simultaneously and return distinct values. Additional IF statements can be included in the ‘value if true’ and ‘value if false’ arguments of a standard IF formula.

Explanation

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

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

multiple Conditions

Syntax

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

Examples

You can download this Multiple Ifs Excel Template here – Multiple Ifs Excel Template

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.

Multiple ifs Example 1

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

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

Multiple ifs Example 1-1

This Formula returns the Result as given below:

Multiple ifs Example 1-2

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

Multiple ifs Example 1-3

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

Example #2

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

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

Multiple ifs Example 2

This Formula returns the Result as given below:

Multiple ifs Example 2-1

Excellent: >=70

Good: Between 60 & 69

Average: Between 41 & 59

Bad: <=40

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

Multiple ifs Example 2-2

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

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.

Multiple ifs Example 3

We can make a formula with the logic:

  1. If either Q1 or Q2 targets are greater than 70, then the employee gets a 10% bonus,
  2. 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,
  1. 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%,””))))

Multiple ifs Example 3-1

This Formula returns the Result as given below:

Multiple ifs Example 3-2

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

Multiple ifs Example 3-3

Example #4

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

  1. If both Q1 and Q2 targets are greater than 70, then the employee gets a 10% bonus
  2. if both of them are greater than 60, then the employee gets a 7% bonus
  3. if both of them are greater than 50, then the employee gets a 5% bonus
  4. if both of them are greater than 40, then the employee gets a 3% bonus
  5. 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%,””))))

Multiple ifs Example 4

This formula returns the Result as given below:

Multiple ifs Example 4-1

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

Drag Example 4-2

Things to Remember

  • The multiple IF function 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 multiple IF condition 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

Correct Order

Incorrect Order

InCorrect Order

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

Evaluate
  • 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 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 are getting difficult to maintain in Excel.

Recommended Articles

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

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>