# Vlookup brings back N/A

• How do I stop the N/A from displaying when doing a V lookup. If the value is N/A I just want a blank cell or better yet a 0. Thanks

• Your foumla is in cell A14, change Jack to test and you shold get a zero as you requested.

Hope this helps

Jack in the UK

• Instead of a nested Vlookup as used in Jack's example: =IF(ISERROR(VLOOKUP(D9,A9:B11,2,FALSE)),0,(VLOOKUP(D9,A9:B11,2,FALSE)))

...you could use a sumif instead.
=SUMIF(A1:A3,D1,B1:B3) (assuming your datarange is a1:b3 and 'test' is in d1).
This only works if you want to return numeric data.

• Hi excelnewbie

I normally handle the #N/A! with a COUNTIF, eg

Code
``=IF(COUNTIF(\$D\$1:\$D\$600,"Cat"),VLOOKUP("Cat",\$D\$1:\$G\$600,2,FALSE),0)``
• Thanks everyone, I will give these examples a try at work tomorrow.

Have a great night !

• A more efficient option is:

=IF(ISNUMBER(MATCH(lookup-value,INDEX(LookupTable,0,1),0)),VLOOKUP(lookup-value,LookupTable,ColIdx,0),"")

Another, if you installed morefunc.xll (an add-in)...

=IF(ISNA(SETV(VLOOKUP(lookup-value,LTable,ColIdx,0))),"",GETV())

These options avoid computing the same formula twice, which is what will happen in the ISERROR solution(s)

HTH

• Thanks very much WillR. Where do you get the add-in? and is it free?

• Another twist to Jack's formula

Jack's example: =IF(ISERROR(VLOOKUP(D9,A9:B11,2,FALSE)),0,(VLOOKUP(D9,A9:B11,2,FALSE)))

=IF(ISNA(VLOOKUP(D9,A9:B11,2,0)),0,VLOOKUP(D9,A9:B11,2,0))

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!