IFS in Excel: what it does and how to use it
IFS takes a series of tests and answers, and returns the answer belonging to the first test that passes. It does the work of several nested IF statements without the nesting.
Syntax
IFS(logical_test1, value1, [logical_test2, value2], ...)
When to use it
Any banding: grades from marks, commission tiers from sales, shipping bands from weight.
What trips people up
Order matters, because the first test that passes wins and the rest are never looked at. Put the narrowest condition first. Finish with TRUE as the last test to catch everything left over, or an unmatched row comes back as #N/A. IFS needs Excel 2019 or later; nested IF is the version that works everywhere.
Watch it built, step by step
Questions
What happens when none of the conditions is true?
IFS returns #N/A. End it with TRUE and a fallback, as in ...,TRUE,"Other", so every row gets an answer.
Does the order of the conditions matter?
Yes. IFS stops at the first test that passes. For bands, test the highest first (>=90, then >=80), or every value will match the lowest band.
Is IFS better than nested IF?
The result is the same; IFS is simply easier to read and to change. It needs Excel 2019 or Microsoft 365, while nested IF works in every version.
Related functions
- IF — IF tests something and gives one answer when the test passes and another when it fails.
