vlookup

[Parra]

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

 

[Juan Pablo González]

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

=IF(COUNTIF(column2002,B14),VLOOKUP(B14,data2002,3,0),"No match")

where column2002 is the first column of data2002

 

[MikeL]

My suggestion would be to filter the column with the #N/A result by using Data-Filter. Then, click on the drop down arrow and choose #N/A from the list. Highlite the range, hit the F5 key, choose Special,choose visible cells, choose OK. Then, hit the Delete key. This should delete all #N/A values in the highlited range.

Hope this helps.
Mike

 

[Parra]

Thanks Juan, it worked great.

Parra

 

[Aladin Akyurek]

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())