IFS Function in Excel: Many Conditions, One Clean Formula
How IFS works in Excel 2019, Microsoft 365 and Google Sheets: grades and tiers, the TRUE catch-all trick, and how it differs from nested IF.
By Ayoub Farid · · 1 min read
IFS checks conditions in order and returns the result for the first one that's true. It does the same job as a nested IF without the pile of brackets.
Syntax
=IFS(test1, result1, test2, result2, ...)Available in Excel 2019, Excel 2021, Microsoft 365, Excel for the web and Google Sheets. Older Excel shows #NAME?.
Example: grades
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")Compare the nested version:
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F")))Same answer, easier to read and edit.
The TRUE catch-all
IFS has no "otherwise" argument. If no test is true, it returns #N/A. Add TRUE as the last test to catch everything else, as in the example above.
Order still matters
IFS returns the first true result, so put the strictest condition first. B2>=70 before B2>=90 would give a 95 a "C".
Example: delivery status
=IFS(D2="","Not shipped",E2="","In transit",E2>F2,"Late",TRUE,"On time")Each test can look at a different cell.
IFS vs SWITCH
If you're comparing one cell against exact values, SWITCH is shorter:
=SWITCH(C2,"N","North","S","South","E","East","W","West","Unknown")IFS is for ranges and mixed tests (>=, <, different cells).
When to use a lookup table instead
If there are many bands, or they change often, put them in a small table and use VLOOKUP with approximate match or XLOOKUP. Formulas stay short and the rules live in cells anyone can edit.
Practise it
Try IFS on a grade calculator. Decisions with IF, AND and OR are the next unit coming to Cellio.
Frequently asked questions
What is the difference between IF and IFS?
IF handles one test with two outcomes. IFS handles several tests in one formula and returns the result of the first true one.
Why does IFS return #N/A?
None of the tests were true. Add TRUE,"default" at the end.
Does Excel 2016 have IFS?
Only with a Microsoft 365 subscription. Standalone Excel 2016 doesn't have it.
Does Google Sheets support IFS?
Yes, with the same syntax.