Vlookup

    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.


    TIA

    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


    HTH

    Cheers
    ___________
    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.


    Sorry


    regards
    Alring

    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.


    TIA

    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.


    =IF(ISBLANK(),"",(IF(ISNA(),"",....)))

    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:

    Code
    =IF(ISERROR(VLOOKUP($I3,RefList2,14,FALSE)),"",(VLOOKUP($I3,RefList2,14,FALSE)))


    And the corrected formula:

    Code
    =IF(IF(ISERROR(VLOOKUP($I3,RefList2,14,FALSE)),"",(VLOOKUP($I3,RefList2,14,FALSE)))=0,"",IF(ISERROR(VLOOKUP($I3,RefList2,14,FALSE)),"",(VLOOKUP($I3,RefList2,14,FALSE))))

    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


    Quote

    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


    Quote

    =If(If(ISERROR(VLOOKUP($I3,RefList2,14,False)),"",(VLOOKUP($I3,RefList2,14,False)))=0,"",


    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.


    HTH
    -marc

    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!

Register Yourself Login