Results 1 to 4 of 4

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 & vlookup to find a value if a payment was taken on ...

  1. #1
    Amz
    Amz is offline
    New Member
    Join Date
    Sep 2010
    Posts
    3

    Exclamation 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. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,334

    Default 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)
    Microsoft MVP - Excel

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    66,250

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

    Quote Originally Posted by Amz View Post
    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)
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Dec 2007
    Posts
    11

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

    Quote Originally Posted by Amz View Post
    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)))

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com