• Greetings,

    I have some VLOOKUP formulas that are returning 0's instead of an empty ("") cells in the 'Input' sheet. I can't seem to figure out why. I have duplicated the problem in the attached file, if anyone would like to look at it and maybe let me know what I did wrong. I will gladly answer any question concerning this file.

    I would appreciate any help on this matter.


  • Re: Vlookup

    Hey Alring,

    Thanks for the quick reply.

    You might want to copy Q3 and paste it to Q4:Q10 and see what happens. This is one of the results that I am trying to avoid.

    It was a good try, can you think of any other modifications that might work?

    I look forward to your reply....

  • Re: Vlookup

    hi minitman,

    use IsBlank instead of IsError


    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: Vlookup

    Hi minitman

    Sorry but I can't help you with that. As fare as i can see then you have to live with the 0.



  • Re: Vlookup

    Hey xlite,

    Thanks for the suggestion. However, when I plug it into Q3 it seems to work until I paste it down below the last entry. There I get the error #N/A. I noticed that the problem is not on the 'Input' sheet, but rather what the 'Imput' sheet is getting from the 'Customers' sheet. There is no reason that there should be 0's in the 'Imput' sheet. Yet, there they are. WHY!!!!!

    Does anybody know?

    Your help would be appreciated.


  • Re: Vlookup

    The issue is that you'll get the #N/A if there is no match in column A of your lookup table. You get a Blank returned if column A matches but there is no entry in column N of the lookup table. You probably need to expand your VLOOKUP to cover both possibilities.


  • Re: Vlookup

    Hey thomach,

    Thanks for the reply.

    I found a simpler way just now. The original formula should not have picked up those phantom 0's, but it did. The original formula wored fine until this anomolie showed up. So with that in mind, I thought if the formula ALWAYS returns this 0 for an empty cell, why not check for it! So I came up with this:

    The original formula:


    And the corrected formula:


    It makes for a larger formulam but it works. And if the 0 problem goes away, this will still work. I am still curious as to how it happened in the first place, anyone know?

    Thanks again to all of you who replied. Tho I could not use your solutions, it did get my looking in a different direction and that is when I stumbled across this solution. Again thanks. I could not have done this with out you.

  • Re: Vlookup


    I am still curious as to how it happened in the first place, anyone know?

    I should have been a bit clearer in my explanation. If colomn N has a blank, the blank is returned to the VLOOKUP which reports it as a Zero. Hence the zero returns from your formula.

    You could also have eliminated the zeros by entered a space in the blank column 14 (N) cells, but I hate this practice since one can't easily "see" the spaces and later formula writting can get confused.

    EDIT: Have a look at the attached file. Note the difference in the VLOOKUP return in cell E1 if you place a "c" in cell D1 (that returns a blank from cell B3) verses a "e" in D1 (that returns a space from cell B5).

  • Re: Vlookup



    You may want to consider ,0, int error handler if the rest of the data returned is numeric If you do not like seeing zeroes in your data use a custom number format

    I use ;; - ; for displaying zeroes as a dash
    Enter whatever you use for positive, negative and text values

    If the data is non-numeric then the ,"", error handler is fine.


  • Re: Vlookup

    Hey thomach & markc,

    Thanks for the explainations. I always thought if the lookup cell was empty (""), it would return an empty ("") not a 0. Thanks for clarifiying.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!