# How do I display 0 instead of #NA after a vlookup

This is a discussion on How do I display 0 instead of #NA after a vlookup within the Excel Questions forums, part of the Question Forums category; I have a formulae on excel using if &amp; vlookup to find a value if a payment was taken on ...

1. ## How do I display 0 instead of #NA after a vlookup

I have a formulae on excel using if & vlookup to find a value if a payment was taken on a certain date, the formulae is working, however I want to edit it so that instead of returning #NA if there isn't a result, it returns 0 - does anyone know how to edit it to do this? The formulae as it is currently is below:

=IF(F3=40379,VLOOKUP(A2,'Payments'!\$A\$2:\$V\$2495,7,FALSE),0)

2. ## Re: How do I display 0 instead of #NA after a vlookup

Try:

=IF(F3=40379,IF(COUNTIF('Payments'!\$A\$2:\$A\$2495,A2),VLOOKUP(A2,'Payments'!\$A\$2:\$V\$2495,7,FALSE),0),0)

3. ## Re: How do I display 0 instead of #NA after a vlookup

Originally Posted by Amz
I have a formulae on excel using if & vlookup to find a value if a payment was taken on a certain date, the formulae is working, however I want to edit it so that instead of returning #NA if there isn't a result, it returns 0 - does anyone know how to edit it to do this? The formulae as it is currently is below:

=IF(F3=40379,VLOOKUP(A2,'Payments'!\$A\$2:\$V\$2495,7,FALSE),0)
Try one of...
Code:
```=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
IF(F3=40379,VLOOKUP(A2,'Payments'!\$A\$2:\$V\$2495,7,0),0)))```
Code:
`=IF(F3=40379,SUMIF('Payments'!\$A\$2:\$A\$2495,A2,'Payments'!\$G\$2:\$G\$2495),0)`

4. ## Re: How do I display 0 instead of #NA after a vlookup

Originally Posted by Amz
I have a formulae on excel using if & vlookup to find a value if a payment was taken on a certain date, the formulae is working, however I want to edit it so that instead of returning #NA if there isn't a result, it returns 0 - does anyone know how to edit it to do this? The formulae as it is currently is below:

=IF(F3=40379,VLOOKUP(A2,'Payments'!\$A\$2:\$V\$2495,7,FALSE),0)
Me I would use

=IF(F3=40379,IF(ISNA(VLOOKUP(A2,Payments!\$A\$2:\$V\$2495,7,FALSE)),0,VLOOKUP(A2,Payments!\$A\$2:\$V\$2495,7,FALSE)))

#### Posting Permissions

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