Display #N/A as 0 in VLookup

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

When writing a Vlookup function to find an exact match, I am trying to
show the #N/A result as a 0, using an If statement. Whenever I refer to
"#N/A" in the statement, #N/A shows. I want to show the result value if
there is a match, and 0 if there is no match. Is there an operator for
"not a number"? For instance, where the #N/A is in cell B6,
=IF(B6=#N/A,"0",B6) returns #N/A, and the argument B6 does not return
the value in cell B6. Any suggestions? I'm using Excel 2000. Thanks!
(e-mail address removed)
__
Spam filter at work,

Please replace "thebeach" with comcast.net when replying.
 
try

=IF(ISNA(formula),0,formula)

By the way, your instructions for your spam filter need work. The final
email address would be (e-mail address removed) as the instrctions are
written.
 
=IF(ISNA(your_formula),0,your_formula))

If you have many formulas to change use this code.

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

Gord Dibben Excel MVP
 
Use the ISERROR Function:-
=IF((ISERROR(VLOOKUP(A1,LookupAray,2,FALSE))),0,VLOOKUP
(A1,LookupAray,2,FALSE))

This uses the If Function to ask if there is an error. If
there is an error the first option is to insert the
Zero '0' and if there is no error to use the original
lookup again. When setting up you have to be very careful
about your bracket placement as it gets very confusing.
Hope this helps.

Note:- The #NA is a very useful return in some instances
as a Zero is a Zero where an #NA as the same as a blank
cell so if using to chart data your line charts just have
a gap in the line if the #NA is there as oppose to diving
to the base line and back up again.
 
"Dave R." <[email protected]> recently pondered:

Dave,
Thank you VERY much for the quick help. That worked like a charm.
Is there a source for solutions like this that you can suggest? Also,
thanks for the hint about my signature... it should now be fixed.

try

=IF(ISNA(formula),0,formula)

By the way, your instructions for your spam filter need work. The final
email address would be (e-mail address removed) as the instrctions are
written.
Click to expand...

Thanks,

(e-mail address removed)
__
Spam filter at work,

Please replace "thebeach.com" with comcast.net if replying via e-mail.
 
Gord Dibben <gorddibbATshawDOTca> recently pondered:

Gord,
Thanks VERY much for the quick advice. I will try the code where
there are numerous formulas.

=IF(ISNA(your_formula),0,your_formula))

If you have many formulas to change use this code.

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

Gord Dibben Excel MVP
Click to expand...

Thanks,

(e-mail address removed)
__
Spam filter at work,

Please replace "thebeach.com" with comcast.net if replying via e-mail.
 
"Nigel Graham" <[email protected]> recently pondered:

Nigel,
Thank you for another option. If I will make a chart from the data, the
#N/A is probably the best situation.


Use the ISERROR Function:-
=IF((ISERROR(VLOOKUP(A1,LookupAray,2,FALSE))),0,VLOOKUP
(A1,LookupAray,2,FALSE))

This uses the If Function to ask if there is an error. If
there is an error the first option is to insert the
Zero '0' and if there is no error to use the original
lookup again. When setting up you have to be very careful
about your bracket placement as it gets very confusing.
Hope this helps.

Note:- The #NA is a very useful return in some instances
as a Zero is a Zero where an #NA as the same as a blank
cell so if using to chart data your line charts just have
a gap in the line if the #NA is there as oppose to diving
to the base line and back up again.
Click to expand...

Thanks,

(e-mail address removed)
__
Spam filter at work,

Please replace "thebeach.com" with comcast.net if replying via e-mail.
 
Ron, honestly I think you're at the best place for solutions like that. I
don't know where else you could post a question and within 5-10 minutes have
many answers coming your way. You can also browse/read the questions people
are having, and learn from those as well.
 
Back
Top