Updated August 21, 2023
Nested IF Formula in Excel
In this article, we will learn the Nested IF Formula in Excel. It is one of Excel’s most commonly & frequently used logical functions.
Usually, the IF function runs a logical test & checks whether a condition or criteria is met and returns one value in a result. It may be either true and another value if false. These are the two possible outcomes with the if function.
Sometimes you need to work with situations or conditions with more than two possible outcomes; in this scenario, the Nested IF Formula helps you out.
Nesting means a combination of formulas, one inside the other, where each formula controls or handles the result of others.
Nested IF Formula is categorized under Advanced IF functions, allowing you to check multiple conditions.
From Excel 2007 version onwards, 64 IF statements or functions can use in one formula (In Nested IF Formula)
Nested IF Formula: It’s an If function within an if function to test multiple conditions.
Syntax of Nested IF Formula:
=IF(condition, value_if_true1, IF(second condition, value_if_true2, value_if_false2 ))
The Nested IF Formula syntax or formula has the below-mentioned arguments:
- Condition: It is the value that you want to test.
- value_if_true: The value appears or returns if the logical condition evaluates to TRUE.
- value_if_false: The value appears or returns if the logical condition evaluates FALSE.
Various arithmetic operators can use in the Nested IF Formula:
> Greater Than
= Equal to
< Less Than
>= Greater than or equal to
<= Less than or equal to
<> Less than or greater than
These above-mentioned operators are used in the Criteria or Condition argument of the Nested IF Formula’s statement; it is purely based on the logic you apply in the criteria argument.
How to Use NESTED IF Formula in Excel?
Let’s check out how this formula Formula works in Excel
Example #1
Let us analyze the Nested IF Formula with Multiple Criteria.
In the below-mentioned example, the table contains a list of the student in column B (B2 to B18) & the score of each student (C2 to C18).
Here, I want to categorize their scores with the below-mentioned conditions:
DISTINCTION: Over 85.
FIRST CLASS: Between 60 and 84, inclusive.
SECOND CLASS: Between 35 and 59, inclusive.
FAIL: Under 35.
With the above conditions, I must categorize students’ results based on their Here Nested IF Formula score. I need to build that formula with multiple IF statements.
Let’s start entering the first IF statement:
=IF(C3>85,”DISTINCTION”,
If I want to handle the second category, this takes care of the “DISTINCTION” category students. “FIRST CLASS”, I need to add another conditional statement:
=IF(C3>=85,”DISTINCTION”, IF(C3>=60, “FIRST CLASS”,
=IF(C3>=85,”DISTINCTION”, IF(C3>=60, “FIRST CLASS”, IF(C3>=35, “SECOND CLASS”,
I continue with similar steps until I reach the last category. Now, I have left with the last category, “FAIL” if the last category or criteria appears. Instead of adding another IF, I must add the “FAIL” for a false argument (value_if_false argument).
After entering the last category, you must close it with three closed brackets.
In the last criterion, the value_if_false argument, if the score is less than 35, the IF function returns FALSE, as we don’t supply a value if false.
=IF(C3>=85,”DISTINCTION”,IF(C3>=60,”FIRST CLASS”,IF(C3>=35,”SECOND CLASS”,”FAIL”)))
The Nested IF Formula is ready; copy this formula in the cell “D3” and click on enter to get the result. Simultaneously this formula is applied to the whole range by selecting a cell from “D3” to “D18” and clicking on CTRL + D to get the result.
How Excel Nested IF Logical Test Works?
=IF(C3>=85,”DISTINCTION”,IF(C3>=60,”FIRST CLASS”,IF(C3>=35,”SECOND CLASS”,”FAIL”)))
Now let’s split or break up the above formula and check it out.
=IF(C3>=85,”DISTINCTION”,
IF(C3>=60,”FIRST CLASS”,
IF(C3>=35,”SECOND CLASS”,”FAIL”)))
Or
IF(check if C3>=85, if true – return “DISTINCTION”, or else
IF(check if C3>=60, if true – return “FIRST CLASS”, or else
IF(check if C3>=35, if true – return “SECOND CLASS”, if false –
return “FAIL” )))
Here, the Nested IF formula directs Excel to evaluate the logical test for the first IF function; in the result, if the condition or criteria is met, then it returns the supplied value (“DISTINCTION”) in the value_if_true argument. Otherwise or else, If the condition or criteria of the first If function is not met, then go ahead and carry out or test the second If statement and follow a similar step until the last criteria.
Conclusion
In the screenshot below, you can observe that parenthesis pairs are usually shaded in various or different colors so that the opening parenthesis matches the closing one at the end.
- Text values should always be enclosed in double quotes “DISTINCTION.”
Note: Never present the numbers or numeric values in the double quotes, e.g. 85, 60 & 35
- You can add line break or spaces in the formula bar to understand this Formula in a better & easier way.
- Evaluate Formula feature is located on the Formula tab under the Formula Auditing group or subsection. This feature helps out to solve your complex formula. Nested IF Formula step by step.
When you click the evaluate button frequently, it will show you all the evaluation process steps (From beginning to end, how the formula works step-by-step).
Things to Remember
- When using the Nested IF Formula, we should not start the second criteria in the IF function with the = sign.
- Apart from arithmetic operators, you can also use addition, subtraction, multiplication & division symbols, e.g. =IF(C1<10, C1*4,
- The order of IF statements in the Nested IF Formula is very important to evaluate the logical test. The formula stops at the first result TRUE. If the IF function’s first condition evaluates TRUE, subsequent conditions or IF statements don’t work.
- Parenthesis Match: It is an important Criterion in the Nested IF formula; if the parentheses do not match, the formula won’t work.
Recommended Articles
This has been a guide to Nested IF Formula in Excel. Here we discuss How to use the Nested IF Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles-
