IFS Function in Excel Tutorial

Excel Tutorial

Introduction

The IFS function in Excel checks multiple conditions and returns a result for the first condition that evaluates to TRUE. It is useful when you need to assign grades, calculate performance ratings, classify sales, or create categories based on different conditions.

Unlike a long Nested IF formula, IFS allows you to write multiple conditions in a simpler and more readable structure.

What Is the IFS Function in Excel?

The IFS function evaluates multiple conditions in order.

  • Excel checks the first condition.
  • If it is TRUE, Excel returns its corresponding result.
  • If it is FALSE, Excel checks the next condition.
  • This continues until a condition is TRUE.
  • If no condition is TRUE, IFS returns the #N/A error.

IFS Function Syntax

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Arguments:

  • logical_test1: The first condition to check.
  • value_if_true1: The result when the first condition is TRUE.
  • logical_test2: The next condition to check.
  • value_if_true2: The result when the second condition is TRUE.

You can add more condition-result pairs as required.

Practical Examples of IFS Function in Excel

Question 1: Assign Student Grades Based on Marks

Problem Statement: A teacher wants to assign grades to students based on their examination marks.

Conditions:

  • 90 or above = Grade A
  • 75 or above = Grade B
  • 60 or above = Grade C
  • 40 or above = Grade D
  • Below 40 = Fail

Student Data:

Student NameMarksGrade
Rahul95
Priya82
Amit68
Neha45
Karan32

Excel Formula (C2):

=IFS(B2>=90,"Grade A",B2>=75,"Grade B",B2>=60,"Grade C",B2>=40,"Grade D",B2<40,"Fail")

Expected Result:

Student NameMarksGrade
Rahul95Grade A
Priya82Grade B
Amit68Grade C
Neha45Grade D
Karan32Fail

Explanation: Excel checks each marks condition in order and returns the grade for the first TRUE condition.

Concept Covered: Assigning multiple categories based on numerical ranges.

Question 2: Classify Employee Performance

Problem Statement: An HR department wants to classify employees based on their performance scores.

Conditions:

  • 90 or above = Outstanding
  • 80 or above = Excellent
  • 70 or above = Good
  • 50 or above = Average
  • Below 50 = Poor

Employee Data:

Employee NamePerformance ScoreRating
Rahul96
Priya84
Amit72
Neha58
Karan42

Excel Formula (C2):

=IFS(B2>=90,"Outstanding",B2>=80,"Excellent",B2>=70,"Good",B2>=50,"Average",B2<50,"Poor")

Expected Result:

Employee NamePerformance ScoreRating
Rahul96Outstanding
Priya84Excellent
Amit72Good
Neha58Average
Karan42Poor

Explanation: IFS checks the performance score against each threshold and returns the corresponding rating.

Concept Covered: Creating employee performance categories.

Question 3: Calculate Employee Incentive Percentage

Problem Statement: A company wants to assign incentive percentages based on monthly sales.

Conditions:

  • Sales of ₹5,00,000 or above = 20%
  • Sales of ₹3,00,000 or above = 15%
  • Sales of ₹2,00,000 or above = 10%
  • Sales below ₹2,00,000 = 0%

Employee Data:

Employee NameMonthly SalesIncentive %
Rahul600000
Priya400000
Amit250000
Neha150000

Excel Formula (C2):

=IFS(B2>=500000,20%,B2>=300000,15%,B2>=200000,10%,B2<200000,0%)

Expected Result:

Employee NameMonthly SalesIncentive %
Rahul60000020%
Priya40000015%
Amit25000010%
Neha1500000%

Explanation: Excel checks the sales amount and returns the incentive percentage for the first matching condition.

Concept Covered: Returning numerical and percentage results.

Question 4: Classify Products Based on Stock Quantity

Problem Statement: An inventory department wants to classify products according to their 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:

ProductAvailable StockStock Status
Laptop150
Keyboard75
Mouse20
Monitor0

Excel Formula (C2):

=IFS(B2>=100,"High Stock",B2>=50,"Medium Stock",B2>=1,"Low Stock",B2=0,"Out of Stock")

Expected Result:

ProductAvailable StockStock Status
Laptop150High Stock
Keyboard75Medium Stock
Mouse20Low Stock
Monitor0Out of Stock

Explanation: IFS evaluates stock quantities from the highest threshold to the lowest and returns the appropriate inventory category.

Concept Covered: Classifying inventory data using multiple conditions.

Question 5: Categorize Customer Purchase Levels

Problem Statement: A company wants to classify customers based on their total purchase amounts.

Conditions:

  • Purchase of ₹1,00,000 or above = Platinum
  • Purchase of ₹50,000 or above = Gold
  • Purchase of ₹20,000 or above = Silver
  • Purchase below ₹20,000 = Regular

Customer Data:

Customer NameTotal PurchaseCustomer Category
Rahul150000
Priya75000
Amit35000
Neha12000

Excel Formula (C2):

=IFS(B2>=100000,"Platinum",B2>=50000,"Gold",B2>=20000,"Silver",B2<20000,"Regular")

Expected Result:

Customer NameTotal PurchaseCustomer Category
Rahul150000Platinum
Priya75000Gold
Amit35000Silver
Neha12000Regular

Explanation: Excel compares each customer’s purchase amount with the defined thresholds and assigns the corresponding category.

Concept Covered: Creating customer categories using IFS.

Important Things to Remember

1. Condition Order Matters

IFS checks conditions from left to right. Arrange numerical thresholds carefully, usually from highest to lowest, to avoid an earlier condition matching too soon.

2. First TRUE Condition Wins

Excel returns the result associated with the first TRUE condition. It does not continue checking the remaining conditions.

3. What Happens When No Condition Is TRUE?

IFS returns #N/A if none of the conditions is TRUE. Make sure your conditions cover all possible values when necessary.

4. Use a Final Condition for Remaining Values

For a complete classification, you can use TRUE as the final condition to provide a default result.

Example:

=IFS(B2>=90,"Excellent",B2>=70,"Good",TRUE,"Needs Improvement")

Student Practice Tasks

Task 1: Classify Student Results

StudentMarksResult
Aman95
Riya78
Mohit55
Simran32

Conditions:

  • 90 or above = Excellent
  • 70 or above = Good
  • 40 or above = Pass
  • Below 40 = Fail

Use IFS to assign the result.

Task 2: Classify Employee Experience

EmployeeExperience (Years)Category
Rahul9
Priya5
Amit2
Neha0

Conditions:

  • 7 or above = Senior
  • 4 or above = Experienced
  • 1 or above = Fresher
  • Below 1 = Trainee

Use IFS to assign employee categories.

Final Student Challenge: Build a Sales Performance Classification Sheet

Create an Excel sheet that automatically classifies employees based on monthly sales.

Conditions:

  • Sales of ₹5,00,000 or above = Star Performer
  • Sales of ₹4,00,000 or above = Excellent
  • Sales of ₹3,00,000 or above = Good
  • Sales of ₹2,00,000 or above = Average
  • Below ₹2,00,000 = Needs Improvement

Given Data:

Employee NameMonthly SalesPerformance Category
Rahul650000
Priya450000
Amit350000
Neha250000
Karan150000

Your Challenge: Write an IFS formula in the Performance Category column and apply it to all employees.

Key Takeaways

  • IFS checks multiple conditions in one formula.
  • It returns the result for the first TRUE condition.
  • Conditions are evaluated from left to right.
  • It can return text, numbers, and percentages.
  • Condition order is important for accurate results.
  • IFS can simplify formulas that would otherwise require multiple nested IF functions.

Frequently Asked Questions (FAQs)

1. What is the IFS function in Excel?

The IFS function checks multiple conditions and returns a result for the first condition that evaluates to TRUE.

2. What is the syntax of the IFS function?

The syntax is =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...).

3. What happens if multiple conditions are TRUE?

IFS returns the result associated with the first TRUE condition and ignores the remaining conditions.

4. What happens if none of the conditions is TRUE?

IFS returns the #N/A error when no condition evaluates to TRUE.

5. Can I use TRUE as the final condition in IFS?

Yes. TRUE can be used as the final condition to provide a default result when none of the earlier conditions is satisfied.

6. What is the difference between IF and IFS?

IF generally checks one condition and provides two possible outcomes. IFS checks multiple conditions and provides a separate result for each condition.

7. Can IFS return percentages and numbers?

Yes. IFS can return text, numbers, percentages, and other supported values.

8. Which Excel versions support the IFS function?

IFS is supported in Excel 2019, Excel 2021, Excel 2024, and Microsoft 365. It is also available in Excel for the web.