FormulaCraft

ISNA

ISNA specifically detects the #N/A error, returning TRUE only for that error type and FALSE for all other values including other errors. It is most often used with VLOOKUP, MATCH, or HLOOKUP to substitute a friendly message or a zero when a lookup target is not found in the range.

Excel
=IF(ISNA(MATCH(A2,B2:B5,0)),"Not found",MATCH(A2,B2:B5,0))
Google Sheets
=IF(ISNA(MATCH(A2,B2:B5,0)),"Not found",MATCH(A2,B2:B5,0))
✓

Verified example

Computed by a real spreadsheet engine on the sample data below.

LookupValueTableResult
AppleAppleFound
GrapeBananaNot found
BananaCherryNot found

=IF(ISNA(MATCH(A2,B2:B5,0)),"Not found",MATCH(A2,B2:B5,0))→1

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

How it works

  1. 1Wrap the lookup formula with ISNA to check whether it returns #N/A (e.g., ISNA(VLOOKUP(...))).
  2. 2ISNA returns TRUE only for #N/A and FALSE for valid results or other error types.
  3. 3Embed in IF to display a custom message: =IF(ISNA(VLOOKUP(A2,B:C,2,0)),"Missing",VLOOKUP(A2,B:C,2,0)).

Need a version for your data?

Try: “Show 'Not found' instead of #N/A when a VLOOKUP can't find a match”

Related

ISBLANKISTEXTISERROR

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: