Function guides ยท Logical
IFS function
Checks conditions in order and returns the value for the first one that is true.
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
| Argument | What it is |
|---|---|
logical_test1 | The first condition to check. |
value_if_true1 | What to return if it is TRUE. |
... | More test and value pairs, up to 127, checked in order. |
What IFS does
IFS checks a list of conditions in order and returns the result that belongs to the first one that is true. It is nested IF without the nesting.
Where =IF(a,x,IF(b,y,IF(c,z,w))) needs three closing parentheses and careful counting, IFS reads as a list of pairs. Each pair is a test followed by the value to return when the test holds.
The one thing it lacks is a built-in else. That is solved by ending with TRUE.
IFS examples
1. Letter grades without nesting
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | |
| 2 | Ana | 83 | B |
Each test sits beside its result. Excel works left to right and stops at the first TRUE. A score of 83 fails the first two tests and passes the third, so it earns a B.
The tests go from strictest to loosest. Reversed, everyone would get the first grade they qualify for.
2. Shipping tiers by weight
| A | B | C | |
|---|---|---|---|
| 1 | Order | Weight (lb) | |
| 2 | W-204 | 12 | 15 |
The last pair is TRUE with a default. TRUE is always true, so anything that did not match above falls through to it. This is the catch-all that prevents #N/A.
3. What happens with no catch-all
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | |
| 2 | Dev | 55 | #N/A |
Here the score is 55 and nothing matches. IFS has no fallback argument, so it returns #N/A. If you see that error, the usual cure is to add TRUE and a default as the final pair.
Common IFS mistakes
- No final catch-all. When no test is true, IFS returns #N/A. End with
TRUE, "default"unless an error is what you want. - Tests in the wrong order. The first TRUE wins. Put
B2>=70beforeB2>=90and nobody ever reaches the A. Order from most to least restrictive. - An odd number of arguments. IFS takes pairs. Leaving one half of a pair out gives the message that you have entered too few arguments.
- Using IFS in an old Excel. IFS arrived in Excel 2019 and Microsoft 365. Workbooks opened in Excel 2016 show #NAME?. Nested IF is the fallback.
IFS on Excel job tests
IFS earns its place on band questions: letter grades, commission tiers, shipping rates, age groups. The rule is given as a list, and you write one test-and-result pair per band.
Put the bands in order, strictest first, and finish with TRUE and the lowest band so no row can fall through to #N/A. If the sheet already has a band table, an approximate-match lookup may be shorter and easier to maintain than IFS.
Check the Excel version before you rely on it. IFS is missing from Excel 2016, where the cell would show #NAME?. If the instructions mention 2016, nest IF instead.
Related functions
IFS questions
- What is the difference between IF and IFS?
- IF takes one condition and nesting is needed for more. IFS takes any number of condition and result pairs in a flat list, which is easier to read and edit.
- How do I add a default value to IFS?
- Make the last test TRUE and give it the default result, for example
TRUE,"Other". - Why does IFS return
- None of the tests was TRUE and there was no catch-all pair. Add one.
- Is IFS available in all versions of Excel?
- It is in Excel 2019, Excel 2021 and Microsoft 365, but not in Excel 2016 or earlier.
Practice IFS
Try it in a working grid: the free lesson IF: choose between two results (module 7 of Everyday Excel) has checked IFS exercises.
Practice this function