Excel function guide · Logic
IFS function in Excel
IFS checks a list of conditions in order and returns the value paired with the first condition that is TRUE. It replaces nested IFs for tiers such as grade bands, and returns #N/A if no condition is met.
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)
IFS is in Excel for Microsoft 365, Excel 2019 and later, and Excel for the web. In older versions, nest IF functions instead.
| Argument | Required? | What it means |
|---|---|---|
| logical_test1 | Required | The first condition to check. |
| value_if_true1 | Required | What to return if it is TRUE. |
| logical_test2, value_if_true2, … | Optional | More pairs, checked in order. Use TRUE as the last test for a catch-all result. |
IFS examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Amira | 92 |
| 3 | Ben | 78 |
| 4 | Chen | 64 |
| 5 | Dana | 45 |
| 6 | Elif | 81 |
1. Grade bands
=IFS(B2>=80, "A", B2>=65, "B", B2>=50, "C", TRUE, "Fail")
Result: A
92 passes the first test, so IFS stops there and returns A.
2. A lower band
=IFS(B4>=80, "A", B4>=65, "B", B4>=50, "C", TRUE, "Fail")
Result: C
64 fails the first two tests and passes B4>=50.
3. No catch-all
=IFS(B5>=80, "A", B5>=65, "B", B5>=50, "C")
Result: #N/A
Dana's 45 meets none of the conditions. A final TRUE, "Fail" pair would catch it.
Try it now
Practise IFS in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 2
In C2, use IFS to grade the score: 80+ is A, 65+ is B, 50+ is C, anything else Fail.
Common IFS mistakes
Tests in the wrong order
IFS returns the first TRUE result. Testing B2>=50 first gives every passing score a C. Order thresholds from highest to lowest (or lowest to highest with <).
No default result
When no test is TRUE, IFS returns #N/A. End with TRUE and the value to use otherwise.
Boundaries
Decide whether 80 itself is an A (>=80) or a B (>80) and be consistent across bands.
Long lists of tiers
If the bands change often, put them in a small table and use XLOOKUP with match_mode -1. Editing a table is safer than editing a formula.
Practise this in Excel Quest
Tiers with nested IF and IFS Members
Course 4 · Logic & Error Handling · 12 min
Grade support agents into bands, assign bonuses by grade, and fix a nested IF that tests in the wrong order.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.