What you will build

Reproduce the #N/A errors case on a small table, identify the cause, and verify the corrected formula or input returns the expected result.

Learning objectives

Open the practice workbook

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The Google Sheets practice table before formulas are entered; E2 and F2 are blank.

Hands-on practice

Step 1: Confirm D2 contains lookup key P03 while the source list in A2:B3 contains only P01 and P02.

Step 2: Enter =VLOOKUP(D2,A2:B3,2,FALSE) in E2. With exact matching, the missing key must return #N/A.

Step 3: Enter =IFNA(VLOOKUP(D2,A2:B3,2,FALSE),"Missing SKU") in F2 and confirm only #N/A is replaced with the explicit message.

=VLOOKUP(D2,A2:B3,2,FALSE)
Cell E2 is selected with =VLOOKUP(D2,A2:B3,2,FALSE) in the formula bar and #N/A in the sheet.

Enter the VLOOKUP formula in E2, where the broken lookup result belongs.

=IFNA(VLOOKUP(D2,A2:B3,2,FALSE),"Missing SKU")
Cell F2 is selected with the IFNA-wrapped VLOOKUP formula in the formula bar and Missing SKU in the sheet.

Enter the IFNA-wrapped formula in F2, where the fixed result belongs.

Checkpoint

SKUPriceLookup SKUBroken resultFixed result
P0112P03#N/AMissing SKU
P0235

For formula lessons, result cells must be calculated rather than typed manually.

Common mistakes and fixes

Correction: return to the exact range and command named in the steps, then compare the live sheet with the SKU, Price, , Lookup SKU, Broken result, Fixed result source fields.

Independent practice

Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Key takeaways

  • ✓Reproduce the #N/A errors case on a small table, identify the cause, and verify the corrected formula or input returns the expected result.
  • ✓Confirm D2 contains lookup key P03 while the source list in A2:B3 contains only P01 and P02.
  • ✓Enter =VLOOKUP(D2,A2:B3,2,FALSE) in E2. With exact matching, the missing key must return #N/A.
  • ✓Keep the broken example visible while testing the correction. If the fixed cell still errors, compare references and input types instead of typing a replacement value over the formula.
  • ✓Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. What is the correct first action in “Fix #N/A errors”?
2. Which follow-up action belongs to “Fix #N/A errors”?
3. What should you verify before “Fix #N/A errors” is complete?
4. Which correction is appropriate if “Fix #N/A errors” behaves unexpectedly?

Next lesson

Continue with Fix #DIV/0! errors to troubleshoot another common spreadsheet error.