Introduction
The Nested IF function in Excel allows you to check multiple conditions and return different results based on those conditions. It is useful when a single IF function is not enough to handle multiple possible outcomes.
For example, you can use Nested IF to assign student grades, calculate employee performance ratings, classify sales results, or determine salary categories. In this tutorial, you will learn how Nested IF works through practical examples.
What Is Nested IF in Excel?
Nested IF means placing one IF function inside another IF function.
It allows Excel to test multiple conditions in sequence. Excel checks the first condition. If it is FALSE, it moves to the next IF condition until it finds a TRUE condition or reaches the final result.
Nested IF Syntax
=IF(condition1, value_if_true, IF(condition2, value_if_true, value_if_false))
Main Arguments:
condition1: The first condition Excel checks.value_if_true: The result when the first condition is TRUE.condition2: The next condition Excel checks if the first condition is FALSE.value_if_false: The result when the remaining condition is FALSE.
You can add more IF functions inside one another to check additional conditions.
Practical Examples of Nested IF in Excel
Question 1: Assign Student Grades Based on Marks
Problem Statement: A teacher wants to assign grades according to students’ marks.
Conditions:
- 90 or above = Grade A
- 75 to 89 = Grade B
- 60 to 74 = Grade C
- 40 to 59 = Grade D
- Below 40 = Fail
Student Data:
| Student Name | Marks | Grade |
|---|---|---|
| Rahul | 95 | |
| Priya | 82 | |
| Amit | 68 | |
| Neha | 45 | |
| Karan | 32 |
Excel Formula (C2):
=IF(B2>=90,"Grade A",IF(B2>=75,"Grade B",IF(B2>=60,"Grade C",IF(B2>=40,"Grade D","Fail"))))
Expected Result:
| Student Name | Marks | Grade |
|---|---|---|
| Rahul | 95 | Grade A |
| Priya | 82 | Grade B |
| Amit | 68 | Grade C |
| Neha | 45 | Grade D |
| Karan | 32 | Fail |
Explanation: Excel checks the marks from the highest range to the lowest. When a condition is TRUE, it returns that grade. If all conditions are FALSE, it returns Fail.
Concept Covered: Multiple conditions with different text results.
Question 2: Classify Employee Performance
Problem Statement: An HR department wants to classify employee performance based on performance scores.
Conditions:
- 90 or above = Excellent
- 75 to 89 = Good
- 60 to 74 = Average
- Below 60 = Needs Improvement
Employee Data:
| Employee Name | Performance Score | Rating |
|---|---|---|
| Rahul | 95 | |
| Priya | 80 | |
| Amit | 68 | |
| Neha | 52 |
Excel Formula (C2):
=IF(B2>=90,"Excellent",IF(B2>=75,"Good",IF(B2>=60,"Average","Needs Improvement")))
Expected Result:
| Employee Name | Performance Score | Rating |
|---|---|---|
| Rahul | 95 | Excellent |
| Priya | 80 | Good |
| Amit | 68 | Average |
| Neha | 52 | Needs Improvement |
Explanation: Excel evaluates each score in sequence and returns the matching performance category.
Concept Covered: Creating performance categories using Nested IF.
Question 3: Calculate Employee Bonus Percentage
Problem Statement: A company wants to decide employee bonus percentages based on annual sales.
Conditions:
- Sales of ₹5,00,000 or above = 20% bonus
- Sales of ₹3,00,000 or above = 15% bonus
- Sales of ₹1,00,000 or above = 10% bonus
- Below ₹1,00,000 = 0% bonus
Employee Data:
| Employee Name | Annual Sales | Bonus % |
|---|---|---|
| Rahul | 600000 | |
| Priya | 400000 | |
| Amit | 200000 | |
| Neha | 80000 |
Excel Formula (C2):
=IF(B2>=500000,20%,IF(B2>=300000,15%,IF(B2>=100000,10%,0%)))
Expected Result:
| Employee Name | Annual Sales | Bonus % |
|---|---|---|
| Rahul | 600000 | 20% |
| Priya | 400000 | 15% |
| Amit | 200000 | 10% |
| Neha | 80000 | 0% |
Explanation: Excel checks the sales amount against each threshold and returns the corresponding bonus percentage. The formula returns 0% when sales are below ₹1,00,000.
Concept Covered: Returning numeric values and percentages through Nested IF.
Question 4: Classify Product Stock Status
Problem Statement: An inventory department wants to classify products based on available stock.
Conditions:
- Stock of 100 or above = High Stock
- Stock of 50 or above = Medium Stock
- Stock of 1 or above = Low Stock
- Stock equal to 0 = Out of Stock
Product Data:
| Product | Available Stock | Stock Status |
|---|---|---|
| Laptop | 150 | |
| Keyboard | 75 | |
| Mouse | 20 | |
| Monitor | 0 |
Excel Formula (C2):
=IF(B2>=100,"High Stock",IF(B2>=50,"Medium Stock",IF(B2>=1,"Low Stock","Out of Stock")))
Expected Result:
| Product | Available Stock | Stock Status |
|---|---|---|
| Laptop | 150 | High Stock |
| Keyboard | 75 | Medium Stock |
| Mouse | 20 | Low Stock |
| Monitor | 0 | Out of Stock |
Explanation: Excel checks stock levels in descending order and returns the appropriate inventory status.
Concept Covered: Using Nested IF to classify business data.
Important Concepts to Understand
1. Order of Conditions
Always arrange conditions carefully. When checking ranges, start with the highest threshold and move towards the lowest. Otherwise, a broader condition may become TRUE too early and return an incorrect result.
2. Nested IF Structure
Each additional condition requires another IF function inside the previous IF function. Make sure every opening parenthesis has a matching closing parenthesis.
3. Final Else Result
The last argument provides the result when none of the earlier conditions is TRUE. For example, it can return “Fail”, “Needs Improvement”, or 0%.
4. Text and Number Results
Nested IF can return text, numbers, percentages, dates, or cell references depending on the requirement.
Student Practice Tasks
Try solving these tasks independently using Nested IF.
Task 1: Student Result Classification
| Student | Marks | Result |
|---|---|---|
| Aman | 92 | |
| Riya | 76 | |
| Mohit | 55 | |
| Simran | 35 |
Conditions: 90 or above = Excellent, 70 or above = Good, 40 or above = Pass, otherwise Fail.
Task 2: Employee Experience Category
| Employee | Experience (Years) | Category |
|---|---|---|
| Rahul | 8 | |
| Priya | 5 | |
| Amit | 2 | |
| Neha | 0 |
Conditions: 7 or above = Senior, 4 or above = Experienced, 1 or above = Fresher, otherwise Trainee.
Final Student Challenge: Build an Employee Performance Rating System
Create an Excel sheet that automatically assigns performance ratings based on employee scores.
Given Data:
| Employee Name | Performance Score | Rating |
|---|---|---|
| Rahul | 96 | |
| Priya | 84 | |
| Amit | 72 | |
| Neha | 58 | |
| Karan | 35 |
Conditions:
- 90 or above = Outstanding
- 80 or above = Excellent
- 70 or above = Good
- 50 or above = Average
- Below 50 = Poor
Your Challenge: Write a Nested IF formula in the Rating column and apply it to all employees.
Key Takeaways
- Nested IF allows you to check multiple conditions in one formula.
- It places one IF function inside another IF function.
- Excel evaluates conditions in order.
- You can return different text, numbers, and percentages.
- Condition order is important for accurate results.
- The final ELSE result handles cases where none of the conditions is TRUE.
Frequently Asked Questions (FAQs)
1. What is Nested IF in Excel?
Nested IF is a formula that places one IF function inside another to check multiple conditions and return different results.
2. What is the difference between IF and Nested IF?
A normal IF checks one condition, while Nested IF can check multiple conditions in sequence.
3. Can Nested IF check multiple conditions?
Yes. You can add multiple IF functions inside one another to evaluate several conditions.
4. How many IF functions can be nested in Excel?
Modern Excel supports up to 64 levels of nested functions.
5. Can Nested IF return text and numbers?
Yes. It can return text, numbers, percentages, dates, or references based on the formula.
6. Does the order of conditions matter in Nested IF?
Yes. Excel checks conditions in sequence, so arrange them carefully to avoid incorrect results.
7. Can I use Nested IF for employee salary calculations?
Yes. You can use it to classify salary ranges, calculate incentives, assign bonus percentages, and determine employee categories.
8. What is the alternative to Nested IF in Excel?
For some tasks, IFS, SWITCH, or lookup functions can make formulas easier to read. The best choice depends on the type of conditions.

