Results 1 to 4 of 4

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. #1
    New Member
    Join Date
    Jun 2010
    Posts
    8

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

    any help please?

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,905

    Default 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. #3
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default 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))
    Yesterday I felt on top of the world. Today its falling in on me.

  4. #4
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default 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
    Last edited by bosco_yip; Jul 7th, 2010 at 05:26 AM.

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