Results 1 to 7 of 7

Vlookup to return zero if N/A

This is a discussion on Vlookup to return zero if N/A within the Excel Questions forums, part of the Question Forums category; I can't figure out the if formula combined with my vlookup. I want it to to return a zero if ...

  1. #1
    Board Regular
    Join Date
    Apr 2011
    Posts
    65

    Default Vlookup to return zero if N/A

    I can't figure out the if formula combined with my vlookup. I want it to to return a zero if it normally would return an #n/a as a result of the vlookup.

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,876

    Default Re: Vlookup to return zero if N/A

    Try

    =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))


    Hope that helps.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,093

    Default Re: Vlookup to return zero if N/A

    And if you have Excel 2007 or higher:

    =IFERROR(VLOOKUP(...),0)
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  4. #4
    Board Regular
    Join Date
    Apr 2011
    Posts
    65

    Default Re: Vlookup to return zero if N/A

    Thanks now that you helped me with that and it worked, it makes me think I should use some sort of a count formula. Ultimately, I am wanting to see if the date appears on a worksheet and tell me how many times that date appears. Of course I am not as familiar with Count formulas... I don't suppose you know those?

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,876

    Default Re: Vlookup to return zero if N/A

    That would be COUNTIF

    =COUNTIF(A1:A100,B1)

    B1 = the date to look for
    A1:A100 = the range to count how many times the date appears in.


    Hope that helps.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    Board Regular
    Join Date
    Apr 2011
    Posts
    65

    Default Re: Vlookup to return zero if N/A

    That is why you are an MVP and I am just a novice. Thanks for the help, you rock!

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,876

    Default Re: Vlookup to return zero if N/A

    Glad to help....

    Don't worry, we all were novices at some point or another.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

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