Show #N/A as zero

[barney5]

I have a large report template 'fed' from numerous tables using VLOOKUP. Whenever a table value isn't available, the value #N/A is returned in the report cell. The problem I'm having is that 1/3 of the report is populated with #N/A's. I tried to do a Find/Replace (replace #N/A with zero) but the error message displays "Microsoft Excel cannot find the data you're searching for."

I also thought of going through the report and manually changing the #N/A's to zeroes but not only is this a tedious process but if the source table changes to now include a report value that had been 'hardcoded' as zero, the report won't reflect the updated value.

Is there a way to replace the #N/A as zero in a cell without affecting VLOOKUP's intended function?

Thank you for your assistance.

 

[texasalynn]

You can use an if statement:

=if(isna(vlookup.....),0,vlookup...)

HTH
texasalynn

 

[Joe4]

You could use the ISNA function with an IF statement:

=IF(ISNA(VLOOKUP(...),0,VLOOKUP(...))

EDIT: DOH! Too slow! Looks like texaslynn beat me to the punch!

 

[IML]

Same idea, with improved performance
use
=IF(ISNUMBER(MATCH(E1,$A$1:$A$10,0)),VLOOKUP(E1,$A$1:$B$10,2,0),0)

instead of
VLOOKUP(E1,$A$1:$B$10,2,0)

or a two cell approach
with a lookup formula in A1
read your data in B1 with =If(isna(a1),0,a1)

this prevents the rather slow vlookup process from repeating twice.

 

[pcc]

or use this to replace errors by zero

Sub fix_errors()
On Error GoTo quit ' in case there are no errors
Selection.SpecialCells(xlCellTypeFormulas, 16).Select

Selection.Replace what:="*", Replacement:=0, lookat:=xlPart, _
searchorder:=xlByColumns, MatchCase:=False
Exit Sub
quit:
End Sub