The "IFS()" function lets you test multiple conditions without using complex nested "IF()" statements. It makes formulas cleaner, easier to read, and easier to maintain.
ยซNote: The "IFS()" function is available in Excel 2019, Excel 2021, and Microsoft 365.ยป
๐ What is the IFS() Function?
The "IFS()" function evaluates multiple conditions in order and returns the value for the first TRUE condition.
Syntax:
=IFS(logical_test1, value_if_true1,
logical_test2, value_if_true2,
...)
๐ Example 1 โ Student Grades
Marks: 95, 82, 68, 45
Formula:
=IFS(B2>=90,"A",B2>=75,"B",B2>=50,"C",B2<50,"Fail")
Result:
โข 95 โ A
โข 82 โ B
โข 68 โ C
โข 45 โ Fail
๐ Example 2 โ Sales Performance
Sales: โน180,000, โน120,000, โน70,000, โน30,000
Formula:
=IFS(A2>=150000,"Excellent",
A2>=100000,"Good",
A2>=50000,"Average",
TRUE,"Needs Improvement")
Result:
โข โน180,000 โ Excellent
โข โน120,000 โ Good
โข โน70,000 โ Average
โข โน30,000 โ Needs Improvement
The final TRUE acts as a default condition if none of the previous conditions are met.
๐ IFS() vs Nested IF()
Nested IF:
=IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=50,"C","Fail")))
IFS:
=IFS(B2>=90,"A",B2>=75,"B",B2>=50,"C",TRUE,"Fail")
The "IFS()" version is shorter and much easier to understand.
๐ Real-World Uses
โข Assign employee performance ratings
โข Grade students
โข Categorize sales performance
โข Classify customer priority levels
โข Determine commission slabs
๐ Common Mistakes
โข Writing conditions in the wrong order
โข Forgetting to include a default condition TRUE
โข Using IFS() in older Excel versions where it isn't available
โ Best Practices
โข Write conditions from highest priority to lowest
โข Always include TRUE as the last condition to handle unexpected cases
โข Use IFS() instead of deeply nested IF() formulas whenever possible
โข Test your formula with different inputs before using it on large datasets
The "IFS()" function makes complex decision-making formulas much simpler and more readable.
Double Tap โค๏ธ For More