=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") tests the conditions from first to last and returns the value paired with the first one that is TRUE. It does what a nested IF does, without putting one IF inside another. IFS needs Excel 2019 or later, or Microsoft 365.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Ana | 94 | A |
| 3 | Ben | 81 | B |
| 4 | Chloe | 70 | C |
| 5 | Dan | 65 | F |
| 6 | Eve | 88 | B |
| 7 | Finn | 90 | A |
Click C2: four test and result pairs and one closing bracket. Change Chloe's score in B4 to 69 and her grade drops to F.
IFS syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
- The arguments come in pairs: a test that gives TRUE or FALSE, then the value to return when it is TRUE.
- Excel checks the tests in order and returns the value of the first TRUE one. Later tests are not checked, so with
>=tests, put the highest threshold first. - There can be up to 127 pairs.
- There is no separate "else" argument. A final pair with
TRUEas its test does that job.
A test without its value (an odd number of arguments) makes Excel reject the formula with "You've entered too few arguments for this function".
TRUE as the default value, and #N/A when nothing matches
If no test is TRUE, IFS returns #N/A. Column C below has no default and fails on the two low scores; column D ends with TRUE,"F" and catches them:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | No default | TRUE default |
| 2 | Ana | 94 | A | A |
| 3 | Ben | 81 | B | B |
| 4 | Dan | 65 | #N/A | F |
| 5 | Eve | 88 | B | B |
| 6 | Gus | 52 | #N/A | F |
#N/A The value you looked for is not in the lookup range.TRUE is a test that is always true, so it is reached only when every test before it was FALSE, and it must be the last pair: anything after it is never checked. The default can be any value: TRUE,"" for an empty cell, TRUE,"Check" to flag the row. If you want to keep the #N/A out of view without a default, =IFNA(IFS(...),"No grade") works too; the IFERROR page explains IFNA.
IFS with text and with AND or OR
The tests are ordinary logical expressions, so they can compare text and use AND and OR. Here a delivery option depends on the region and the order total:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Region | Total | Delivery |
| 2 | 1001 | North | $120 | Free |
| 3 | 1002 | North | $60 | Standard |
| 4 | 1003 | South | $150 | Express |
| 5 | 1004 | East | $40 | Post |
| 6 | 1005 | north | $300 | Free |
Order 1001 is North and over 100, so it gets Free. Order 1002 is North but under 100, so the first test fails and the second one, B2="North", gives Standard. Text comparisons ignore case, so 1005, written north, also gets Free.
When every test compares the same cell with a fixed value (B2="N", B2="S", B2="E"), SWITCH lists each value once and is shorter: =SWITCH(B2,"N","North","S","South","Other").
Practice: delivery speed
| A | B | C | |
|---|---|---|---|
| 1 | Order | Days | Speed |
| 2 | 1001 | 3 | |
| 3 | 1002 | 1 | |
| 4 | 1003 | 7 | |
| 5 | 1004 | 2 | |
| 6 | 1005 | 5 |
Your turn: In C2, return "Express" when the days in B2 are 1 or fewer, "Standard" when they are 3 or fewer, and "Slow" otherwise. Use IFS. The formula fills down to C6.
IFS vs nested IF
The two formulas below return the same grade for every score:
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F")))
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")
| IFS | Nested IF | |
|---|---|---|
| Brackets | One pair | One pair per IF |
| Default value | A final TRUE pair | The last IF's value_if_false |
| No test matches | #N/A unless you add TRUE | The last IF's value_if_false (FALSE if you left it out) |
| Limit | 127 pairs | 64 nested IFs |
| Excel 2016 and earlier | #NAME? | Works |
Choose IFS when everyone who opens the file has Excel 2019 or later, and nested IF when the file goes to someone with an older Excel. With only two outcomes, a plain IF is simpler than either. For many numeric bands (tax brackets, shipping weights), neither is the best tool: a lookup table with approximate match keeps the thresholds in cells, as shown on the nested IF page.
Frequently Asked Questions
How do I add an else value to IFS?
Make the last test TRUE, which is always true: =IFS(B2>=90,"A",B2>=80,"B",TRUE,"F"). Any value that matched none of the earlier tests gets F.
Why does IFS return #N/A?
None of the tests was TRUE and there is no final TRUE pair. =IFS(B2>=90,"A",B2>=80,"B") returns #N/A for a score of 75. Add TRUE,"F" at the end, or wrap the formula in IFNA.
Which Excel versions have IFS?
Excel 2019, Excel 2021, Excel 2024 and Microsoft 365, plus Excel for the web. In Excel 2016 and earlier a formula with IFS shows #NAME?, so use nested IF there. Google Sheets also has IFS.
Is IFS better than nested IF?
It is easier to read and has only one closing bracket, but it works the same way: the first TRUE test wins. Nested IF is still the choice when the file must open in Excel 2016 or earlier.
