# How to change #NA to 0 in Vlookup

This is a discussion on How to change #NA to 0 in Vlookup within the Excel Questions forums, part of the Question Forums category; I have tried many ways of returning a 0 instead of a #NA in my formula but i cannot seem ...

1. ## How to change #NA to 0 in Vlookup

I have tried many ways of returning a 0 instead of a #NA in my formula but i cannot seem to get it to work.

this is what i have tried so far:

=IF(ISNA(VLOOKUP(A3,Sheet2!\$A\$3:\$C\$65536,2,0)),0,VLOOKUP(A3,Sheet2!\$A\$3:\$C\$65536,2,0)

=IF(ISNA(VLOOKUP(A3,Sheet2!\$A\$3:\$C\$65536,2,0)),"",(VLOOKUP(A3,Sheet2!\$A\$3:\$C\$65536,2,0)

2. ## Re: How to change #NA to 0 in Vlookup

This seems to work...
Code:
`=IF(ISNA(VLOOKUP(A3,Sheet2!\$A\$3:\$C\$65536,2,0)),0,VLOOKUP(A3,Sheet2!\$A\$3:\$C\$65536,2,0))`

3. ## Re: How to change #NA to 0 in Vlookup

This works for me

=IF(ISNA(VLOOKUP(B1,A:A,1,FALSE)),0,VLOOKUP(B1,A:A,1,FALSE))

4. ## Re: How to change #NA to 0 in Vlookup

Or one of…….

=IF(ISNUMBER(MATCH(A3,Sheet2!A:A,0)),VLOOKUP(A3,Sheet2!\$A:\$C,2,0),0)

=IF(COUNTIF(Sheet2!A:A,A3),VLOOKUP(A3,Sheet2!\$A:\$C,2,0),0)

Regards
Bosco

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•