Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
IFERROR
XLOOKUP fails, so IFERROR shows Not listed. When the lookup works, you get the price as normal.
Try this lesson free →Example
Cable isn't in the price list, so a plain XLOOKUP shows #N/A. IFERROR catches the error and shows something useful instead:
=IFERROR(XLOOKUP(B4,H2:H5,I2:I5),"Not listed")| Order | Product | Qty | Revenue | Price | Per item | Product | Price | |
|---|---|---|---|---|---|---|---|---|
| O101 | Earbuds | 2 | 70 | Earbuds | 35 | |||
| O102 | Charger | 1 | 15 | Charger | 15 | |||
| O103 | Cable | 3 | 27 | Phone case | 8 | |||
| O104 | Phone case | 0 | 0 | Screen guard | 5 | |||
| O105 | Screen guard | 4 | 20 |
Key points
IFERROR(Wrap any formula that might fail.
XLOOKUP(...)First: the formula to try.
"Not listed"Second: what to show if it fails. Text, 0, or a blank "".
Put together: =IFERROR(formula, fallback). It also stops #DIV/0! when you divide by a zero or empty cell.
Practice
1. In E4, look up the price for order O103. Show Not listed if the product isn't there.
=IFERROR(XLOOKUP(B4,H2:H5,I2:I5),"Not listed")2. In F5, work out the price per item for order O104: revenue divided by quantity. Show 0 if it can't be worked out.
=IFERROR(D5/C5,0)3. In E2, look up the price for order O101, showing Not listed if it's missing.
=IFERROR(XLOOKUP(B2,H2:H5,I2:I5),"Not listed")4. In F5, work out the price per item for order O104 (revenue divided by quantity). Show 0 if it can't be worked out.
=IFERROR(D5/C5,0)5. In E4, look up the price for order O103. If it's not in the price list, show Check.
=IFERROR(XLOOKUP(B4,H2:H5,I2:I5),"Check")Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →