IFS Function in Excel: Replace Nested IF Cleanly
· 9 min read
The IFS function checks conditions in order and returns the value paired with the first one that is TRUE. It replaces a chain of nested IFs with one flat list of pairs:
=IFS(test1, value1, test2, value2, ..., TRUE, value_if_nothing_matched)Two things make or break an IFS function Excel formula. The tests run from left to right and the first TRUE wins, so the order matters. And IFS has no built-in "else", so you add one yourself by making the last test simply TRUE. Without it, a row that matches nothing returns #N/A.
IFS and SWITCH exist in Excel 2019, Excel 2021, Microsoft 365 and Excel for the web. Older versions show #NAME?, so if a file has to open in Excel 2016 or earlier, stay with nested IF. If AND, OR and plain IF are new to you, start with IF with AND and OR.
The example table
Eight unpaid invoices for an office supplies company. Column D is the number of days past the due date (zero or less means the invoice is not overdue yet). The finance team wants each invoice in an ageing bucket: Current, 1-30 days, 31-60 days or Over 60.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Amount | Days past due | Nested IF | IFS | IFS, no TRUE case | Action |
| 2 | INV-201 | Harbor Dental | 1,200 | 0 | Current | Current | Current | No action |
| 3 | INV-202 | Pine Street Cafe | 640 | 12 | 1-30 days | 1-30 days | 1-30 days | No action |
| 4 | INV-203 | Lakeside Gym | 5,400 | 45 | 31-60 days | 31-60 days | 31-60 days | Send reminder |
| 5 | INV-204 | Orchard School | 8,100 | 75 | Over 60 | Over 60 | #N/A | Call today |
| 6 | INV-205 | Bay Clinic | 300 | -3 | Current | Current | Current | No action |
| 7 | INV-206 | Maple Law | 2,250 | 30 | 1-30 days | 1-30 days | 1-30 days | No action |
| 8 | INV-207 | Summit Print | 950 | 61 | Over 60 | Over 60 | #N/A | Send reminder |
| 9 | INV-208 | River Vets | 4,300 | 18 | 1-30 days | 1-30 days | 1-30 days | No action |
IFS vs nested IF
Here is the ageing bucket as a nested IF, the way it was written before IFS existed (column E):
=IF(D2<=0, "Current", IF(D2<=30, "1-30 days", IF(D2<=60, "31-60 days", "Over 60")))And the same logic with IFS (column F):
=IFS(D2<=0, "Current", D2<=30, "1-30 days", D2<=60, "31-60 days", TRUE, "Over 60")Both give the same eight answers: Current, 1-30 days, 31-60 days, Over 60, Current, 1-30 days, Over 60, 1-30 days. The difference is only in how they read:
| Nested IF | IFS | |
|---|---|---|
| Shape | Each IF sits inside the "else" of the one before | One flat list of test, value pairs |
| Closing brackets | One per IF, all at the end | One |
| The "else" case | The last argument of the innermost IF | A final TRUE, value pair you add yourself |
| No match | Cannot happen if the last IF has an else | #N/A |
| Works in | Every version | Excel 2019 and later, Microsoft 365, the web |
With three or four outcomes, IFS is easier to read and much easier to edit: adding a "61-90 days" bucket is one extra pair, not one extra IF plus a bracket you have to place correctly. With two outcomes, a plain IF is still the clearest choice.
Why a 30-day invoice lands in "1-30 days"
Row 7 is exactly 30 days late. The test D7<=30 is TRUE, so IFS stops there and returns "1-30 days". If you had written D7<30, it would fall through to "31-60 days". Boundaries are where ageing reports go wrong, so test one value that sits exactly on each boundary.
The TRUE catch-all
IFS returns #N/A when none of its tests are TRUE. Look at column G, which is the same formula without the last pair:
=IFS(D2<=0, "Current", D2<=30, "1-30 days", D2<=60, "31-60 days")Rows 5 and 8 (75 and 61 days) show #N/A, because no test covers them. TRUE as the final test is always true, so it catches every row the earlier tests let through. Think of TRUE, "Over 60" as the "else" of IFS.
Some people wrap IFS in IFERROR instead: =IFERROR(IFS(...), "Over 60"). That works, but it also hides real errors, such as a #VALUE! from text in the days column. The TRUE pair only catches "nothing matched", which is what you want. See IFERROR in Excel for when hiding errors is reasonable.
Order matters: the first TRUE wins
IFS does not look for the best match. It stops at the first test that is TRUE. Here is a version an AI assistant might write, testing the lowest threshold first:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Days past due | AI's formula | Fixed |
| 2 | INV-202 | 12 | 1-30 days | 1-30 days |
| 3 | INV-203 | 45 | 1-30 days | 31-60 days |
| 4 | INV-204 | 75 | 1-30 days | Over 60 |
| 5 | INV-205 | -3 | Current | Current |
A 75-day invoice is more than 0 days late, so the first test is TRUE and it is labelled 1-30 days. The "31-60 days" and "Over 60" pairs can never be reached. There is no error, and the column looks perfectly plausible until someone notices that nothing is ever older than 30 days.
The rule: with "greater than" tests, start with the highest threshold. With "less than or equal" tests, as in the first sheet, start with the lowest. Either works, as long as each test only sees the rows the earlier tests let through. A quick check after writing it: filter or count the result column and make sure every bucket you expect actually appears.
IFS with AND and OR
Each test in IFS can be any TRUE/FALSE expression, including AND and OR. Column H of the first sheet decides what the finance team does next:
=IFS(AND(D2>60, C2>=5000), "Call today", D2>30, "Send reminder", TRUE, "No action")Only INV-204 (75 days, 8,100) gets Call today. INV-203 and INV-207 get Send reminder, and everything else gets No action. INV-207 is over 60 days but under 5,000, so the first test fails and the second one catches it. That fall-through is intended here, and it shows why you read IFS top to bottom like a checklist.
SWITCH: when you are matching exact values
IFS is for conditions. When every test is "is this cell equal to X", SWITCH is shorter: you name the cell once, then list value and result pairs, with an optional default at the end.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Terms | Days to pay |
| 2 | Harbor Dental | COD | 0 |
| 3 | Pine Street Cafe | N30 | 30 |
| 4 | Lakeside Gym | N60 | 60 |
| 5 | Orchard School | N30 | 30 |
| 6 | Bay Clinic | N45 | Check terms |
=SWITCH(B2, "COD", 0, "N30", 30, "N60", 60, "Check terms")The results are 0, 30, 60, 30 and Check terms. N45 is not in the list, so it gets the default. Unlike IFS, SWITCH has a real default argument: an odd number of arguments after the first means the last one is the default. Leave it out and an unknown code returns #N/A.
You will sometimes see =SWITCH(TRUE, D2>60, "Over 60", D2>30, "31-60 days", "Current"). It works, because SWITCH compares TRUE with each test, but it is just IFS written in a less obvious way. Use IFS for conditions and SWITCH for exact codes.
When neither is the right tool
If the list of codes or thresholds is long, or changes every quarter, put it in a small table and look it up instead. A rate table of ten bands is easier to check and update than a formula with twenty arguments. Our two-way lookup guide covers approximate-match bands, and XLOOKUP in Excel covers exact codes.
Common mistakes
No TRUE case. The most common IFS bug. The formula works on the test data, then a new value outside every range arrives and shows #N/A. Always end with TRUE, something.
Thresholds in the wrong order. As in the second sheet, a loose test placed first swallows the rows meant for later pairs. Test the extremes: put a very large and a very small value in and check the label.
Numbers in quotes. D2>"30" compares a number with text. Text counts as larger than any number, so the test is always FALSE. Write D2>30.
Overlapping or missing boundaries. D2<30 followed by D2>30 leaves exactly 30 with no bucket of its own, so it drops to the TRUE case. Write each boundary once, with <= or >=, and check the value on the line.
Using IFS in a file for older versions. IFS opens as #NAME? in Excel 2016 and earlier. If you are not sure what the reader has, nested IF is the safe choice.
FAQ
What is the difference between IF and IFS in Excel?
IF tests one condition and returns one of two values. IFS tests several conditions in order and returns the value of the first one that is TRUE, so it replaces nested IFs.
How do I add an "else" to IFS?
Make the last test TRUE: =IFS(A2>100, "High", A2>50, "Medium", TRUE, "Low"). TRUE is always true, so it catches everything the earlier tests missed.
Why does my IFS formula return #N/A?
None of the tests were TRUE for that row. Add a final TRUE, value pair, or check for a gap between your boundaries.
Should I use IFS or SWITCH?
Use IFS when the tests are conditions such as greater than or less than. Use SWITCH when you are matching one cell against a list of exact values, such as codes.
How many conditions can IFS have?
Up to 127 test and value pairs. In practice, more than five or six is a sign that a lookup table would be clearer.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Rate each delivery with IFS
- Nobody made GoldAudit
- Commission by channel with SWITCH
- Small, medium or large?