Always Pair VLOOKUP with ISNA (or IFERROR)

#excel #spreadsheet #beginners #productivity

The Problem

VLOOKUP returns a #N/A error when the lookup value isn't found.

=VLOOKUP(A2, ProductMaster!A:B, 2, FALSE)

If the value in A2 doesn't exist in the product master, the cell shows #N/A.

That's annoying on its own — but the real problem is that any formula referencing that cell also breaks.

=SUM(B2:B10)  -- if any cell in range is #N/A, the whole SUM returns #N/A

Fix with ISNA

ISNA returns TRUE if the value is #N/A, FALSE otherwise. Combine it with IF:

=IF(ISNA(VLOOKUP(A2, ProductMaster!A:B, 2, FALSE)), "", VLOOKUP(A2, ProductMaster!A:B, 2, FALSE))

When no match is found, the cell returns an empty string instead of #N/A. The cascading error problem goes away.

Shorter: Use IFERROR

Excel 2007+ has IFERROR, which is the modern standard:

=IFERROR(VLOOKUP(A2, ProductMaster!A:B, 2, FALSE), "")

Much cleaner. One caveat: IFERROR catches all errors (#REF!, #VALUE!, etc.), not just #N/A. If you need to distinguish between error types, stick with ISNA.

Comparison

Approach	Length	Error scope
`ISNA` + `IF`	Verbose	`#N/A` only
`IFERROR`	Concise	All errors

Bonus: Use ISNA for Conditional Display

ISNA is also useful when you want to show a value based on whether a match exists:

=IF(ISNA(VLOOKUP(A2, ProductMaster!A:B, 2, FALSE)), "Not found", "Found")

Takeaway

Whenever you write a VLOOKUP, wrap it in IFERROR right away. It's a simple habit that keeps your spreadsheets clean and prevents confusing error cascades.

About SIcore Framework

I'm building an open-source Java framework designed to make enterprise web app development straightforward — even for junior developers. HTML, CSS, JavaScript, and Java are all structured around consistent, minimal conventions.