The Problem
You have a VLOOKUP formula that sometimes returns #N/A errors when it can't find a match. Instead of showing the error, you want to display 'Not Found' or a blank cell. You need to handle errors gracefully.
The Easy Way: Use SheetXAI
If you don't want to mess with formulas, the fastest way to do this is simply by asking.
With SheetXAI, you can open the sidebar and type:
If the formula in A2 returns an error, show 'Not Found' instead of the error.
SheetXAI will instantly write the formula or script for you and fill the cells. It handles the syntax so you can focus on the result.
The Manual Way: The Formulas You Need
To do this manually, you need to use: IFERROR.
1. IFERROR
Returns a custom value if a formula results in an error. Otherwise, returns the formula's result. Prevents error messages from appearing in your spreadsheet.
Syntax: =IFERROR(value, value_if_error)
- value: The formula or value to check for errors.
- value_if_error: The value to return if the formula results in any error (like #N/A, #VALUE!, #DIV/0!, etc.).
Example: IFERROR(VLOOKUP(A2, Data, 2, FALSE), 'Not Found') returns 'Not Found' if VLOOKUP can't find a match, otherwise returns the VLOOKUP result.
Understanding the Logic
- IFERROR: Checks if a formula results in an error. If yes, returns your custom value. If no error, returns the formula's result.
Common errors it handles:
- #N/A: Value not found (common with VLOOKUP, INDEX MATCH)
- #VALUE!: Wrong data type (e.g., text in a math formula)
- #DIV/0!: Division by zero
- #REF!: Invalid cell reference
- #NAME?: Function name misspelled
- #NUM!: Invalid number
- #NULL!: Invalid range
Common use cases:
- Wrap VLOOKUP to show 'Not Found' instead of #N/A
- Wrap division formulas to show 0 or blank instead of #DIV/0!
- Clean up formulas that might fail in some rows
- Make spreadsheets look professional (no error messages)
The Final Formula:
=IFERROR(A2/B2, 0)
Conclusion
Now you know the "classic" way to solve this using formulas. It's a great skill to have.
But for those times when you just want the job done without the mental math, SheetXAI is there to help.
Related Guides
- VLOOKUP in Google Sheets - Look up data (often used with IFERROR)
- INDEX MATCH in Google Sheets - More flexible lookup (often used with IFERROR)
- Nested IF in Google Sheets - Multiple conditions
- Google Sheets AI Guide - Learn how AI can automate your Google Sheets workflows
- AI Spreadsheet Tools - Discover how AI transforms spreadsheet work
