1
Guard a Product Lookup With a Friendly Message
Free
A2:B4 maps Product to Price. D2 holds a product name that might not be in the table. Write a formula in E2 that returns the price, or 'Product not found' if the lookup fails.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Widget | 9.99 | | Gizmo | ? |
|---|
| 3 | Gadget | 14.5 | | | |
|---|
Show the solution
Wrap the VLOOKUP in IFERROR: =IFERROR(VLOOKUP(D2,A2:B3,2,FALSE),"Product not found")
Right — wrapping VLOOKUP in IFERROR turns a raw #N/A into a message anyone can understand.
Expected result in E2: Product not found
2
Guard a Successful Lookup the Same Way
Free
A2:B4 maps Product to Price, and D2 holds a name that IS in the table. Write a formula in E2 using the same IFERROR-wrapped VLOOKUP pattern.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Widget | 9.99 | | Widget | ? |
|---|
| 3 | Gadget | 14.5 | | | |
|---|
Show the solution
Same pattern: =IFERROR(VLOOKUP(D2,A2:B3,2,FALSE),"Product not found")
Good — when the lookup succeeds, IFERROR is invisible and just returns the real value.
Expected result in E2: 9.99
3
Combine IFERROR With a Default Numeric Fallback
Free
A2:B4 maps SKU to Cost, and D2 holds a SKU that might not exist. Write a formula in E2 that returns the cost or 0 if not found.
Show the solution
Use =IFERROR(VLOOKUP(D2,A2:B3,2,FALSE),0)
Right — a numeric fallback like 0 keeps downstream SUM/AVERAGE formulas from breaking.
Expected result in E2: 0
4
Guard an XLOOKUP-Based Price Check
A2:A4 and B2:B4 map Product to Price via XLOOKUP. D2 holds a product name that might not exist. Write a formula in E2 that returns the price or 'N/A' if not found, using IFERROR around XLOOKUP.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Widget | 9.99 | | Sprocket | ? |
|---|
| 3 | Gadget | 14.5 | | | |
|---|
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →
5
Build a Full Error-Safe Classification
A2:B4 maps Product to Price, D2 holds a product name that might not exist. Write a formula in E2 that returns 'Expensive'/'Affordable' if found, or 'Unknown' if not — combining IFERROR, VLOOKUP, and IF.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Widget | 9.99 | | Zeta | ? |
|---|
| 3 | Gadget | 14.5 | | | |
|---|
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →
6
Guard a Successful Full Classification
A2:B4 maps Product to Price, D2 holds a name that IS in the table. Write a formula in E2 using the same combined IFERROR+IF+VLOOKUP pattern.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Widget | 9.99 | | Gadget | ? |
|---|
| 3 | Gadget | 14.5 | | | |
|---|
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →
7
Guard an Employee Lookup With a Department Fallback
A2:B4 maps Employee to Department, D2 holds a name that might not exist. Write a formula in E2 that returns the department or 'Not an Employee' if not found.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Chen | Sales | | Omar | ? |
|---|
| 3 | Diego | Support | | | |
|---|
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →
8
Guard a Rate Lookup With a Standard Default Rate
A2:B4 maps Tier to Rate, D2 holds a tier that might not exist. Write a formula in E2 that returns the rate or a default of 0.1 if not found.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Gold | 0.15 | | Bronze | ? |
|---|
| 3 | Silver | 0.1 | | | |
|---|
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →
9
Guard a City Lookup With a Region Fallback
A2:B4 maps City to Region, D2 holds a city that might not exist. Write a formula in E2 that returns the region or 'Unmapped' if not found.
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | Austin | South | | Miami | ? |
|---|
| 3 | Boston | Northeast | | | |
|---|
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →
10
Guard a Part Lookup With a Reorder Fallback Message
A2:B4 maps Part Number to Stock Count, D2 holds a part number that might not exist. Write a formula in E2 that returns the stock count or 'Check Part Number' if not found.
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →