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

    Thank you for your help
    CYA30, Aug 29, 2013
    1. Advertisements

  2. CYA30

    joeu2004 Guest

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


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




    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:


    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
    1. Advertisements

Ask a Question

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.