IFS
Evaluates multiple conditions and returns the value for the first TRUE condition.
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)Arguments
logical_test1First conditionvalue_if_true1Result if first condition is TRUElogical_test2Second condition(optional)value_if_true2Result if second is TRUE(optional)
=IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",TRUE,"F")Multiple grade thresholds
=IFS(B1="Gold",100,B1="Silver",50,B1="Bronze",25)Award points by medal
=IFS(A1>0,"Positive",A1<0,"Negative",TRUE,"Zero")Categorize numbers
- •Use TRUE as last condition for default value
- •Replaces nested IF statements
- •Available in Excel 2019 and later
- •Skipping the final TRUE,"default" pair and getting hit with #N/A the moment nothing else matches
- •Ordering conditions the wrong way round - test A1>=70 before A1>=90 and that top grade will never trigger
- •Building a grading formula around IFS for a file that also needs to open in Excel 2016, where the function just isn't there
IFThe building block IFS is meant to replace once you're past two or three nested conditionsSWITCHReads more cleanly when you're matching one value against a fixed list rather than testing rangesVLOOKUPAn approximate-match VLOOKUP against a threshold table can quietly replace a long IFS chain for grading-style logicWhat happens if none of the IFS conditions are TRUE?
You get #N/A, plain and simple. Add a final TRUE,"default value" pair so there's always a fallback result instead of an error.
Does condition order actually matter in IFS?
Yes, and it's an easy trap - IFS checks conditions left to right and stops at the first TRUE one. For grading-style thresholds, list the highest value first, or a lower threshold will catch everything before the higher one gets a chance.
Can I use IFS in any version of Excel?
No - IFS needs Excel 2019, Excel 2021, or Microsoft 365. Anything older, and you're back to nested IF statements.
Need to translate a formula using IFS?
Use our translator to convert your complete formula