IFS function
IFS checks conditions in order and returns the result paired with the first one that is true. It replaces a chain of nested IFs, and returns #N/A if nothing is true.
Example
Read it as a sentence: check whether the Qty in D2:D7 is more than 10 and show big if so, and if not show small, which starts with big.
Excel shows big
D
1
Qty
2
12
3
7
4
20
5
5
6
15
7
9
Items
Syntax
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
Arguments in [square brackets] are optional.
-
logical_test1(required) - First condition.
-
value_if_true1(required) - Result if the first condition is TRUE.
-
logical_test2, value_if_true2(optional) - More condition and result pairs, up to 127.
Returns: The result paired with the first TRUE condition.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=IFS(FALSE,1,TRUE,2) | 2 | Second test |
=IFS(5>3,"a") | a | One pair |
=IFS(1,"one") | one | Number test |
=IFS(TRUE,"a",1/0,"b") | a | Error in later test |
=IFS(Items!D2:D7>10,"big",TRUE,"small") | big | Array tests spill |
=IFS(Places!E3<1,"Walk",Places!E3<5,"Cab",TRUE,"Train") | Cab | Site: featured, Scotland Yard 2.8 miles |
=IFS(FALSE,1) | #N/A | No test true |
=IFS("a",1) | #VALUE! | Text test |
The Items sheet these formulas read
D
1
Qty
2
12
3
7
4
20
5
5
6
15
7
9
Items
The Places sheet these formulas read
E
1
Miles
2
0
3
2.8
Places
Watch out for
- If nothing is TRUE you get #N/A; end with TRUE, "default" as a catch-all.
- Conditions are checked in order, so put the most specific first.
- Arguments must come in pairs or you get an error.
Where it works
- Excel
- IFS arrived in Excel 2019. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .