Vlookup return 0 instead of N/A

Posted on 2012-09-15
Last Modified: 2012-09-15
When I copy my vlookup formula down I get either a number or N/A. Instead of the N/A I want a formula that would return a 0. My vlookup formula is below.


I found this formula below on a website...and I tried to modify it for my use but it did not work.

=IF(COUNTIF($B$2:$B$100,A1), VLOOKUP(A1,$B$2:$D$100,3,0),””) (this is not my modified version)

Any help would be appreciated.

Question by:Edward Pamias
    LVL 25

    Accepted Solution

    use the isna function, e.g.
    LVL 70

    Expert Comment

    the formula below returns 0 if A3 is blank - is this what you want?

    LVL 26

    Expert Comment

    Hi, epamias.


    This has...
    ... the advantage that the data is searched only once. (Trivial in this case, but often significant)
    ... the disadvantage that IFERROR didn't exist before Excel 2007.

    LVL 14

    Author Closing Comment

    by:Edward Pamias
    Thank you! This one worked. The last formula worked as well. Sorry I did not see that until after I accepted the ISNA formula.
    LVL 26

    Expert Comment

    Not a problem, epamias. Glad you're sorted.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now