#n/a - in vlookup

[ppashi]

What formula can convert cell value "#n/a" to zero.
I have a table of vlookup range and some cell contains
#n/a which I would like to change to 0.

 

[Gord Dibben]

ppashi

=IF(ISNA(VLOOKUP(K4,E1:G26,3,FALSE)),0,VLOOKUP(K4,E1:G26,3,FALSE))

If you have a lot of them to convert you could copy/paste this code to a
general module in your workbook or your Personal.xls file.

Sub NATrapAdd()
Dim myStr As String
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = True Then
If Not Cel.Formula Like "=IF(ISNA*" Then
myStr = Right(Cel.Formula, Len(Cel.Formula) - 1)
Cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Select your range of cells and run the macro to add the ISNA trap to all.

If not comfortable with macros, visit David McRitchie's website on getting
started with VBA and macros.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP - XL97 SR2 & XL2002