The IFS function
Checks several conditions in order and returns the value for the first one that is TRUE. Use TRUE as the last test for "everything else".
How to use IFS
IFS is a flat list of if-this-then-that pairs. Picture a discount board in a shop: spend 500 or more and get 15% off, spend 200 or more and get 10%, otherwise nothing. IFS reads the board from the top and stops at the first line that fits.
You write the arguments in pairs: a test, then the result for it. =IFS(A2>=90,"High",A2>=60,"Mid",TRUE,"Low") checks 90 first, then 60, and lands on the last pair because TRUE is always true. That last pair is your "everything else" line. Without it, a value that fits no test returns #N/A.
Use IFS for grades, price bands, commission tiers or priority labels, where nested IFs would pile up parentheses. Order matters: put the strictest test first, because the first TRUE wins. For a simple yes or no, plain IF is shorter. When you compare one cell against exact values such as country codes, SWITCH is shorter still, since you name the cell only once. And if the bands live in a table, an approximate-match lookup such as =VLOOKUP(A2,E2:F4,2,TRUE) lets you change the bands without touching the formula.
Syntax
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)- logical_test1
- The first condition.
- value_if_true1
- The result when the first condition is TRUE.
- logical_test2, value_if_true2 (optional)
- More condition and result pairs, up to 127.
Examples
=IFS(1A2>=90,2"High",3A2>=60,4"Mid",5TRUE,6"Low")| A | B | C | |
|---|---|---|---|
| 1 | Score | Mid | |
| 2 | 62 |
In plain words: 1If the score in A2 is 90 or more, 2show High. 3If it is 60 or more, 4show Mid. 5In every other case, 6show Low.
| A | B | C | |
|---|---|---|---|
| 1 | Score | Mid | |
| 2 | 62 |
Common IFS mistakes
IFS returns #N/A
None of the tests was TRUE, and IFS has no default. Add a last pair that always matches:
=IFS(A2>=90,"High",A2>=60,"Mid",TRUE,"Low").Everyone gets the wrong band
The first TRUE wins, so the order decides. In
=IFS(A2>=60,"Mid",A2>=90,"High",TRUE,"Low")a score of 95 already passes the first test, so High is never reached. Put the strictest test first.Excel says I entered too few arguments
Every test needs a result right after it, so the argument count must be even. A test without a result, such as a stray
TRUEat the end, is the usual cause.A test like 60<=A2<90 never matches
Spreadsheets cannot chain comparisons.
60<=A2becomes TRUE or FALSE first, and that is then compared with 90. UseAND(A2>=60,A2<90)as the test, or rely on the order and write onlyA2<90after the stricter tests.
Practice IFS
Read the guide
Questions
Does IFS work in older versions of Excel?
IFS is available in Excel 2019, Excel 2021, Microsoft 365 and Google Sheets. Excel 2016 and earlier do not have it and show #NAME?. In those versions, nest IFs or use a lookup table instead.
How do I use IFS with AND or OR?
Put AND or OR in a test, as in =IFS(AND(B2>=50,C2="Yes"),"Pass",TRUE,"Fail"). Each test can be as long as you like, as long as it ends up TRUE or FALSE.
How many conditions can IFS take?
Up to 127 pairs of a test and a result. Past a handful, a lookup table with VLOOKUP or XLOOKUP is easier to maintain.
How do I use IFS with text values?
Compare the cell with text in double quotes, =IFS(A2="Paid","Done",A2="Sent","Waiting",TRUE,"Open"). If a text never matches, look for extra spaces in the cell and remove them with TRIM.