Excel IFS function: several tests without nesting
IFS checks tests in order and returns the value for the first one that's true. =IFS(B2>=2000,"Gold",B2>=1000,"Silver",TRUE,"Bronze") labels Dana Silver. The last TRUE acts as the catch-all.
The examples use the same sales table as the practice box below.
| A Rep | B Sales | E Region | H Hired | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | East | 3/12/2018 |
| 3 | Luis | 830 | West | 7/1/2021 |
| 4 | Priya | 2,100 | East | 11/20/2016 |
| 5 | Marcus | 475 | North | 2/15/2023 |
| 6 | Jen | 990 | West | 9/3/2019 |
| 7 | Omar | 1,600 | East | 1/27/2020 |
Syntax
=IFS(test1, value1, [test2, value2], ...)IFS vs nested IF
Both of these return the same label:
=IF(B2>=2000,"Gold",IF(B2>=1000,"Silver","Bronze"))
=IFS(B2>=2000,"Gold",B2>=1000,"Silver",TRUE,"Bronze")IFS is easier to read, with no stack of closing parentheses. Nested IF works in every version of Excel.
Results for the team
| Rep | Sale | Label |
|---|---|---|
| Priya | 2,100 | Gold |
| Dana | 1,250 | Silver |
| Marcus | 475 | Bronze |
Always add a default
If no test is true, IFS returns #N/A. Ending with TRUE,"Bronze" makes sure every row gets a value.
Order matters
IFS stops at the first true test. Put the highest bar first. If you test B2>=1000 before B2>=2000, Priya gets Silver.
Which versions have IFS
Excel 2019 and later, Microsoft 365 and Google Sheets. Excel 2016 doesn't. If your test runs on an older version, like many Kenexa Prove It tests, use nested IF.
Try it: Label the sale
In C2, label Dana Gold if her sale (B2) is 2000 or more, Silver if it's 1000 or more, otherwise Bronze.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Is IFS better than nested IF?
It's easier to read and edit. Nested IF is more compatible with older Excel.
Does IFS have an else?
Not directly. Use TRUE as the last test, and its value works as the else.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How the Excel IF function works, with text and number results, AND and OR, nested IFs, the #NAME? mistake, and a live exercise.
How to use IFERROR in Excel to replace #N/A, #DIV/0! and other errors, when not to use it, and a live exercise with VLOOKUP.
How to use COUNTIFS in Excel to count rows that meet several conditions, including number ranges, dates and OR logic, with a live exercise.