SheetsdrillIFS
IFS(condition1, value1, [condition2, value2, ...]) checks conditions in order and returns the value paired with the first one that is TRUE. It replaces long nested IF formulas, and a final pair of TRUE, "default" catches everything else.
Syntax
=IFS(condition1, value1, [condition2, value2, ...])| Part | What it is |
|---|---|
condition1 | The first test, for example B2>=90. |
value1 | What to return when condition1 is TRUE. |
condition2, value2 | More pairs, checked only if the previous conditions were FALSE. |
Examples
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F")Result: A for 95, B for 83, C for 71, F for 50
Order matters: put the highest threshold first.
=IFS(B2>=50000, "Gold", B2>=10000, "Silver", TRUE, "Bronze")Result: Customer tier by lifetime spend
=IFS(C2<=1, 5, C2<=5, 9, C2<=20, 15, TRUE, 25)Result: Shipping rate by weight band: 9 for a 3 kg parcel
Common mistakes
- #N/A No match means no condition was TRUE; add TRUE, "Other" as the last pair.
- Thresholds in the wrong order return the wrong band: with B2>=70 before B2>=90, a 95 gets C; order from most to least restrictive.
- A missing value after a condition gives a wrong-argument-count error; every condition needs its own value.
Practice IFS
These drills check your answer on live data, free, no account needed.
- Customer tiers with IFSSpend of 5,000 or more is "Gold", 1,000 or more is "Silver", anything else is "Bronze". Fill C2 and C3 using IFS.
- Commission rate tierReturn the commission rate for the monthly sales in B1: the rate of the highest tier whose starting amount is not above the sales. Use XLOOKUP. Answer in B2.
Questions
What is the difference between IF and IFS?
IF tests one condition with a true and false result; IFS tests many in sequence and has no built-in else, which is why TRUE, value is added at the end.
Can IFS return different formulas?
Yes, each value can be a calculation: =IFS(A2="Senior", B2*1.1, A2="Junior", B2*1.05, TRUE, B2).
Related
- IFHow the IF function in Google Sheets returns one value when a condition is true and another when it is false, with examples for text, blanks and nested IFs.
- SWITCHHow SWITCH in Google Sheets compares one expression against a list of cases and returns the matching value or a default, with examples for codes and regions.
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- AND and ORHow AND and OR work in Google Sheets to test several conditions at once, how to use them in IF, and why they fail inside FILTER and ARRAYFORMULA, with fixes.