+ Reply to Thread
Results 1 to 14 of 14

vlookup returning 0 value when told not to

  1. 10-19-2010, 11:49 AM #1
    Oly Steel Man
    Oly Steel Man is offline
    Registered User
    Join Date
    07-28-2010
    Location
    Chambersburg, PA, USA
    MS-Off Ver
    Excel 2010
    Posts
    39

    vlookup returning 0 value when told not to

    I've had this problem for sometime and while its not hindering me right now it does create a bit of visual confusion on the screen and when trying to distribute a report to co-workers. I've have a sheet that I use the vlookup function on and when told to display nothing in the event of an isna value it will display a 0 instead. I've attached a copy of the sheet so you can dissect as necessary. Again it references properly when data is present but not when there is an absence of data. Use column D as a reference for the error, cell D14 is the first instance of this error. I've tried different formats for the cells as well.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-19-2010, 11:56 AM #2
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: vlookup returning 0 value when told not to

    You don't have any NA on the Packet Schedule sheet, so the ISNA function is always going to return False. Instead test for blank like this:

    Please Login or Register  to view this content.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave
    Reply With QuoteRegister To Reply
  3. 10-19-2010, 11:59 AM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: vlookup returning 0 value when told not to

    Your zeros are not coming from the ISNA portion of your equation but from the vlookup when you find something in column A (the id is on your packet schedule) but there's nothing to return in column 3. You'll need to nest a second IF statement to return "" if VLOOKUP returns 0 or set up your options to not show zeros. Hope that helps.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 10-19-2010, 11:59 AM #4
    ron2k_1
    ron2k_1 is offline
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: vlookup returning 0 value when told not to

    The prob is not in the formula, it is because on you packet schedule for items 759142 and 759144 and many others you have nothing under column 3 (or C) as instructed by your vlookup formula, so you need to enter something in that column so that it returns something else other than 0, or an addtional IF argument.

    EDIT: Hey! I'm late for the party
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
    Reply With QuoteRegister To Reply
  5. 10-19-2010, 12:01 PM #5
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: vlookup returning 0 value when told not to

    If you highlight the vlookup portion of your formula and hit F9 you'll see that it equates to 0 so the ISNA will not kick in. To allow for a 0 value use:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 10-19-2010, 12:11 PM #6
    Oly Steel Man
    Oly Steel Man is offline
    Registered User
    Join Date
    07-28-2010
    Location
    Chambersburg, PA, USA
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: vlookup returning 0 value when told not to

    Quote Originally Posted by davegugg View Post
    You don't have any NA on the Packet Schedule sheet, so the ISNA function is always going to return False. Instead test for blank like this:

    Please Login or Register  to view this content.
    This would work so long as I have a value in the lookup column (A). It does return an N/A value in the other cells where there is no lookup value. That's why I specified the isna portion. I suppose I'll have to just auto fill the formula if I have values in column A and leave the others blank until I have data.
    Reply With QuoteRegister To Reply
  7. 10-19-2010, 12:22 PM #7
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: vlookup returning 0 value when told not to

    Look at Cutter's response, he fixed my oversight on that issue.
    Reply With QuoteRegister To Reply
  8. 10-19-2010, 12:22 PM #8
    Oly Steel Man
    Oly Steel Man is offline
    Registered User
    Join Date
    07-28-2010
    Location
    Chambersburg, PA, USA
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: vlookup returning 0 value when told not to 

    Please Login or Register  to view this content.
    Crude but this did the trick. Any help on cleaning it up?
    Reply With QuoteRegister To Reply
  9. 10-19-2010, 12:30 PM #9
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: vlookup returning 0 value when told not to

    Since none of the values in the searched column are less than 4 characters would it be safe to assume that will always be the case?

    If so you could use:

    Please Login or Register  to view this content.
    That will take care of 0's or blanks
    Reply With QuoteRegister To Reply
  10. 10-19-2010, 12:41 PM #10
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: vlookup returning 0 value when told not to

    You could just use your original formula with &"" appended to the second VLOOKUP, that will suppress the zeroes, i.e.

    =IF(ISNA(VLOOKUP($A2,'Packet Schedule'!$A$2:$E$599,5,FALSE)),"",VLOOKUP($A2,'Packet Schedule'!$A$2:$E$599,5,FALSE)&"")
    Audere est facere
    Reply With QuoteRegister To Reply
  11. 10-19-2010, 01:02 PM #11
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: vlookup returning 0 value when told not to

    Hey, that's a cool trick. Why does that work?
    Reply With QuoteRegister To Reply
  12. 10-19-2010, 01:19 PM #12
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,690

    Thumbs up Re: vlookup returning 0 value when told not to

    Quote Originally Posted by Cutter View Post
    Hey, that's a cool trick. Why does that work?
    A reference to a blank cell will show a 0. If you append the null string to the end of the reference it forces it to be interpreted as text, and therefore will give a blank.

    Neat trick.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  13. 10-19-2010, 01:23 PM #13
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: vlookup returning 0 value when told not to

    If whatever's being returned is Text then another alternative to double evaluation:

    Please Login or Register  to view this content.
    that said the above is flawed if the string being returned exceeds 255 chars
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  14. 10-19-2010, 01:30 PM #14
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: vlookup returning 0 value when told not to

    So appending "" to a number to get results in text is the same (but reverse) logic as adding 0 (or multiplying by 1) to text to get a number.

    Very slick. Will put it in memory bank for future use.
    Thanks daddylonglegs and 6StringJazzer!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1