Vlookup to return 0 instead of NA

Discussion in 'Excel Beginners' started by CYA30, Aug 29, 2013.

  1. CYA30

    CYA30 Guest

    I am hoping someone can help me....

    I am currently building out a spreadsheet and have successfully (
    hope!) used this formula
    =VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE). It's returning th
    numbers that I need. What I would like though is for it to return a
    instead of N/A when the information is not available. I am using th
    results in calculations and really need it to return either a 0 or b
    blank.

    Thank you for your help


    --
    CYA30
     
    CYA30, Aug 29, 2013
    #1
    1. Advertisements

  2. CYA30

    joeu2004 Guest

    "CYA30" <> wrote:
    > I am currently building out a spreadsheet and have successfully
    > (I hope!) used this formula
    > =VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE). It's returning
    > the numbers that I need. What I would like though is for it to
    > return a 0 instead of N/A when the information is not available.


    If you are using Excel 2007 or later and you do not require compatibility
    with Excel 2003 or ealier, you can write:

    =IFERROR(VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE),0)

    If you require compatibility with Excel 2003 or earlier, ostensibly you must
    write:

    =IF(ISNA(VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE)),0,
    VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE))

    or

    =IF(ISNA(MATCH(MROUND($D$13,2),Sheet2!$A$1:$A$353,1)),0,
    VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE))

    However, note that VLOOKUP(...,TRUE) returns #N/A only when MROUND(D13,2) is
    less than Sheet2!A1. I wonder if you really want VLOOKUP(...,FALSE). See
    the Help page for the difference. But if you truly want VLOOKUP(...,TRUE),
    you could write:

    IF(MROUND($D$13,2)<Sheet2!$A$1,0,
    VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE))

    PS: I suspect you can write MROUND(D13,2) instead of MROUND($D$13,2). In
    fact, MROUND(D13,2) might be preferred, depending on if/how you might copy
    the formula.
     
    joeu2004, Aug 30, 2013
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. BizzyByers
    Replies:
    0
    Views:
    102
    BizzyByers
    Jan 16, 2004
  2. Tim
    Replies:
    1
    Views:
    131
    Wouter HM
    Feb 16, 2004
  3. Ken Isaacson

    Keyboard Nav scrolls instead of next cell

    Ken Isaacson, Feb 19, 2004, in forum: Excel Beginners
    Replies:
    1
    Views:
    78
    Frank Kabel
    Feb 19, 2004
  4. Mo
    Replies:
    1
    Views:
    116
    Frank Kabel
    Aug 7, 2004
  5. Gazivaldo

    An Error return other than #N/A with VLOOKUP

    Gazivaldo, Nov 18, 2005, in forum: Excel Beginners
    Replies:
    2
    Views:
    63
    Gazivaldo
    Nov 22, 2005
  6. MikeR-Oz
    Replies:
    1
    Views:
    92
    Clivey_UK
    Mar 22, 2006
  7. Hugh self taught

    vlookup return value from 2 columns

    Hugh self taught, Aug 18, 2009, in forum: Excel Beginners
    Replies:
    4
    Views:
    118
    Hugh self taught
    Aug 18, 2009
  8. charles
    Replies:
    3
    Views:
    106
    Ed Ferrero
    Oct 1, 2009
Loading...