EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Nested IF Formula in Excel
 

Nested IF Formula in Excel

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 21, 2023

Nested IF Formula in Excel

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

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

You can download this Nested IF Formula Excel Template here – Nested IF Formula Excel Template

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

Nested IF Formula Example 1-1

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

Nested IF Formula Example 1-2

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

Nested IF Formula Example 1-3

Note: I simply added another IF statement in the abovementioned syntax into the first IF statement. Similarly, I extend the formula to handle the next category, “SECOND CLASS”, where I repeat the above-mentioned step.

=IF(C3>=85,”DISTINCTION”, IF(C3>=60, “FIRST CLASS”, IF(C3>=35, “SECOND CLASS”,

Nested IF Formula Example 1-4

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

Nested IF Formula Example 1-5

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

Nested IF Formula Example1.6 png

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.

NestedIF Formula

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.

Nestedif Formula Parenthesis

  • 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

Nestedif Formula - Parenthesis 1

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

NestedIf Formula- Evaluate formula

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

Nested IF formula -Evalute formula

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-

  1. COUNTIF Formula in Excel
  2. COUNTIF Excel Function
  3. Excel IF Function
  4. Excel IF AND Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Nested IF Formula Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Nested IF Formula Excel Template

EDUCBA

डाउनलोड Nested IF Formula Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW