Display 0 instead of #N/A

[zzdave]

I know there is a way to do this but can't for the life of me remember it. I am running a vlookup, and in some cells it quite rightly returns #N/A. However I want the cell to display 0 instead. I have tried some 'if' formulas but with no success.
Any help greatly appreciated.
David Williams

 

[DonkeyOte]

if(isna(vlookup(blah,blah,1,false)),0,vlookup(blah,blah,1,false))

ISNA will only omit NA errors whereas

ISERROR will omit all errors.

 

[Mudface]

You could use something like IF(ISNA(your formula),0,(your formula)).

 

[zzdave]

Thanks a lot for your help - works a treat.

 

[WillR]

If you want to do this on a large scale.... perhaps this macro will help - can't claim the credit for it but it works a dream & add IF(ISERR formulas around all formulas in a worksheet.

Sub ErrorTrapAddDDL()

' Adds =If(IsError() around formulas

  Dim cel As Range
  Dim rng As Range
  Dim Check As String

  Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

  Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

  On Error Resume Next

  Set rng = Selection.SpecialCells(xlFormulas, 23)
  If rng Is Nothing Then Exit Sub

  With WorksheetFunction
    For Each cel In rng
      If Not cel.Formula Like Check Then
        cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
      End If
    Next
  End With
End Sub

HTH

 

[Aladin Akyurek]

On 2002-10-18 05:13, zzdave wrote:
I know there is a way to do this but can't for the life of me remember it. I am running a vlookup, and in some cells it quite rightly returns #N/A. However I want the cell to display 0 instead. I have tried some 'if' formulas but with no success.
Any help greatly appreciated.
David Williams

If you'd want to install the morefunc.xll add-in, downloadable from

http://longre.free.fr/english/index.html

you can use the more efficient

=IF(ISNA(SETV(VLOOKUP(lookup-value,lookup-table,columnindex,0))),"",GETV())

which avoids computing the same thing twice.