Excel crashes when typing "false" in VLookup function

Discussion in 'Microsoft Excel Worksheet Functions' started by pcbins, Jan 29, 2009.

  1. pcbins

    pcbins Guest

    While typing a vlookup function such as "=vlookup(A2,'Sheet2'!A:A,false)",
    immediately after typing the "f" in "false", Excel will crash with the
    following error:

    Identify Label
    There is more than one cell with this label:f
    Select the cell containing the label to use:
    [OK] [Cancel]

    No matter what is entered following, the following error will occur:

    Microsoft Office Excel has encountered a problem and needs to close.
    We are sorry for the inconvenience.

    This problem has been around for almost 5 years now. Is there a hotfix for
    it yet? Or should I give MS another 5 years to work on it?
     
    pcbins, Jan 29, 2009
    #1
    1. Advertisements

  2. This may help as a workaround--but it's not a solution.

    (xl2003 menus)
    Tools|Options|Calculation tab|uncheck "Accept labels in formulas"

    Maybe you'll be ok????
     
    Dave Peterson, Jan 29, 2009
    #2
    1. Advertisements

  3. pcbins

    pcbins Guest

    I'm not sure why everyone thinks this will fix it, but, as I tell them, this
    option is not checked. I have tried it both ways, restarting excel after each
    change. And it makes no difference...


     
    pcbins, Jan 29, 2009
    #3
  4. pcbins

    Guest

    This formula has incomplete parameters. The Excel 2000 help file (F1)
    defines VLOOKUP funcation as:

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    You are placing a boolean value (e.g. FALSE) where an integer value
    needs to be. To wit, you are placing your desired "range_lookup"
    optional value in the required "col_index_num" parameter. While FALSE
    can loosely be defined as zero (0) and TRUE as anything NOT FALSE, it
    generally isn't good practice to substitute a boolean for an integer
    and especially in your case as there is no ordinal column number zero
    (0) in a range. In your example perhaps a more appropriate formula
    would be:

    =VLOOKUP(A2,'Sheet2'!A:A,1,FALSE)

    HTHs
     
    , Jan 29, 2009
    #4
  5. pcbins

    pcbins Guest

    Sorry, that was a typo... I've been using this formula for years and years
    and years... probably typed it out a thousand times or more...

    I know how to use the formula. Please ignore the typo above and focus on the
    problem...
     
    pcbins, Jan 29, 2009
    #5
  6. Since you multiposted the same question in different places, how would any one
    know that you had gotten this response elsewhere?

    Good luck.
     
    Dave Peterson, Jan 29, 2009
    #6
  7. Hi,

    You table array is the entire column A (that is 65,536 rows). Please reduce
    the range to where your data is till and then see whether Excel still
    crashes.

    --
    Regards,

    Ashish Mathur
    Microsoft Excel MVP
    www.ashishmathur.com
     
    Ashish Mathur, Jan 30, 2009
    #7
  8. pcbins

    pcbins Guest

    No, it doesnt matter what size the array is. It is rarely the entire column.
    RARELY EVER. It can be 500 rows or 5000 or 50000. It makes no difference.

    PLEASE ADDRESS THE PROBLEM, MICROSOFT, and provide a fix. I think 5 years is
    long enough to have researched it.

     
    pcbins, Jan 30, 2009
    #8
  9. pcbins

    pcbins Guest

    Nevermind, I think I will repost without an example so you can focus on the
    real problem...

     
    pcbins, Jan 30, 2009
    #9
  10. pcbins

    Glenn Guest

    Does this happen in any worksheet you try it in, or only a specific worksheet?
    I can't duplicate the problem.

    By the way, you may have mistakenly assumed that you are talking to MICROSOFT
    EMPLOYEES...the people here, even the MVP's, are volunteers, not generally
    affiliated with Microsoft.
     
    Glenn, Jan 30, 2009
    #10
  11. pcbins

    JE McGimpsey Guest

    These groups are peer-newsgroups, not official channels for MS. Nobody
    here can fix your problem, assuming it exists.

    Frankly, given that it, as far as I can tell, has never come up in these
    groups before, I suspect it's something unique to your system.
    Otherwise, there would have been an outcry from millions of users who
    use VLOOKUP.
     
    JE McGimpsey, Jan 30, 2009
    #11
  12. pcbins

    pcbins Guest

    try Googling it... I accidentally found this "lets help each other since
    microsoft won't" forum... millions are out there not knowing where to go for
    help when microsoft ignores them...
     
    pcbins, Jan 30, 2009
    #12
  13. pcbins

    JE McGimpsey Guest

    I had checked Google groups, and I've just googled ("VLOOKUP crash
    FALSE"). Prior to this month, there's nothing relevant back through 2003.

    I don't want to be an MS apologist, but if there were really millions of
    people having this problem, we would have heard about it here.

    That doesn't mean that the problem isn't real - just not very common.
     
    JE McGimpsey, Jan 30, 2009
    #13
  14. pcbins

    Glenn Guest


    http://tinyurl.com/bbq9kn
     
    Glenn, Jan 30, 2009
    #14
  15. pcbins

    pcbins Guest

    oh my goodness! you are kidding me? Are you sure you are using Google?? I get
    pages and pages...
     
    pcbins, Jan 30, 2009
    #15
  16. pcbins

    JE McGimpsey Guest

    JE McGimpsey, Jan 30, 2009
    #16
  17. pcbins

    JE McGimpsey Guest

    I get pages and pages of results for that search, but only a few that
    mention crashing when typing the 'f' in False - and at least one was
    XL02, not X03.

    I'm not really interested in continuing the search, and I'm not trying
    to be a net nanny. It's obviously a real problem for at least a few
    people, but AFAIK, there's been no solutions posted anywhere.

    If someone happens to have a solution, I'm sure they'll post it.
     
    JE McGimpsey, Jan 30, 2009
    #17
  18. pcbins

    Glenn Guest


    The only "solution" posted seems to have been to use a zero instead of typing
    "false".
     
    Glenn, Jan 30, 2009
    #18
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.