Skip to content

All-Access: every lesson, no ads. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month, billed yearly. Free to start. Cancel any time

See plans
All-Access, no ads: every lesson, course and template. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month. + GST at checkoutSee the plans →

Functions

IFS Excel Formula: Default Value, Order and Examples

By the LogicExcel Editorial Team19 min read3,734 wordsPractice this

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.

Try this now

The IFS function allows you to test multiple criteria and return a value corresponding with the FIRST true condition. Let's say employees get a $1,000 bonus if they close 5 or more deals, $500 if they close 3 or more deals, and $0 if they close fewer than 3 deals.
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.
ABCD
1EMPLOYEEDEALS
2John1
3Sarah4
4Max5
5Michael3
D1fx

You are typing into D1. Refer to the cells that hold the data.

No key for < or > on your keyboard? Use these:

Continue the full lesson →

Ready to practice?

Apply what you just learned with interactive exercises.

Start Lesson 64

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

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.

RowA: InvoiceB: ClientC: AmountD: Days overdueE: TypeF: ContactG: Bucket
1InvoiceClientAmountDays overdueTypeContactBucket
2INV-301Delta Freight4200-5RetainerNalediCurrent
3INV-302Sable Studio185012ProjectPieter1 to 30 days
4INV-303Orchard Dental96034RetainerSandra31 to 60 days
5INV-304Pike Plumbing730061ProjectThemba61 to 90 days
6INV-305Quartz Legal210095ProjectAnilOver 90 days
7INV-306Reed Print48030RetainerJoyce1 to 30 days
8INV-307Summit Gym125060RetainerMarcus31 to 60 days
9INV-308Umber Cafe640(empty)ProjectLeratoCheck date
Type each formula in an empty cell next to row 2 and fill it down to row 9.

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], ...)

ArgumentRequiredWhat it does
logical_test1YesA comparison or function that returns TRUE or FALSE, such as D2<=0.
value_if_true1YesWhat to return when the first test is TRUE. Text goes in double quotes.
logical_test2, value_if_true2NoMore pairs. Microsoft allows up to 127 pairs in one formula.
Excel reads the pairs left to right, stops at the first TRUE and returns its result. It never looks at the later pairs. That behavior is the whole function, and every trap on this page comes from forgetting it.

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.

ApproachReads likeBest forWatch out for
IFSA flat list of testsRanges and ordered thresholds, many outcomesNo built-in else. #N/A unless you end with TRUE. Needs a modern Excel.
Nested IFA tree that closes with many bracketsTwo or three outcomes, files shared with older ExcelHard to read past three levels, and a long run of closing brackets is easy to miscount
SWITCHOne cell matched against exact optionsCodes, labels, exact valuesExact matches only. It cannot test greater-than
Lookup tableA table you maintain on the sheetMany tiers that change oftenNeeds a sorted table and an approximate-match lookup
The nested equivalent of the bucket formula shows the difference:

=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 seeCauseFix
#N/ANo 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 mistypedCheck the version. Use nested IF for older Excel
#VALUE!A test returned something that is not TRUE or FALSE, such as a text cellMake sure every test is a comparison, not a bare cell reference
"Too few arguments" messageA test with no result, or a result with no testCount the pairs. Every test needs one result
Wrong bucket, no errorTests in the wrong order, text numbers, or blank cells counted as 0Reorder, convert the column, add a ="" test first
A bare cell as a test is the easiest #VALUE! to hit:

=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:

  1. 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.
  2. 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.
  3. No equals sign. Two answers began ifs( with no =, so Excel stored the text instead of calculating it.
  4. 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.
The "first true condition" screen is the one that teaches IFS. Of 158 first attempts, 66.5% were right. It asks for 1,000 at 5 or more, 500 at 3 or more, and 0 below 3. The correct formula is =IFS(B2>=5,1000,B2>=3,500,B2<3,0). One wrong answer, typed four times, used B2>3 for the last test. That looks harmless, but suppose B2 holds 2: neither B2>=5, B2>=3 nor B2>3 is TRUE, so the answer is #N/A. The last test must cover everything the earlier ones left behind. Another wrong form, typed three times, wrote b<3 for the last test. A bare column letter with no row number is not a cell reference. Excel reads b as a name it has never heard of, and the formula returns #NAME? whenever a value reaches that test.

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.

  1. Bucket each invoice. In G2 put the blank-aware version so missing dates do not pass as current:
=IFS(D2="","Check date",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")

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.

  1. Count the problems. Count how many rows need attention before anyone reads the numbers:
=COUNTIF(G2:G9,"Check date")

The answer is 1, which sends you to row 9 to fix the date.

  1. Total the exposure. Add the amount that sits in the worst bucket, then the amount that is more than 60 days late:
=SUMIF(G2:G9,"Over 90 days",C2:C9)

=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.

  1. Assign an action. In H2 give each row an instruction that the finance team can sort:
=IFS(G2="Current","No action",G2="Check date","Fix the date",G2="Over 90 days","Call today",TRUE,"Send reminder")

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.