Multiple IFs In Excel

Updated on January 9, 2024
Article byTwinkle Sethi
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Multiple IF Condition In Excel?

The 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. The additional IF statements can be included in the “value if true” and “value if false” arguments of a standard IF formula.

For example, suppose we have a dataset of students’ scores from B1:B12. We need to grade the students according to their scores. Then, using the IF condition, we can manage the multiple conditions. In this example, we can insert the nested IF formula in cell D1 to assign a grade to a score. We can grade total score as “A,” “B,” “C,” “D,” and “F.” A score would be “F” if it is greater than or equal to 30, “D” if it is greater than 60, and “C” if it is greater than or equal to 70, and “A,” “B” if the score is less than 95. We can insert the formula in D1 with 5 separate IF functions:

=IF(B1>30,”F”,IF(B1>60,”D”,IF(B1>70,”C”,IF(C5>80,”B”,”A”))))

Key Takeaways

  • Multiple IF conditions in Excel are IF statements within another IF statement used to test multiple conditions simultaneously and return distinct values. They can be included in the “value if true” and “value if false” arguments of a standard IF formula.
  • The multiple IF function evaluates logical tests in a formula’s order, ensuring that conditions that are evaluated as “True” are not tested.
  • To evaluate multiple IF conditions step-by-step, use the ‘Evaluate Formula’ feature in Excel on the Formula tab in the ‘Formula Auditing’ group, which displays all the evaluation process steps.

Syntax

The 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 formula is incorporated into the “value_if_false” argument of the previous IF. So, the nested IF excelNested IF ExcelIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read more formula works as follows:

multiple Conditions

Syntax

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

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us learn how to use multiple IFs in Excel with apt and detailed examples.

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

Example #1

Suppose 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 us say the first score is stored in column B, the 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 for the rest of the cells.

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

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

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”)))

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 for the rest of the cells.

Multiple ifs Example 2-2

We can add several “IF” conditions if required similarly.

Example #3

Suppose we wish to test a few sets of different conditions. In that case, those conditions can be expressed using logical OR and 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 and Q2. Then, 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 receives a 7% bonus,
  3. If either of them is greater than 50, then the employee gets a 5% bonus,
  4. If either is greater than 40, then the employee receives 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

Next, drag the formula to get the results of the rest of the cells.

Multiple ifs Example 3-3

Example #4

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

  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 receives 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 receives 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

Next, drag the formula to get results for the rest of the cells.

Drag Example 4-2

Explanation

  • The multiple IF function evaluates the logical tests in the order they appear in a formula. So, for example, as soon as one condition evaluates to be “True,” the following 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. 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 the following conditions.

Correct Order

Correct Order

Incorrect Order

InCorrect Order

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

Important Things To Note

  • 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, the parentheses pairs are shaded in different colors so that the opening parentheses match the closing ones.
  • Also, on closing the parenthesis, the matching pair is highlighted.

Frequently Asked Questions

1. What is the alternative to multiple IFs in Excel?

Create a reference table, use VLOOKUP with an approximate match, use IF with logical functions OR / AND, and use an array formula instead of nested IF in Excel.

2. What is the difference between IF-else if and multiple IFS?

Unlike the if-else if statement, which only executes the latter if statement if the first if statement is false, multiple if statements are guaranteed to execute all of them.

3. Can you use multiple IF functions in one cell?

Excel allows nesting up to 64 different IF functions. Still, it’s only advisable due to the complexity of building and ensuring the logic can be calculated correctly through each condition at the end of the statement.

4. How to evaluate formula?

Evaluate the formula logic– To see the step-by-step evaluation of multiple IF conditions, we can use the “Evaluate Formula” feature in Excel on the “Formula” tab in the “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 the first logical testLogical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more of multiple IF formulas will go as D2>=70; 85>=70; True; Excellent.

Multiple ifs Evaluate

This article is a guide to Multiple IF Conditions in Excel. We discuss using multiple IF conditions, practical examples, and a downloadable Excel template. You may also learn more about Excel from the following articles: –