VLOOKUP and #N/A

  • Thread starter Thread starter brian
  • Start date Start date
B

brian

I know there must be a way to do this, but.....

When doing a VLOOKUP, instead of "#N/A" being used when a
value is not found, I want it to return a zero.
 
brian

Enclose your VLOOKUP in an IF(ISERROR()) formula:

=IF(ISERROR(yourformula),"",yourformula)

Andy.
 
A couple of options in order of efficiency and beauty...

1] Use an additional cell:

In B1 enter:

=VLOOKUP(LookupValue,LookupTable,ColIdx,0)

In A1 enter:

=IF(ISNA(B1),0,B1)

2] Download & install the free morefunc.xll add-in
(http://longre.free.fr/english/index.html) & use:

=IF(ISNA(SETV(VLOOKUP(LookupValue,LookupTable,ColIdx,0))),0,GETV())

which is as efficient as Option 1.

3] If Option 2 is not feasible, add the following code to your workbook as a
module:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

and use:

=IF(ISNA(V(VLOOKUP(LookupValue,LookupTable,ColIdx,0))),0,V())

4] If efficiency is not important to you, use either

=IF(ISNUMBER(MATCH(LookupValue,INDEX(LookupTable,0,1),0)),VLOOKUP(LookupValu
e,LookupTable,ColIdx,0),0)

or

=IF(ISNA(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue,Lo
okupTable,ColIdx,0))

5] Reject using either senseless

=IF(ISERROR(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue
,LookupTable,ColIdx,0))

or rather inefficient

=IF(COUNTIF(LookupValue,INDEX(LookupTable,0,1)),VLOOKUP(LookupValue,LookupTa
ble,ColIdx,0),0)
 
Back
Top