The IFS function checks a list of conditions in order and returns the result for the first one that is true. Type =IFS(D2<=0,"Current",D2<=30,"1 to 30 days",TRUE,"Older") and an invoice that is 12 days overdue shows 1 to 30 days. The closing TRUE pair works as the default, so nothing falls through to an error.
Functions
IFS Excel Formula: Default Value, Order and Examples
Try this now
The IFS function evaluates the criteria in order and returns the first true option. Try typing
=IFS(B2>=5, 1000, B2>=3, 500, B2<3, 0) to calculate John's bonus.| A | B | C | D | |
|---|---|---|---|---|
| 1 | EMPLOYEE | DEALS | ||
| 2 | John | 1 | ||
| 3 | Sarah | 4 | ||
| 4 | Max | 5 | ||
| 5 | Michael | 3 |
You are typing into D1. Refer to the cells that hold the data.
No key for < or > on your keyboard? Use these:
Ready to practice?
Apply what you just learned with interactive exercises.
Below you get the syntax, the order rule, a default value, ranges, AND and OR, blanks, errors, and a clear line between IFS, nested IF and SWITCH.
TL;DR
- The pattern is =IFS(test1, result1, test2, result2, ...). Excel returns the result next to the first test that is TRUE and stops there.
- IFS has no else slot. If nothing is TRUE you get #N/A. End the list with TRUE, "your default" to catch everything else.
- Put the narrowest test first. A broad test placed early swallows the rows meant for later tests.
- For "between two values", wrap two comparisons in AND inside one test.
- A blank cell counts as 0 in a number comparison, so check for ="" first if blanks are possible.
- IFS needs Excel 2019, Excel 2021, Excel 2024 or Microsoft 365. Older versions of Excel show #NAME?.
Table of Contents
- Sample data for the IFS examples
- IFS syntax and arguments
- How to use the IFS function in Excel
- IFS else: add a default value with TRUE
- Why the order of IFS tests matters
- IFS formula between two values
- IFS with multiple criteria: AND and OR
- IFS with text and calculated results
- IFS and blank cells
- IFS compared with nested IF and SWITCH
- Which Excel versions have IFS
- IFS errors and how to fix them
- What learners get wrong
- A worked job: the overdue invoice report
- Practice the IFS function
- FAQ
- Sources
Sample data for the IFS examples
The examples use an accounts receivable list from a small studio. Row 1 has the headings and the invoices sit in rows 2 to 9. A negative number in column D means the invoice is not due yet. Row 9 has no date logged, so column D is empty. Column G holds the bucket that the worked job builds near the end.
| Row | A: Invoice | B: Client | C: Amount | D: Days overdue | E: Type | F: Contact | G: Bucket |
| 1 | Invoice | Client | Amount | Days overdue | Type | Contact | Bucket |
| 2 | INV-301 | Delta Freight | 4200 | -5 | Retainer | Naledi | Current |
| 3 | INV-302 | Sable Studio | 1850 | 12 | Project | Pieter | 1 to 30 days |
| 4 | INV-303 | Orchard Dental | 960 | 34 | Retainer | Sandra | 31 to 60 days |
| 5 | INV-304 | Pike Plumbing | 7300 | 61 | Project | Themba | 61 to 90 days |
| 6 | INV-305 | Quartz Legal | 2100 | 95 | Project | Anil | Over 90 days |
| 7 | INV-306 | Reed Print | 480 | 30 | Retainer | Joyce | 1 to 30 days |
| 8 | INV-307 | Summit Gym | 1250 | 60 | Retainer | Marcus | 31 to 60 days |
| 9 | INV-308 | Umber Cafe | 640 | (empty) | Project | Lerato | Check date |
IFS syntax and arguments
IFS takes pairs. Each pair is a test and the result to show if that test is TRUE.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
| Argument | Required | What it does |
| logical_test1 | Yes | A comparison or function that returns TRUE or FALSE, such as D2<=0. |
| value_if_true1 | Yes | What to return when the first test is TRUE. Text goes in double quotes. |
| logical_test2, value_if_true2 | No | More pairs. Microsoft allows up to 127 pairs in one formula. |
Every test needs a result partner. If you type an odd number of arguments, Excel refuses the formula and tells you that you entered too few arguments.
How to use the IFS function in Excel
Take the ageing buckets finance teams use. A negative or zero day count is current, up to 30 days is the first bucket, and so on. With nested IF you would stack five functions and five closing brackets. With IFS you write a flat list:
=IFS(D2<=0,"Current",D2<=30,"1 to 30 days",D2<=60,"31 to 60 days",D2<=90,"61 to 90 days",TRUE,"Over 90 days")
Row 2 has -5, so the first test is TRUE and the cell shows Current. Row 3 has 12. The first test is FALSE, the second is TRUE, so you get 1 to 30 days. Row 5 has 61, which fails the first three tests and passes D2<=90, so it shows 61 to 90 days. Row 6 has 95 and reaches the last pair.
Rows 7 and 8 sit exactly on the boundaries. Row 7 has 30 days and shows 1 to 30 days, because <=30 includes 30. Row 8 has 60 days and shows 31 to 60 days. Decide the boundary rule before you type the symbol: "up to and including 30" is <=30.
This is the IFS formula in Excel at its simplest. Each bucket is one pair, you can read the rules straight down, and adding a bucket means adding a pair. If you are weaker on the symbols, our guide to logical operators in Excel covers <=, >=, <> and the rest.
IFS else: add a default value with TRUE
IFS has no else argument, which is the part people miss. When no test is TRUE, Excel gives up and returns #N/A.
Cut the last pair from the formula above and you can see it:
=IFS(D2<=0,"Current",D2<=30,"1 to 30 days")
Row 2 shows Current and row 3 shows 1 to 30 days, but row 5 has 61 days, passes neither test, and shows #N/A.
The fix is a test that is always TRUE. The word TRUE itself works, because IFS only needs something that evaluates to TRUE:
=IFS(D2<=0,"Current",D2<=30,"1 to 30 days",TRUE,"Older")
Row 5 now shows Older. The final TRUE,"Older" pair is the IFS else. It must be last. Put it earlier and it catches everything after it, and the later pairs never run.
You could hide the missing default with IFERROR instead:
=IFERROR(IFS(D2<=0,"Current",D2<=30,"1 to 30 days"),"Older")
Row 5 shows Older again, but IFERROR also swallows real problems, such as a mistyped cell reference or a text value in the days column. Use the TRUE pair when you want a default and keep IFERROR for errors you cannot predict.
Why the order of IFS tests matters
Because the first TRUE wins, test order is a rule of logic, not style. Here is the same bucket list with the tests flipped, a classic IFS mistake:
=IFS(D2<=90,"Late",D2<=30,"Recent",TRUE,"Over 90 days")
Row 3 has 12 days. The first test, D2<=90, is TRUE, so the cell shows Late. The <=30 test is never reached. Row 2, with -5 days, also shows Late, which is wrong for an invoice that is not due.
The rule is simple. With <= or < tests, list the smallest threshold first and climb. With >= or > tests, list the largest first and descend. Check the result by testing a value that sits just inside each boundary, not just a value in the middle of each bucket.
Another useful habit: after you write the formula, read the first test aloud and ask "does anything I do not want caught here also pass?" If yes, tighten that test or move it later.
IFS formula between two values
IFS has no "between" keyword. If you need an IFS formula between two values, put both limits into one test with AND:
=IFS(AND(D2>=1,D2<=30),"1 to 30 days",AND(D2>=31,D2<=60),"31 to 60 days",TRUE,"Other")
Row 3 (12 days) is between 1 and 30, so it shows 1 to 30 days. Row 7 (30 days) also does. Row 4 (34 days) shows 31 to 60 days. Row 2 (-5) and row 5 (61) fall through to Other.
This looks longer than the flat version, but it makes the ranges independent, so the order stops mattering. That is useful when the ranges are not neat steps of one scale, for instance when you only want to label two particular windows and ignore the rest.
Write whole numbers carefully here. A decimal such as 30.5 falls between <=30 and >=31, so it lands in Other. If your days can be fractional, build the ranges with < on the upper edge instead.
IFS with multiple criteria: AND and OR
Each test in IFS can hold several conditions, by using AND and OR. Suppose you only escalate invoices that are both far overdue and large:
=IFS(AND(D2>60,C2>=2000),"Escalate",D2>60,"Chase",TRUE,"Wait")
Row 5 (61 days, 7,300) satisfies both parts of the first test and shows Escalate. Row 6 (95 days, 2,100) shows Escalate. Neither row 7 (30 days) nor row 8 (60 days, which is not more than 60) gets past the second test, so both show Wait. The more specific test, the AND, goes first. If you put D2>60 first, every late invoice would show Chase and the Escalate line would never run.
OR works the same way. To chase any project invoice, and anything over 60 days, and wait on the rest:
=IFS(OR(E2="Project",D2>60),"Chase",TRUE,"Wait")
Row 3 is a Project, so it shows Chase. Row 4 is a Retainer at 34 days, so it shows Wait. Row 8 is a Retainer at exactly 60 days, which is not over 60, so it also shows Wait. Our guide to logical functions covers AND, OR, XOR and NOT in more depth if the helpers are new to you.
IFS with text and calculated results
The result in each pair can be a number or a formula, and the tests can mix text and numbers. A late fee that depends on the invoice type is a good example:
=IFS(E2="Retainer",C2*0.02,E2="Project",C2*0.05,TRUE,0)
Row 2 is a Retainer for 4,200, so the fee is 84. Row 3 is a Project for 1,850, so the fee is 92.5. Row 5 is a Project for 7,300, so the fee is 365. Notice that the results have no quotes, so they stay as numbers that you can sum later.
The text tests ignore capitals, so "retainer" would match too. IFS compares the whole cell, which means "Retainer " with a trailing space would fall to the default 0. If the data was typed by hand, add TRIM around the cell: TRIM(E2)="Retainer".
When every test compares the same cell to an exact value, there is a shorter tool. We get to SWITCH in the comparison below.
IFS and blank cells
Row 9 has no day count. That matters, because Excel treats an empty cell as 0 in a number comparison. Run the first formula on row 9:
=IFS(D9<=0,"Current",D9<=30,"1 to 30 days",TRUE,"Over 90 days")
The empty D9 counts as 0, D9<=0 is TRUE, and the cell shows Current. A missing date silently becomes a clean bill of health, which is the worst result on a collections sheet.
Put a blank check first:
=IFS(D9="","Check date",D9<=0,"Current",D9<=30,"1 to 30 days",TRUE,"Older")
Now row 9 shows Check date, and the other tests only run when there is a number to read. On row 2 the same formula still returns Current, and on row 5 it returns Older. The rule to remember: any IFS that tests numbers should decide what a blank means before it tests anything else.
A related trap is numbers stored as text. If the days column was imported and the numbers are left-aligned with a green triangle, Excel ranks text above every number. A text "12" fails <=30, falls through to the default, and the invoice shows as the oldest bucket. Convert the column to real numbers first.
IFS compared with nested IF and SWITCH
All three do the same job: pick one of several results. They differ in how much you have to read.
| Approach | Reads like | Best for | Watch out for |
| IFS | A flat list of tests | Ranges and ordered thresholds, many outcomes | No built-in else. #N/A unless you end with TRUE. Needs a modern Excel. |
| Nested IF | A tree that closes with many brackets | Two or three outcomes, files shared with older Excel | Hard to read past three levels, and a long run of closing brackets is easy to miscount |
| SWITCH | One cell matched against exact options | Codes, labels, exact values | Exact matches only. It cannot test greater-than |
| Lookup table | A table you maintain on the sheet | Many tiers that change often | Needs a sorted table and an approximate-match lookup |
=IF(D2<=0,"Current",IF(D2<=30,"1 to 30 days",IF(D2<=60,"31 to 60 days",IF(D2<=90,"61 to 90 days","Over 90 days"))))
It returns the same bucket as the IFS version for every row, but the last pair is hidden at the end of a pile of brackets, and there is no way to see the structure at a glance. See the full IF function guide for how nesting works.
SWITCH is the better pick when you compare one cell with exact values. Here is the same idea written for the type column:
=SWITCH(E2,"Retainer","Monthly","Project","Milestone","Other")
Row 2 returns Monthly, row 3 returns Milestone. The final "Other" has no partner, which makes it the default. With IFS you would write E2="Retainer" and E2="Project" separately and add a TRUE line. Our SWITCH guide shows the edge cases.
If the thresholds change every quarter, a lookup table is better than either. Put the bucket limits in a range and use an approximate-match VLOOKUP or XLOOKUP. You edit the table and never touch the formula.
Which Excel versions have IFS
IFS arrived with Excel 2019. It is available in Excel 2019, Excel 2021, Excel 2024 and Microsoft 365, in Excel for the web, and in the Mac editions of those releases. It is not in Excel 2016 unless you have the Microsoft 365 subscription, and it is not in Excel 2013 or earlier.
The practical risk is sharing. If you send a workbook with IFS to someone on Excel 2016 or 2013, the cell shows #NAME? on their screen even though it works on yours. When the audience is unknown, use nested IF or a lookup table instead.
In current Microsoft 365, IFS also accepts a range as a test and spills the answers down the column. =IFS(D2:D9<=0,"Current",TRUE,"Late") returns eight results from one cell. Older Excel needs the formula filled down instead.
IFS errors and how to fix them
| What you see | Cause | Fix |
| #N/A | No test was TRUE and there is no default, as in =IFS(D5<=0,"Current",D5<=30,"1 to 30 days") | End the list with TRUE, "default" |
| #NAME? | The function does not exist in this Excel version, or the name is mistyped | Check the version. Use nested IF for older Excel |
| #VALUE! | A test returned something that is not TRUE or FALSE, such as a text cell | Make sure every test is a comparison, not a bare cell reference |
| "Too few arguments" message | A test with no result, or a result with no test | Count the pairs. Every test needs one result |
| Wrong bucket, no error | Tests in the wrong order, text numbers, or blank cells counted as 0 | Reorder, convert the column, add a ="" test first |
=IFS(E2,"Matched")
E2 holds the word Retainer, which is neither TRUE nor FALSE, so the result is #VALUE!. The test needs a comparison such as E2="Retainer".
What learners get wrong
Between 26 September and 3 November 2026, learners made 1,380 first attempts across our IFS Function lesson, and 81.7% were right. These are first checks of a screen in a page view, not people. Its hardest screens point to a few habits.
The Phoenix packing screen asks for tank top at 90 or above, T-shirt at 70 or above, and jacket below 70. Of 139 first attempts, 64.0% were right. The mistakes were small in number, but each one is a trap you can hit at work:
- The wrong column. Two first attempts pointed at D2 instead of B2. On a sheet where D2 is empty, =IFS(D2>=90,"Tank Top",D2>=70,"T Shirt",D2<70,"Jacket") returns Jacket, because the empty cell counts as 0. There is no error to warn you, which is why this one is dangerous. With 105 in B2, the right formula =IFS(B2>=90,"Tank Top",B2>=70,"T Shirt",B2<70,"Jacket") returns Tank Top.
- An unclosed quote. Two answers ended in "Jacket) with the closing quote missing. Excel will not accept the formula, and the quote has to close before the bracket.
- No equals sign. Two answers began ifs( with no =, so Excel stored the text instead of calculating it.
- The wrong row. One answer typed B1 instead of B2. That reads the heading cell. Excel ranks text above every number, so a text heading makes B1>=90 TRUE and the formula returns Tank Top whatever the temperature is.
On the two "what would this return" screens, 145 first attempts each, 74.5% and 78.6% were right. Some learners typed the formula back instead of its result. When a screen asks what a formula returns, give the value it produces, not the formula. For B2 the answer is 0, and for B3 it is 500.
A worked job: the overdue invoice report
You are asked for an ageing report on the invoice list above. Build it in four steps.
- Bucket each invoice. In G2 put the blank-aware version so missing dates do not pass as current:
Fill down to row 9. You get Current, 1 to 30 days, 31 to 60 days, 61 to 90 days, Over 90 days, 1 to 30 days, 31 to 60 days and Check date.
- Count the problems. Count how many rows need attention before anyone reads the numbers:
The answer is 1, which sends you to row 9 to fix the date.
- Total the exposure. Add the amount that sits in the worst bucket, then the amount that is more than 60 days late:
=SUMIF(D2:D9,">60",C2:C9)
The first returns 2,100, the second returns 9,400, because 7,300 and 2,100 are both over 60 days.
- Assign an action. In H2 give each row an instruction that the finance team can sort:
Row 2 shows No action, row 6 shows Call today, row 9 shows Fix the date, and every other row shows Send reminder.
The report is four formulas and one rule per bucket. When the policy changes, you edit one pair, not a nest.
Practice the IFS function
The fastest way to lock in the order rule is to type the formula yourself and see it accepted or refused. Open Practice Lesson 64 and work through the pairs. The page already has the exercise, so type the formula rather than read on. If the symbols still slow you down, review True & False first, then come back. For the more common one-test case, read the IF function guide.
FAQ
How does the IFS function work in Excel?
IFS takes pairs of a test and a result. Excel checks the tests from left to right and returns the result of the first test that is TRUE. It ignores every pair after that. If no test is TRUE, it returns #N/A. Add TRUE, "default" as the last pair to cover everything else.
How do I add a default value to IFS?
IFS has no else argument, so end the list with a test that is always TRUE: TRUE,"your default". Excel reaches that pair only when every earlier test is FALSE. Keep it last. If you place it earlier, it will catch every row and the pairs after it will never run.
Can IFS test more than one condition at a time?
Yes. Put AND or OR inside a test. AND(D2>60,C2>=2000) is TRUE only when both parts are true, and OR(E2="Project",D2>60) is TRUE when either is. List the most specific test first, because Excel stops at the first TRUE.
How do I use IFS to check if a number is between two values?
Use AND inside the test with a lower and an upper limit, for example AND(D2>=1,D2<=30). Alternatively, list ordered <= tests from smallest to largest, so each test already knows the earlier ones failed. The AND version makes the ranges independent of order.
Why does my IFS formula return #N/A?
None of your tests was TRUE, and IFS has no default. Check the input first: a blank or text cell can fail every test. Then add TRUE, "default" as the last pair. If the formula should have matched, check that the numbers are not stored as text and that the tests are in the right order.
Is IFS better than nested IF?
For three or more outcomes, usually yes, because the tests read as a flat list with a single closing bracket. Nested IF is better when the file must open in Excel 2016 or older, or when you only have two or three outcomes. IFS needs Excel 2019 or Microsoft 365.
Does IFS work in older versions of Excel?
No. IFS is not in Excel 2016 (without a subscription), Excel 2013 or earlier. A workbook that contains it shows #NAME? on those versions. It works in Excel 2019, 2021, 2024, Microsoft 365, Excel for the web and the Mac editions of those releases.
Sources
Keep going
Guides people read next.
Excel IF Then Formula: Else, Nested, AND, OR Examples
Learn the Excel IF then else formula with worked examples: text, blanks, AND, OR, nested IF, SUM and COUNTIF, plus fixes for common errors.
22 minSWITCH Function in Excel: Syntax, Default, Examples
Learn the Excel SWITCH function: match one value against a list, set a default, use SWITCH(TRUE) for ranges, and choose between SWITCH, IFS and CHOOSE.
18 min
SQL Data Types: Categories, Examples and How to Choose
SQL data types explained: numeric, text, date and time, Boolean and binary types, with examples, MySQL, SQL Server, Oracle and PostgreSQL differences, and how to choose.
15 min