+ Reply to Thread
Results 1 to 21 of 21

issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

  1. #1
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    it seems there's a bug in excel - as i've mentioned in the title...
    at least - for my attached excel
    please help me debug / understand what's going on

    many thanks!vlookup and match problem with source as defined name around row 32000....zip

    ps i'm using excel 2003, on windows 7 64 bit
    Last edited by randor; 05-02-2014 at 09:07 AM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: bug in match/vlookup when search range is defined name, at around the 32000th item...

    Could you explain exactly what it is you're trying to accomplish? It's not clear what the "bug" actually is. I'm guessing it's a returned result that you weren't expecting, but without a little more of an explanation, it's not exactly clear.

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: bug in match/vlookup when search range is defined name, at around the 32000th item...

    No bug.

    You are using a VLOOKUP with a parameter of TRUE, hence the range must be sorted in ascending order.

    Unfortunately, with text values, what you think may be correctly sorted is not necessarily the case. For example, according to your list, it seems that you believe that:

    41722_10.789

    should come between:

    41722_9.786 and 41723_0.022

    whereas Excel believes that it is actually between:

    41722_1.786 and 41722_2.033

    I tend to agree with Excel.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: bug in match/vlookup when search range is defined name, at around the 32000th item...

    it's easiest to simply look at the excel...
    its hard to explain in words
    but i will try :
    but 1stly, i have narrowed the issue down a bit further: it happens also if the search range is not a defined name.
    and i will resend the excel, because i am actually more interested in the issue for the MATCH function.

    my source range is "ALMOST ascendingly sorted". i use the final argument "1" for the match function.
    so in sections where the source range is ascending, i expect it to give the right result, and in sections that it suddenly descends, i understand that it can "fail"

    but, i see some "failures" , on a seemingly random frequency, in sections where as i've described, i expect it to succeed

  5. #5
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: bug in match/vlookup when search range is defined name, at around the 32000th item...

    i have found a work around in the meantime by succeeding to make lookup range fully alphabetical

    but i still think theres something up with excel
    i will send an excel file to demonstrate...

  6. #6
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: bug in match/vlookup when search range is defined name, at around the 32000th item...

    Quote Originally Posted by XOR LX View Post
    No bug.

    You are using a VLOOKUP with a parameter of TRUE, hence the range must be sorted in ascending order.

    Unfortunately, with text values, what you think may be correctly sorted is not necessarily the case. For example, according to your list, it seems that you believe that:

    41722_10.789

    should come between:

    41722_9.786 and 41723_0.022

    whereas Excel believes that it is actually between:

    41722_1.786 and 41722_2.033

    I tend to agree with Excel.

    Regards


    here is an example where it doesnt make sense, and of the inconsistency:
    =INDEX(aaa,MATCH("40680_3.381",aaa,1))) . this comes up with 40680_10.644
    yet the value being searched for appears exactly in the series (i've named as "aaa")


    40679_3.132=INDEX(aaa,MATCH("40679_3.132",aaa,1)) this one is good
    40680_10.644=INDEX(aaa,MATCH("40680_3.129",aaa,1)) why doesnt this one work ???
    40681_3.126=INDEX(aaa,MATCH("40681_3.126",aaa,1)) this one is good

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: issue in match/vlookup, at seemingly random items , for ALMOST sorted range

    As I've already said, you cannot hope to obtain correct results using VLOOKUP with a TRUE parameter if your data is not sorted in ascending order.

    It doesn't matter if your search string is in the range or not - it still may not give correct results. Unfortunately Excel is not sympathetic to an "almost ascendingly sorted" range, as you say.

    And as for you "expecting it to succeed" in certain cases, I'm afraid there is little chance of giving correct results in some sections and incorrect ones in others. It simply doesn't work like that.

    Regards

  8. #8
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: issue in match/vlookup, at seemingly random items , for ALMOST sorted range

    note that if you run the above test for the whole range , it very RARELY "fails" as i've described.

    And , why does the function accept a range that is not 100% sorted ? As the function does accept such a range , and does succeed in the vast majority of tests for this range , this does seem to be a function failure to me!

  9. #9
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: issue in match/vlookup, at seemingly random items , for ALMOST sorted range

    in fact, now i would say more certainly that it is a BUG
    because as the range is not sorted, the function should always give #NA

    (as per the help file of microsoft:

    =MATCH(40,B2:B5,-1) Returns an error because the range B2:B5 is not in descending order. (#N/A) )

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    MATCH with a match_type of -1 (or 1) and VLOOKUP with a range_lookup of TRUE (or 1) are not at all the same thing.

    Regards

  11. #11
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    as regards MATCH , do you agree that the function should return #NA if it's given a unsorted range, and an argument of +1 ? (in which case the behaviour is buggy)

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    Dude....

    It's not a bug.

    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  13. #13
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    Quote Originally Posted by randor View Post
    as regards MATCH , do you agree that the function should return #NA if it's given a unsorted range, and an argument of +1 ? (in which case the behaviour is buggy)
    It's an interesting question. One one hand, I could see the value in having Excel perform this "error check" for us. On another hand, I could see how, with a large data set and many lookups, this would create a lot of extra (and maybe unnecessary) overhead in the performance of the lookup function.

    Here's my take on the issue. I find that it really helps to think of spreadsheets as a programming language and to consider yourself as a programmer. You have "discovered" an untrapped error in one of the spreadsheets built in functions. Your job as a programmer is to figure out how you want to handle the error.

    IMO, it is better to have each spreadsheet programmer figure out on a case by case basis if and how he/she wants to trap this error, rather than have MS's programmers anticipate every possible scenario where this error might exist and trap it for us.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  14. #14
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    such misleading and ambiguous behaviour is a bug in my book ...

    it looks as though the function was written to be able to handle such "almost-sorted" sequences - due to the fact that it almost always succeeds.

    eg maybe it was designed to work only in regions that are sorted
    it would be interesting to hear from someone that has investigated the limits of the functionality of this function

    thanks very much to all your replies
    Last edited by randor; 05-02-2014 at 11:02 AM.

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    Help tells you the array needs to be in ascending order to work; just to repeat what others have said, 'in ascending order' is a binary variable; it either is or it isn't, and yours isn't.

    The answers you're getting are the result of a binary search, which absolutely, positively, no-kidding, requires a sorted array.
    Last edited by shg; 05-02-2014 at 05:48 PM.
    Entia non sunt multiplicanda sine necessitate

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    VLOOKUP using the range_lookup argument set to TRUE (or 1 or omitted altogether) is designed to work with numeric data that falls into bins. This is why the data has to be sorted. You can still use it with textual data but the data still needs to be sorted.

    An example is a letter grading system. You have a test with 100 questions. The grading levels are based on the total number of correct answers. The grade levels are:

    0 to 50 correct answers = letter grade F
    51 to 70 correct answers = letter grade D
    71 to 80 correct answers = letter grade C
    81 to 90 correct answers = letter grade B
    91 to 100 correct answers = letter grade A

    We could setup a table like this:

    Data Range
    A
    B
    C
    1
    From
    To
    Grade
    2
    0
    50
    F
    3
    51
    70
    D
    4
    71
    80
    C
    5
    81
    90
    B
    6
    91
    100
    A


    We actually only need column A and C but I include B just to make it easier to see the levels.

    If a student had 79 correct answers then we could use any one of these formulas to get the corresponding letter grade:

    =VLOOKUP(79,A2:C6,3,TRUE)
    =VLOOKUP(79,A2:C6,3,1)
    =VLOOKUP(79,A2:C6,3)
    =LOOKUP(79,A2:C6)

    If the table is not sorted in ascending order on the leftmost column it's possible that you might luck out and get a correct result but it would be luck and nothing more.

    It's not a bug!

  17. #17
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    Quote Originally Posted by Tony Valko View Post
    If the table is not sorted in ascending order on the leftmost column it's possible that you might luck out and get a correct result but it would be luck and nothing more.
    it's not luck when you have 99% success rate. that is the whole point here. the failures (in regions that are 'locally sorted') are very RARE - if they always occurred, then that would clearly be by design. the question is , given that they occur on such a rare basis - why is that ?

    thanks for your very nicely presented example , if you could extend it to be more relevent - ie for a series that is like mine - almost perfectly sorted , then that could help shed some light on this

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    Last edited by Tony Valko; 05-03-2014 at 09:31 AM.

  19. #19
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    it's not luck when you have 99% success rate.
    There's no element of luck at all. At every comparison in a binary search, one of three things happen: The search ends on an exact match, or the search continues in the remaining segment of the array below, or the search continues in the remaining segment of the array above. If the comparison is made to a mis-sorted element, then it's possible that the posse goes north after a fugitive that went south.

    If the comparison never tests against a mis-sorted value, you get the right answer. If it does, maybe you do and maybe you don't.

    Perhaps you could write a UDF extends the functionality of the [match type] argument to include +/-0.5 for "sorta sorted ascending" and "sorta sorted descending."
    Last edited by shg; 05-03-2014 at 03:34 PM.

  20. #20
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    This would have been a good candidate for the Ask Me Anything event!
    Last edited by Tony Valko; 05-03-2014 at 04:09 PM. Reason: I don't know how to spell "this"!

  21. #21
    Registered User
    Join Date
    11-22-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: issue/BUG in match/vlookup, at seemingly random items , for ALMOST sorted range

    interesting...

    i suppose to understand this any deeper, will have to read up on how such algorithms work, but your (shg) explanation makes sense.

    PS this fromWikipedia...
    http://en.wikipedia.org/wiki/Binary_search_algorithm

    Although the basic idea of binary search is comparatively straightforward, the details can be surprisingly tricky… — Donald Knuth[7]

    When Jon Bentley assigned it as a problem in a course for professional programmers, he found that an astounding ninety percent failed to code a binary search correctly after several hours of working on it,[8] and another study shows that accurate code for it is only found in five out of twenty textbooks.[9] Furthermore, Bentley's own implementation of binary search, published in his 1986 book Programming Pearls, contains an error that remained undetected for over twenty years.[10]




    as regards checking for being perfectly ordered, i concede that function speed in excel is WAY more important than doing this check

    thanks vm to all responses, case closed , no bug!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to search a range and find a specfic item based on a condition.
    By Cornered99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2013, 05:10 PM
  2. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  3. Replies: 4
    Last Post: 03-30-2012, 07:55 AM
  4. [SOLVED] Search Range for item in seperate range
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-09-2006, 12:00 PM
  5. [SOLVED] Defined Name Range (Application.Match)
    By PCLIVE in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-20-2005, 10:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1