Handle Missing Prices with IFERROR
Your task: In D2, look up each SKU's price, showing Not in price list when it is missing, then fill it down.
Full instructions, scenario and goal
Scenario
The purchasing team prices eight order lines from a price list. A few SKUs were discontinued and are no longer in the list, so a plain lookup shows #N/A and breaks the totals that follow. The sheet should show the price when the SKU is in the list, and the text Not in price list when it is not.
Your task
- Look at the order lines in A1:C9 and the price list in F1:G7.
- In D2, write a formula that looks up the SKU from B2 in the price list and returns its unit price. If the SKU is not in the list, it must show the text Not in price list, spelt exactly like that.
- Lock the price list with $ signs, press Enter, then copy D2 and paste it into D3:D9.
- Select Check Answer.
You are done when
- Every cell in D2:D9 contains a formula that finds the SKU's price in the price list.
- A SKU that is not in the list shows the text Not in price list, not #N/A.
- The formulas still work if the SKUs or the price list change.
What you will practise
Replace an Excel error with a useful result using IFERROR, and understand what IFERROR hides.
The idea in 30 seconds
IFERROR wraps another formula. If that formula works, IFERROR returns its result unchanged. If it produces an error such as #N/A, #DIV/0! or #VALUE!, IFERROR returns the second argument instead. It turns an alarming error into a message or a harmless value, for example a lookup that finds nothing.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell D2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
IFERROR wraps another formula. If that formula works, IFERROR returns its result unchanged. If it produces an error such as #N/A, #DIV/0! or #VALUE!, IFERROR returns the second argument instead. It turns an alarming error into a message or a harmless value, for example a lookup that finds nothing.
Use it deliberately. IFERROR catches every kind of error, including ones caused by a typing mistake in the formula, so a wide IFERROR can hide a real problem. Keep the wrapped formula small and choose a replacement that is obvious to the reader, such as a short message, because a replacement of 0 can be mistaken for a genuine value.
Skills you will practise
- IFERROR
- VLOOKUP
- Error handling
- Absolute references
Topic guide: Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH
Complete first: Look Up a Product's Price
Common mistakes to avoid
- Replacing the error with 0
- A price of 0 looks like real data and would quietly reduce a total. A clear message shows that something needs attention.
- A price list that slides
- Without $ signs the price list range moves down as the formula is copied, and SKUs near the top of the list stop being found.