Excel IFS Function

IFS tests a list of conditions in order and returns the value tied to the first one that comes back TRUE. It replaces the deeply nested IF formulas that used to be the only way to handle more than two or three outcomes. One thing to know before you write your first formula: IFS has no built-in "else" value. If every single condition evaluates to FALSE, the formula returns #N/A, not a blank cell or a zero.

Available in: Excel 365, Excel 2021, Excel 2019. Not available in Excel 2016 or earlier.

Syntax

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Parameter	Required	Description
`logical_test1`	Yes	The first condition to check. It has to evaluate to TRUE or FALSE, not a number, not text, not an error.
`value_if_true1`	Yes	What IFS returns if `logical_test1` comes back TRUE.
`logical_test2, value_if_true2`	No	Additional condition and result pairs, checked left to right. IFS supports up to 127 pairs in a single formula.

⚠

IFS has no built-in default value for when every condition is FALSE. It returns #N/A instead. To avoid this, add TRUE as your final logical_test, followed by whatever fallback value you want returned when nothing else matches.

Basic Example

You run an online store and charge a shipping surcharge based on order weight. Column D holds the weight in pounds for each order.

=IFS(D2<=5, 0, D2<=20, 4.99, D2<=50, 9.99, TRUE, 19.99)

// D2<=5   = orders under 5 lbs ship free
// D2<=20  = orders up to 20 lbs cost $4.99
// D2<=50  = orders up to 50 lbs cost $9.99
// TRUE    = catch-all for anything heavier, charges $19.99

IFS checks the conditions top to bottom. The moment one comes back TRUE, it returns the paired value and stops. A 12-pound order skips the first test, matches the second, returns 4.99, and never touches the third or fourth pair.

How IFS Works

Conditions Are Evaluated in Order, and Order Matters

IFS checks each logical_test from left to right. If you write your weight tiers out of sequence, say D2<=50 before D2<=20, the formula will match the wider range first and never reach the narrower one. Always order your conditions the way you'd walk through them by hand: lowest to highest, or highest to lowest, but never mixed.

IFS Stops at the First TRUE Result

Once a condition returns TRUE, IFS returns the matching value and ignores everything after it. This matters for performance on large sheets: if your first condition covers 80% of your rows, IFS resolves those rows fast because it never evaluates the remaining tests for them.

There's No Built-In Default Value

Unlike a nested IF, where the final value_if_false naturally acts as your catch-all, IFS has no equivalent built in. You have to build your own by adding TRUE as the last logical_test. Skip this and any row that doesn't match one of your explicit conditions returns #N/A.

IFS Spills Across Ranges in Excel 365

Feed IFS a range instead of a single cell reference in the logical_test, and it spills the results down automatically. =IFS(B2:B20>90,"A",B2:B20>80,"B",TRUE,"C") entered in one cell fills 19 rows without copying the formula down. This only works in Excel 365 and 2021, where dynamic arrays are native.

IFS Works with Text and Dates, Not Just Numbers

Most tutorials only show numeric threshold examples. IFS handles text comparisons and date math the same way. =IFS(B2="Rush", "Ship today", B2="Standard", "Ship in 3 days", TRUE, "Confirm with customer") works exactly like a numeric tier test, just with string equality instead of < or <=.

Common Use Cases

Consolidating Vendor Commission Tiers into One Formula

A common request that gets left half-answered elsewhere: paying commission at whichever is lower, a percentage of sales or a flat cap, across several volume tiers. IFS handles this in one formula instead of four separate ones.

=IFS(C2<1000, MIN(C2*0.1, 50), C2<5000, MIN(C2*0.08, 300), C2<20000, MIN(C2*0.06, 900), TRUE, MIN(C2*0.04, 5000))

// C2<1000   = tier 1, pay 10% capped at $50
// C2<5000   = tier 2, pay 8% capped at $300
// C2<20000  = tier 3, pay 6% capped at $900
// TRUE      = top tier, pay 4% capped at $5,000

Each tier's MIN() picks the lower of the percentage or the cap, and IFS routes each vendor's sales total into the right tier automatically.

Pricing a Print Job by Finish and Quantity

Print shops often price by both product type and order size at the same time. Combine AND() inside each logical_test to handle two conditions per tier.

=IFS(B2="Digital", 0, AND(B2="Matte", C2>=500), 0.12, AND(B2="Matte", C2<500), 0.18, AND(B2="Gloss", C2>=500), 0.15, TRUE, 0.22)

// B2="Digital"              = flat $0 finishing cost
// AND(B2="Matte", C2>=500)  = matte, high volume, $0.12/unit
// AND(B2="Matte", C2<500)   = matte, low volume, $0.18/unit
// TRUE                      = catches gloss under 500 units at $0.22/unit

Flagging Overdue Invoices by Days Past Due

Accounts receivable teams often need a status label, not just a number.

=IFS(TODAY()-C2>90, "Write off", TODAY()-C2>60, "Collections", TODAY()-C2>30, "Follow up", TRUE, "Current")

TODAY()-C2 calculates days since the invoice date. IFS routes that number into a status label, and because TODAY() recalculates daily, the labels update on their own without touching the formula.

Handling Errors

IFS throws two catchable errors, and one message that isn't a runtime error at all.

Common causes of #N/A:

None of the logical_test conditions returned TRUE, and there's no TRUE catch-all pair at the end
A typo in a condition (like = instead of <=) means rows that should match never do

Common causes of #VALUE!:

A logical_test evaluates to something other than TRUE or FALSE, like a text string, a blank cell, or an error carried in from another formula
A referenced cell contains an error value, which then propagates into the logical_test

If you build a formula and Excel refuses to accept it with the message "You've entered too few arguments for this function," that's not a runtime error IFERROR can catch. It means one of your logical_test arguments is missing its paired value_if_true. Fix the formula structure before saving it.

=IFNA(IFS(C2<60, "F", C2<70, "D", C2<80, "C", C2<90, "B", C2>=90, "A"), "No grade")

✓

Add TRUE as a final catch-all condition instead of wrapping IFS in IFERROR. A catch-all handles the missing-match case cleanly. IFERROR, by contrast, will also swallow a genuine #VALUE! caused by a typo, and you won't know your formula has a real problem.

Notes & Gotchas

What is the IFS function in Excel and how do you use it?

IFS is a logical function that checks a list of conditions in order and returns the value paired with the first one that's TRUE. You use it by writing condition and result pairs one after another: =IFS(condition1, result1, condition2, result2, ...). Excel checks each condition left to right and stops at the first match.

What is the difference between the IF function and the IFS function in Excel?

IF handles exactly one condition and gives you a TRUE result and a FALSE result. IFS handles as many conditions as you need, up to 127, without nesting one IF inside another. Where IF forces you to write IF(A, B, IF(C, D, IF(E, F, G))) for three outcomes, IFS writes the same logic as one flat list: IFS(A, B, C, D, E, F).

How many conditions (logical tests) can the IFS function handle?

IFS supports up to 127 logical_test and value_if_true pairs in a single formula. In practice, most real formulas use somewhere between three and eight pairs; anything beyond 20 or so is usually a sign the data belongs in a lookup table instead.

What happens if none of the conditions in an IFS function are true?

IFS returns #N/A. This is by design, not a bug, but it surprises people coming from nested IF, where the final else branch always catches everything. Add TRUE as the last logical_test with your fallback value to avoid the error entirely.

Can the IFS function replace nested IF statements in Excel?

Yes, in most cases, and it's usually the better choice. Nested IF formulas are capped at 64 levels and get unreadable past three or four. IFS flattens the same logic into a list that's easier to scan and edit. The one case where nested IF or SWITCH still wins: single-variable exact-match lookups against a long list of specific values, where SWITCH's syntax is shorter.

Why does IFS return #VALUE!?

A logical_test is producing something other than TRUE or FALSE. This usually happens when a condition references a cell containing text where you expected a number, or when an upstream formula in that cell has already errored out. Check each condition individually by selecting it in the formula bar and pressing F9 to see what it actually evaluates to.

Does IFS stop checking once it finds a TRUE condition, or does it evaluate everything?

It stops. Once a logical_test returns TRUE, IFS returns the paired value and doesn't check any remaining conditions. This is worth knowing because it means a later condition referencing bad data, like a division by zero, generally won't surface as an error if an earlier condition already matched for that row.

Does IFS work with dates, or only numeric thresholds?

Yes. Date math works inside a logical_test the same way it works anywhere else in Excel. TODAY()-C2>30 is a perfectly valid condition, and it's one of the more common uses of IFS in accounts receivable and project tracking sheets, even though most tutorials only show numeric grade or discount examples.

Is IFS available in every version of Excel?

No. IFS is available in Excel 365, Excel 2021, and Excel 2019. It does not exist in Excel 2016 or earlier. If you share a workbook with someone on an older version, IFS formulas will show as #NAME? errors on their end.

Related Functions

Function	Use this when...
`IF`	You only have one condition to test and don't need multiple tiers.
`IFERROR`	You need to catch any error type from a formula, not just `#N/A`.
`VLOOKUP`	Your tier or category data lives in a separate lookup table instead of being hardcoded into the formula.