IFS, AND/OR & IFERROR practice exercises
IFERROR replaces an error with something friendlier. IFS checks several conditions in order without nesting. AND and OR combine tests inside any condition. Together they keep formulas readable as the logic grows.
Syntax
IFERROR(value, value_if_error) · IFS(cond1, value1, cond2, value2, ...)
IFERROR(value, fallback)- Returns value, or fallback if value is an error such as #N/A or #DIV/0!.
IFS(cond1, value1, ...)- Returns the value for the first condition that is TRUE. End with
TRUE, ...as a catch-all. AND(a, b) / OR(a, b)- TRUE when all (AND) or any (OR) of the tests are TRUE.
The practice data
An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=IFERROR(VLOOKUP("Eve",A2:F9,6,FALSE),"No sales")
There is no rep called Eve, so instead of #N/A the cell shows "No sales". Result:
| No sales |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return Yes if row 4 is in the East and has Sales over 100, otherwise No.
2 Grade the Sales in F6: High if 150 or more, Mid if 100 or more, else Low.
3 Look up the Sales of rep "Zed" with VLOOKUP, showing Unknown if not found.
Master IFS, AND/OR & IFERROR in the full course
Lesson 12 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon IFS, AND/OR & IFERROR mistakes
- Wrapping everything in IFERROR, which also hides genuine mistakes like a wrong range.
- Putting IFS conditions in the wrong order: the first TRUE wins, so test the highest threshold first.
- Forgetting the TRUE catch-all in IFS, which returns #N/A when nothing matches.
FAQ
IFERROR or IFNA?
IFNA only catches #N/A, which is safer for lookups because other errors still show up.
Is IFS available in Google Sheets?
Yes, IFS works in Google Sheets and Excel 2019 and later.