IFS Function in Excel and Google Sheets
Purpose
Test multiple conditions in sequence and return the result paired with the first one that is true
Return value
The value paired with the first logical_test that evaluates to TRUE, or #N/A if no condition matches
IFS is the function that finally killed deeply IF Formula">nested IF formulas in my spreadsheets. Before IFS arrived, assigning a letter grade or classifying a value into more than three tiers meant writing an IF inside an IF inside an IF — readable for the person who wrote it, incomprehensible to everyone else. IFS replaces that entire structure with a flat list of condition-result pairs evaluated in sequence. The first condition that is TRUE determines the output. Write as many conditions as you need — grade boundaries, commission tiers, shipping rate bands, priority levels — and each pair sits cleanly at the same level with no nesting. Add TRUE as the final condition as a catch-all and you have a complete multi-tier decision formula that any colleague can read and maintain without deciphering bracket depth. IFS is available in Excel 2019, Excel 365, and all versions of Google Sheets.
Syntax
✓ Excel 2019+
✓ Google Sheets
= Same syntax
=IFS(logical_test1, value1, [logical_test2, value2], ...)
Arguments
| Argument | Required | Description |
|---|---|---|
| logical_test1 | Required | The first condition to evaluate. If TRUE, value1 is returned and no further conditions are checked. |
| value1 | Required | What to return when logical_test1 is TRUE. Can be a number, text, cell reference, or formula. |
| logical_test2, value2 ... | Optional | Additional condition-result pairs, up to 127 pairs total. Evaluated in order — only the first TRUE condition's paired value is returned. |
| TRUE, default | Optional | Use TRUE as a final logical_test as a catch-all else clause. Without it, IFS returns #N/A when no condition is TRUE. |
How it works
IFS evaluates condition-result pairs from left to right, stopping at the first TRUE condition and returning its paired value. Because conditions are checked in order, put the most restrictive or specific conditions first. For a grade scale where >=90 earns A and >=80 earns B, place >=90 before >=80 — if >=80 came first, a score of 95 would match it and incorrectly return B. The most common mistake is forgetting the catch-all: without a final TRUE condition, any row that fails every test returns #N/A. Always end with TRUE paired with the default result. IFS is available from Excel 2019 — for Excel 2016 and earlier, use IF Formula">nested IF instead. When comparing a single expression against fixed values rather than ranges, SWITCH is even more compact.
Examples
1
Assign a letter grade to each student: A for 90 and above, B for 80+, C for 70+, D for 60+, F below 60.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Alice | 95 | =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F") |
| 3 | Bob | 82 | B |
| 4 | Carol | 74 | C |
| 5 | Dan | 61 | D |
| 6 | Eve | 48 | F |
| 7 | Finn | 91 | A |
Row 2: A
— Alice scored 95 which passes the first condition (>=90) so IFS returns A immediately without evaluating B, C, D, or the catch-all.
TRUE at the end acts as the else clause — Eve's 48 fails every grade boundary and falls through to the catch-all, returning F. Without TRUE as the final condition, Eve's row would return #N/A.
2
Assign a shipping rate by order weight: 3.99 for up to 0.5 kg, 6.99 up to 2 kg, 9.99 up to 5 kg, 14.99 for anything heavier.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Weight kg | Rate |
| 2 | ORD-001 | 0.3 | =IFS(B2<=0.5,3.99,B2<=2,6.99,B2<=5,9.99,TRUE,14.99) |
| 3 | ORD-002 | 1.5 | 6.99 |
| 4 | ORD-003 | 4.2 | 9.99 |
| 5 | ORD-004 | 7.1 | 14.99 |
| 6 | ORD-005 | 0.8 | 6.99 |
Row 2: 3.99
— ORD-001 weighs 0.3 kg which is at most 0.5 — the first condition passes and 3.99 is returned.
ORD-004 at 7.1 kg fails all three weight conditions and falls through to the TRUE catch-all, returning 14.99. Conditions must go from smallest to largest — if TRUE came first every order would get 14.99.
3
Map each support ticket to a priority level: P1 for Critical, P2 for High, P3 for Medium, P4 for everything else.
| A | B | C | |
|---|---|---|---|
| 1 | Ticket | Severity | Priority |
| 2 | TKT-001 | Critical | =IFS(B2="Critical","P1",B2="High","P2",B2="Medium","P3",TRUE,"P4") |
| 3 | TKT-002 | High | P2 |
| 4 | TKT-003 | Medium | P3 |
| 5 | TKT-004 | Low | P4 |
| 6 | TKT-005 | High | P2 |
Row 2: P1
— TKT-001 has severity Critical which matches the first condition so P1 is returned without evaluating the remaining pairs.
TKT-004 has severity Low which matches none of the three named conditions — it falls through to the TRUE catch-all and receives P4. Any unrecognised severity also gets P4.
Common use cases
1. Assign letter grades from exam scores across five bands without deeply nested IFs
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")
2. Calculate a tiered commission rate that increases with monthly sales totals
=IFS(C2>=50000,0.12,C2>=30000,0.09,C2>=10000,0.06,TRUE,0.03)
3. Classify customer accounts as Platinum, Gold, Silver, or Bronze based on annual spend
=IFS(D2>=100000,"Platinum",D2>=50000,"Gold",D2>=20000,"Silver",TRUE,"Bronze")
4. Assign shipping regions to delivery post codes based on their prefix
=IFS(LEFT(B2,2)="NW","North West",LEFT(B2,2)="SE","South East",LEFT(B2,2)="NE","North East",TRUE,"Other")
5. Map numeric status codes to human-readable labels for a report display column
=IFS(C2=1,"Pending",C2=2,"Active",C2=3,"Complete",C2=4,"Cancelled",TRUE,"Unknown")
Common errors
#N/A — no condition matched
No logical_test evaluated to TRUE and there is no catch-all TRUE condition as the final pair.
Fix: Always add TRUE as the last logical_test paired with a default result. This acts as the else clause and prevents #N/A for any row that passes none of the preceding conditions.
Wrong result returned for a specific value
Conditions are in the wrong order. IFS stops at the first TRUE, so a broad condition like >=60 before >=90 will return the wrong tier for high values.
Fix: Order conditions from most restrictive to least restrictive — highest thresholds or most specific values first.
#NAME? or formula not recognised in older Excel
IFS is not available in Excel 2016 or earlier. Opening a file with IFS in an older version causes a #NAME? error.
Fix: Use nested IF for backward compatibility with Excel 2016 and earlier. If the file only opens in Excel 2019 or newer, IFS is safe to use.
Tips and variations
Always end with TRUE as the catch-all else clause
Without TRUE as the last condition, any row that passes none of the tests returns #N/A rather than a default. I always add TRUE as the final condition — it acts as the else branch of the decision and makes the formula complete.
=IFS(B2>=90,"A",B2>=80,"B",TRUE,"C or below")
Order conditions from most restrictive first
IFS returns the result of the first matching condition and stops. Put the highest threshold or most specific value first. A score of 95 should match >=90 first and correctly get A — if >=70 came first it would match that and return a lower grade.
=IFS(C2>=50000,0.12,C2>=30000,0.09,C2>=10000,0.06,TRUE,0.03)
Switch to SWITCH when matching fixed values instead of ranges
IFS is ideal for range conditions and inequalities. When comparing a single expression against exact fixed values — department codes, status numbers — SWITCH is more compact because it states the expression once and lists only the values to match.
=SWITCH(B2,"Critical","P1","High","P2","Medium","P3","P4")
Excel vs Google Sheets
Excel vs Google Sheets
IFS is available in Excel 2019, Excel 365, and all versions of Google Sheets. It is not available in Excel 2016 or earlier — use IF Formula">nested IF in those versions. The syntax and behaviour are identical in Excel 2019+ and Google Sheets.
Frequently asked questions
IFS tests a series of conditions in order and returns the value paired with the first condition that is TRUE. It is the modern replacement for chains of nested IF statements when you have three or more distinct outcomes. Instead of writing IF inside IF inside IF, IFS takes condition-result pairs in a flat list and evaluates them from left to right.
Nested IF puts one IF inside the false branch of another, creating deeply indented formulas that are hard to read and maintain. IFS lists all condition-result pairs at the same level with no nesting, making each branch immediately visible. It also stops at the first true condition, so there is no need to structure conditions around previous false branches.
Use TRUE as the last condition argument. Since TRUE is always true, it acts as the final else clause and is reached only when none of the preceding conditions passed. The pattern is =IFS(test1,result1,test2,result2,...,TRUE,default). Without TRUE as a catch-all, IFS returns #N/A when no condition matches.
IFS returns #N/A when every condition evaluates to FALSE and there is no catch-all condition. This is different from nested IF which returns FALSE when no false branch is specified. Always add TRUE as a final condition with a default value to prevent unexpected #N/A errors.
No. IFS was introduced in Excel 2019 and is also available in Excel 365. It is not available in Excel 2016, 2013, 2010, or earlier. If your file needs to work in older versions, use nested IF instead. IFS is available in all versions of Google Sheets.