Excel formula troubleshooting workbook: #N/A, #VALUE! and #REF!
Work through three different failures with a concrete faulty formula, the cause and a separate repaired result.
Download the example workbook (.xlsx)
Illustrative data. English function names and comma argument separators are shown; Excel may use different separators or translated function names. The file was structurally inspected, but native Excel calculation and UI behavior need local verification.
Three cases in one sheet
Missing key → #N/A: B2 contains ZX-9, which is absent from the A7:B8 key table. C2 uses an exact VLOOKUP and should show #N/A. D2 supplies A-10 and E2 should return Widget. Check the key itself before hiding the error with IFERROR.
Incompatible input → #VALUE!: B3 contains the word “five”. C3 multiplies it by 2 and should show #VALUE!. D3 contains numeric 5; E3 should return 10. Replace or parse the input only when the source meaning is known.
Broken reference → #REF!: The REF reproduction sheet starts with A2=7 and B2 =A2*2, so B2 is 14. Make a copy of the file, then delete column A on that sheet; the shifted formula has no valid source and becomes #REF!. The Example sheet preserves that after-deletion formula in C4 as =#REF!*2. Restore the intended source value 7 in D4 and the reference in E4 (=D4*2); E4 should return 14. This repair replaces the lost dependency, not just its displayed value.
Check the error type against Microsoft guidance
Microsoft documents missing lookup values (#N/A), incompatible values (#VALUE!) and invalid references (#REF!) separately. Compare the actual formula and input before changing data.
Do not treat the errors as interchangeable
The three faulty cells are intentional demonstrations; the repaired cells have separate expected outputs. For a visually matching key with different number/text types, use the lookup type diagnosis. For a result blocked from spilling into neighboring cells, use the spill diagnosis. Those need different remedies.
Compatibility and source
Compatibility: These examples use VLOOKUP, arithmetic and ordinary cell references, so they do not require XLOOKUP or dynamic arrays. They are intended for Excel 2016/2019 and current desktop editions; exact deletion UI and recalculation still need native Excel verification.
The three Microsoft error guides linked above describe the specific failure types. Start with the broad formula and shortcut reference or browse more Excel tips.