How to Use the Excel IFS Function? – 2 Easy Examples
Note: This tutorial on the IFS function in Excel is suitable for Excel 2016 version and later, including Office 365.
Have you wondered if there is a simpler way to handle multiple nested-IF statements in one step? Meet the Excel IFS function.
Introduced in Excel 2016, it is designed to eliminate complex nested-IF statements. It is much easier to use than the IF function, as it can compare up to 127 conditions in one go, unlike an IF formula, which can compare only one statement at a time.
In this guide, I will walk you through the basics of the Excel IFS function and teach you how to use it effectively in your day to day work.
You’ll learn:
Related:
The Excel SMALL Function – 3 Best Examples
How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide
How to Use Excel COUNTIFS: The Best Guide
Excel IFS Function Syntax
=IFS(logical_test1,value_if_true1, logical_test2, value_if_true2,……logical_test127,value_if_true127)
Please keep in mind that you should enter at least one pair of logical tests and value_if_true inside the IFS formula. The rest of the other logical tests are optional.
How to Use the IFS Function in Excel?
The IFS function can be used as a normal IF function, except that instead of specifying value if false, we list all criteria under the same function. Let us look at some examples to understand better.
Example 1: Assign Grades Using the Excel IFS Function
In the following example, to assign grades to students’ marks without using complicated IF statements, you can use the following IFS formula in the first cell and drag it to the rest of the cells.
=IFS(C3>90,”A+”,C3>80,”A”,C3>70,”B”,C3>60,”C”,C3>50,”D”,C3<50,”F”)
As you can see, this simple formula eliminates the need for complex loops of nested if-statements. For example, if we used a normal IF formula to accomplish the same task, it will look something like this.
=IF(C3>90,”A+”,IF(C3>80,”A”,IF(C3>70,”B”,IF(C3>60,”C”,IF(C3>50,”D”,”F”)))))
Also Read:
How to Add Error Bars in Excel? 7 Best Methods
How to Extract an Excel Substring? – 6 Best Methods
How to Make a Line Graph in Excel? 4 Best Line Graph Examples
Example 2: Assign Default Value to the IFS Function
There is no set default value for the IFS function. That is it will not return any preset value if all the conditions are FALSE.
However, you can manually set a default value by setting the last logical test to TRUE and its corresponding value_if_true to the default value.
IFS(C2=1,”OK”,C2=2,”LOW”,C2=3,”HIGH”,TRUE,”INVALID”)
Here, “INVALID” is the assigned default value if all criteria are not met.
Points to Remember:
- The IFS does not return any default if all conditions are FALSE. It will return an #N/A error instead.
- All logical tests should either return a TRUE or FALSE binary value. A #VALUE error will appear if any other value is returned.
Suggested Reads:
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Superscript in Excel? (9 Best Methods)
How to Split Cells in Excel? 3 Easy Methods
Conclusion
In this short tutorial, we have covered the IFS function’s syntax, its usage and common errors faced by people while using it. We hope you find this useful. If you have any questions about this or any other Excel feature please let us know in the comments section. We are always happy to help.
Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. You can train your entire team in Excel and other business software, for a low one-time monthly fee here.
Visit our free Excel Resources Centre for more high-quality Excel guides.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
