Excel practice · Putting It Together

IFERROR practice exercises

5 graded problems with realistic data, all hard.

Catches a formula that would return an error and returns something else instead. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.

New to the function? Read the IFERROR guide first: syntax, a worked example and the mistakes people make.

In this set

  1. Show a friendly message when a lookup fails
  2. See IFERROR pass a good result through
  3. Wrap a MATCH that might fail
  4. Wrap an XLOOKUP that might fail
  5. Default a missing rate to zero

The exercises

1. Show a friendly message when a lookup fails Hard

SKU-9 doesn't exist in the price list. Instead of an error, show "Not in catalog".

ABCDE
1SKUPriceSKU:SKU-9
2SKU-110Price:?
3SKU-215

Enter a formula in E2.

Show a hint

IFERROR(formula, value_if_error) wraps the whole lookup: =IFERROR(VLOOKUP(E1,A2:B3,2,FALSE),"Not in catalog").

This is IFERROR's single most common real use: hiding a #N/A behind a message a reader can actually understand.

2. See IFERROR pass a good result through Hard

SKU-2 DOES exist in this slightly longer, three-SKU price list. Confirm IFERROR just returns the real value when there's no error.

ABCDE
1SKUPriceSKU:SKU-2
2SKU-110Price:?
3SKU-215
4SKU-320

Enter a formula in E2.

Show a hint

One more row in the table, same idea: =IFERROR(VLOOKUP(E1,A2:B4,2,FALSE),"Not in catalog").

IFERROR only steps in when there IS an error. It never changes a valid result, which is exactly what you want.

3. Wrap a MATCH that might fail Hard

"Rome" isn't in the city list. Instead of #N/A, show "Not Found".

ABCDE
1CityFind:Rome
2ParisResult:?
3Lyon

Enter a formula in E2.

Show a hint

=IFERROR(MATCH(E1,A2:A3,0),"Not Found").

Any formula that can return an error (MATCH, VLOOKUP, XLOOKUP without a fallback) can be wrapped in IFERROR the same way.

4. Wrap an XLOOKUP that might fail Hard

Code A9 isn't in the table. Instead of #N/A, show "Unknown".

ABCDE
1CodeNameCode:A9
2A1WidgetName:?
3A2Gadget

Enter a formula in E2.

Show a hint

=IFERROR(XLOOKUP(E1,A2:A3,B2:B3),"Unknown"). XLOOKUP also has its own built-in 4th-argument fallback; this shows the IFERROR way of doing the same thing.

Knowing both ways (XLOOKUP's built-in fallback and wrapping in IFERROR) matters because VLOOKUP and MATCH only have the IFERROR option.

5. Default a missing rate to zero Hard

Look up the rate for code Z4 in this four-row rate table. If it were missing, this should default to 0 instead of erroring.

ABCDE
1CodeRateCode:Z4
2Z10.05Rate:?
3Z20.08
4Z30.1
5Z40.12

Enter a formula in E2.

Show a hint

The fallback doesn't have to be text, it can be a number: =IFERROR(VLOOKUP(E1,A2:B5,2,FALSE),0).

Defaulting a missing rate to 0 (instead of erroring out a whole downstream calculation) is a very deliberate, very common modeling choice.

Solve these in a real grid

Type your formula, get graded instantly. Your first 10 exercises are free, no signup.

Start the IFERROR exercises →

More putting it together practice

All Excel practice exercises · Excel test prep