XLOOKUP with 0 as the fallback
A missing item should cost nothing in the total, not break it.
Task:
Some items on the order are freebies with no price on the list. In C2:C6 give each line's cost — quantity times the listed price, with unlisted items costing 0 — and the order total in C7.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of XLOOKUP
0/3The same formula in the other shapes it takes at work.
- XLOOKUP Function
The lookup that handles a missing value without a second function wrapped around it.
CoreAdvanced - XLOOKUP to the left, with a default
Search one column, return from another on either side, and say what to show when nothing matches.
Intermediate - XLOOKUP with 0 as the fallback
A missing item should cost nothing in the total, not break it.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Qty | Cost | Item | Price | |
| 2 | Mug | 4 | Mug | 6 | ||
| 3 | Tote bag | 2 | Notebook | 4.5 | ||
| 4 | Notebook | 10 | Pen set | 8 | ||
| 5 | Sticker | 25 | ||||
| 6 | Pen set | 3 | ||||
| 7 | Total |
What this exercise teachesMay contain the answer
A text fallback like "Not found" makes the multiplication fail, and one #VALUE! in the column breaks the total. Choosing 0 keeps the arithmetic going — appropriate here because free items really do cost nothing. Pick the fallback for what the missing case means, not out of habit.