On 2002-09-30 18:45, Parra wrote:
The formula below works fine.
VLOOKUP(B14,data2002,3,0)
But in some cases the result it #N/A.
Any suggestions on how to remove the #N/A? Anything I can write into the formula.
Thanks
Three efficient options...
(1)
=IF(ISNA(SETV(VLOOKUP(B14,data2002,3,0))),"",GETV())
which requires the morefunc.xll add-in, downloadable from
http://longre.free.fr/english/index.html
(2)
=IF(ISNUMBER(MATCH(B14,INDEX(data2002,0,1),0)),VLOOKUP(B14,data2002,3,0),"")
(3) If you can't add morefunc for some reason, use the following UDF to your WB:
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
=IF(ISNA(v(VLOOKUP(B14,data2002,3,0))),0,v())