Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: VLOOPUP & #NA -- How to write a formula in VLOOKUP

  1. #1
    New Member
    Join Date
    Jul 2003
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOPUP & #NA -- How to write a formula in VLOOKUP

    Hello Mr Excel
    When I use a VLOOKUP i get some #NA. How can I write the VLOOPUP formula so if "NA" is the output it is defualted to "0"
    Thanks
    K

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

    Welcome to the board.

    =IF(ISNA(your lookup formula),0,your lookup formula)

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    =if(isna(vlookup(.........)),0,vlookup(.........))
    will return 0 if NA
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  4. #4
    New Member
    Join Date
    Jul 2003
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

    It works great !
    Thanks GUYS...

  5. #5
    New Member
    Join Date
    Jul 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

    Not sure if you guys are still on this board, but if so, I wanted to let you know that the solution below just saved my butt! Thanks!

  6. #6
    New Member
    Join Date
    Jul 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

    Just used this one today, dead handy!!!!

  7. #7
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

    Quote Originally Posted by Dave350z View Post
    Just used this one today, dead handy!!!!
    If you're using Excel 2007 or later then you can use the new IFERROR function.

    In older versions this is how you might do it:

    =IF(ISNA(VLOOKUP(A1,C:D,2,0)),"",VLOOKUP(A1,C:D,2,0))

    In Excel 2007 and later that can be reduced to:

    =IFERROR(VLOOKUP(A1,C:D,2,0),"")

    Note that the IFERROR function will trap ALL errors, not just the #N/A errors.
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  8. #8
    New Member
    Join Date
    Feb 2013
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

    To add to the above point....

    If your still on Excel 2003 the IF(ISERROR instead of IF(ISNA will also trap all errors

  9. #9
    New Member
    Join Date
    Aug 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

    Hi,

    IN G13 cell Iam getting N/A

    =VLOOKUP($C13;'data 2013'!$B$5:$DK$150;$G$9;0)

    Which other formula I can use and combined them to get 0 instead of N/A

    Thank you

Some videos you may like

User Tag List

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
  •