IFS

How IFS works

IFS reads its arguments in pairs: a condition followed by a value. It checks the first condition, and if true, returns the first value. If false, it moves to the second pair, then the third, and so on. The first match wins, the rest are ignored.

Order matters. Test the most specific or highest-value conditions first. If you list a broad condition like B2 >= 0 before a narrow one like B2 >= 90, the broad one matches first and the narrow one never gets evaluated.

There's no else branch. If no condition is true, IFS returns #N/A. To set a default, end with a literal TRUE as the last condition - it always matches and runs the default value.

= IFS(test1, value1, [test2, value2], ...)

Arguments

IFS for letter grades: cleaner than nested IFs

Convert quiz scores to A/B/C/D/F. The classic case for nested IFs becomes a clean ordered list with IFS. Test the highest band first so it wins before lower bands match.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")

• •B2>=90, "A" -> first pair: 90 or higher gets A
• •B2>=80, "B" -> second: 80-89 gets B (90+ already matched above)
• •B2>=70, "C" -> third: 70-79 gets C
• •B2>=60, "D" -> fourth: 60-69 gets D
• •TRUE, "F" -> the catch-all: TRUE always matches, so anything below 60 gets F
• •Order critical -> if you flipped the >=60 pair to first, every passing score would return D

Performance review tiers

Year-end review distills a numeric rating (1-5) into a band: Exceeds, Meets, Improving, Below. IFS lets you write the rules in plain order with no nesting.

=IFS(C2>=4.5,"Exceeds",C2>=3.5,"Meets",C2>=2.5,"Improving",TRUE,"Below")

• •C2>=4.5 -> first pair: exceptional rating
• •C2>=3.5 -> second: solid contributor
• •C2>=2.5 -> third: needs improvement
• •TRUE, "Below" -> catch-all for the rest
• •Defaults -> always end with TRUE so unmatched cases don't return #N/A

IFS in a calculation: commission tiers

Branches don't have to be text. Each value can be a number, a formula, or a cell reference. Sales commission is the canonical case: 5% under $10k, 8% to $50k, 12% over $50k.

=IFS(B2>=50000,B2*0.12,B2>=10000,B2*0.08,TRUE,B2*0.05)

• •B2>=50000, B2*0.12 -> first pair: top tier earns 12%
• •B2>=10000, B2*0.08 -> middle tier earns 8%
• •TRUE, B2*0.05 -> everyone else earns 5%
• •Test high first -> if you tested 10000 first, no one would ever hit the 50000 tier

Map status codes to readable labels

Imported data often arrives with single-letter or numeric status codes. IFS lets you turn them into human-readable strings without nesting.

=IFS(B2="A","Active",B2="P","Pending",B2="C","Cancelled",B2="X","Expired",TRUE,"Unknown")

• •B2="A", "Active" -> exact-match pairs
• •TRUE, "Unknown" -> catch-all keeps an undocumented code from breaking the column with #N/A
• •For fixed lookups -> consider SWITCH instead - it's slightly cleaner when every test is exact equality

IFS plus AND: bonus tier eligibility

Each IFS test is a boolean expression - so wrap multiple conditions in AND() or OR() and feed them in. Bonus tiers might require both years of service AND rating.

=IFS(AND(B2>=5,C2>=4.5),"Senior bonus",AND(B2>=3,C2>=4),"Standard bonus",TRUE,"Not yet")

• •AND(B2>=5,C2>=4.5) -> first tier: 5+ years AND rating 4.5+
• •AND(B2>=3,C2>=4) -> second: 3+ years AND rating 4+
• •TRUE, "Not yet" -> everyone else
• •Same with OR() -> wrap any combination of conditions in OR() if either should trigger the tier

Where people go wrong

1. 01
   Wrong order of conditions

   Listing a broad condition (B2>=0) before a narrow one (B2>=90) means the broad one always matches first. Every score returns the broad value, the narrow value is never reached.

   Fix: Test from most-specific to most-general. Highest threshold first. Catch-all (TRUE, defaultValue) last.
2. 02
   Forgetting the catch-all

   If no condition matches, IFS returns #N/A. A single edge case slips through and the column displays errors.

   Fix: End every IFS with TRUE as the last test and a default value. =IFS(..., TRUE, "Default") catches anything you didn't explicitly handle.
3. 03
   Using IFS on Excel 2016 or older

   IFS was added in Excel 2019. On older versions the formula returns #NAME?. Sharing a workbook with someone on an older version may break.

   Fix: If you need to support Excel 2016 or earlier, fall back to nested IF. For Excel 365 / 2019+ users, IFS is fine.
4. 04
   IFS where SWITCH would be cleaner

   When every condition is "this cell equals exactly that value", IFS feels repetitive: =IFS(B2="A","Active",B2="B","Beta",B2="C","Cancelled"). SWITCH does the same thing in less syntax.

   Fix: =SWITCH(B2,"A","Active","B","Beta","C","Cancelled","Unknown") is shorter for pure equality maps. Use IFS when the conditions are comparisons or ranges.

Notes

• IFS stops at the first TRUE condition. Subsequent pairs are not evaluated.
• Always end with TRUE as the last test plus a default value to avoid #N/A errors.
• IFS supports up to 127 condition/value pairs.
• Each condition can be a comparison (B2>=10), an equality (B2="yes"), or a function call (AND(...), OR(...), ISNUMBER(B2)).
• Values can be text, numbers, cell references, or formulas - not just static strings.
• Available in Excel 2019, Excel 2021, and Excel 365. Not in older versions.
• For pure equality maps (B2 equals X, B2 equals Y), SWITCH is usually shorter than IFS.

What to learn next

Now prove it