+ Reply to Thread
Results 1 to 29 of 29

How does this lookup work?

  1. #1
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Question How does this lookup work?

    In a recently solved post, daddylonglegs gave the following code as a solution:

    Please Login or Register  to view this content.
    I understand Replace, but I don't understand the Lookup function. Could someone describe to me how this works?

    Specifically, I'm curious how this works to solve the problem specified in the solved post.
    Last edited by starryknight64; 06-16-2009 at 12:02 PM.
    starryknight64

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    I'm sure dll will revisit but in short

    A1: abc/def/ghi

    B1: =REPLACE(A1,1,LOOKUP(2^15,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")

    the LOOKUP is used in this context to return the last position in which the / appears within the string of interest...

    it does this by creating an array of values using FIND in A1 but incrementing the start position from 1 to the len of the string - done via the use of ROW(INDIRECT("1:"&LEN(A1))) ... so for a string of 11 characters you get:

    {1;2;3;4;5;6;7;8;9;10;11}

    with B1 active, highlight the FIND section of the formula in the formula bar and press F9... here you will see the various outputs given the incrementing start position:

    {4;4;4;4;8;8;8;8;#VALUE!;#VALUE!;#VALUE!}

    so you can see that when you start at position one the FIND function finds / in position 4 in the string, this is constant until the start position for FIND becomes 5 at which point the next / is found in position 8... once you get past position 8 FIND can no longer find a / in the string hence #VALUE!

    LOOKUP will ignore data types not consistent with the criteria so in this instance the criteria is a number (2^15) as such the #VALUE! errors are ignored. By setting the criteria to be > any number in the resulting array of values LOOKUP will by default return the last number found... in this case 8.

    So the REPLACE then simply states replace the first 8 characters with nothing.

    For more info on LOOKUP do a search here - particularly in relation to Binary Search or Find Last Number etc... where you may get detailed explanations.
    Last edited by DonkeyOte; 06-16-2009 at 11:04 AM. Reason: changed , to ; in array example

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How does this lookup work?

    Nice explanation Luke, not much more to add, except to say that I first saw a version of this formula posted by Harlan Grove in the Microsoft newsgroups......

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    Thanks ?????

    I am curious though to know your opinion in regard to whether or not the REPLACE method is preferable to the old fashioned:

    =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

    which though less elegant is not volatile... presumably REPLACE for the odd function here and there ?

  5. #5
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Re: How does this lookup work?

    Quote Originally Posted by DonkeyOte View Post
    ...
    LOOKUP will ignore data types not consistent with the criteria so in this instance the criteria is a number (2^15) as such the #VALUE! errors are ignored. By setting the criteria to be > any number in the resulting array of values LOOKUP will by default return the last number found... in this case 8.
    So, the 2^15 is simply a "really large number" so that the LOOKUP will return the largest number it finds? Or is it really the last number it finds?

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

    Re: How does this lookup work?

    2^15 = 32768 = 1 + 32767 = the most text that can be stored in a cell. Any number >= 32767 would work, and a constant would be better.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How does this lookup work?

    Quote Originally Posted by starryknight64 View Post
    So, the 2^15 is simply a "really large number" so that the LOOKUP will return the largest number it finds? Or is it really the last number it finds?
    The LOOKUP returns the last number here [in this type of formula it's also the largest]. It's really a trick. Because the lookup array should be sorted excel assumes that the largest value is the last and returns that one (even if it isn't the largest)

    2^15 is used specifically here because FIND won't return a number higher than 2^15-1, i.e. 32767, because that's the maximum number of characters allowed in a cell

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    It's a really large number... and basically ensures that whatever the values in the lookup_vector they will be less than the criteria and this ensures we will get the last value of the same type as the criteria returned to us... by means of binary search, this approach is more commonly seen when trying to find the last number in a range, eg

    Please Login or Register  to view this content.
    =LOOKUP(9.99999999999999E+307,A1:A10)

    will yield 7... last numeric type in the lookup_vector ...

    I'm not even going to try and explain Binary Search as it's beyond me to do so however Aladin Akyurek provided a quick graphical representation here

  9. #9
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Re: How does this lookup work?

    No its cool, I know and understand binary search just fine.

    Thanks for the answers, everyone. This will likely come in handy later.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    Quote Originally Posted by daddylonglegs View Post
    Because the lookup array should be sorted excel assumes that the largest value is the last and returns that one (even if it isn't the largest)
    Nice dll, I think that's the simplest and most succinct way I've seen that explained...

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How does this lookup work?

    Quote Originally Posted by DonkeyOte View Post
    Thanks ?????

    I am curious though to know your opinion in regard to whether or not the REPLACE method is preferable to the old fashioned:

    =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

    which though less elegant is not volatile... presumably REPLACE for the odd function here and there ?
    I see Domenic suggested a variation of that formula in the original thread. Domenic nearly always tries to suggest the most efficienct option so I assume he feels it's better in that regard, I would assume so too.

    Of course how much you care about efficiency probably depends on how much and what sort of data you have.

    Efficiency considerations aside the above formula might fail, of course, if there are "~" characters in A1

  12. #12
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Re: How does this lookup work?

    What about my reply in the solved post?

    Would reversing the string and simply doing a RIGHT on the index of the first found (using FIND) be more efficient? Or is there overhead involved in looking up a macro definition?

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How does this lookup work?

    I'm really a "formula dude"

    In this case formula v VBA, I don't know?

    DonkeyOte, shg?

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    I defer to Charles Williams

    http://www.decisionmodels.com/calcsecretsj.htm
    (UDF performance section in the link but all worthy of a read)

    dll, as we know from experience on other threads I'm certainly not anti UDF but I would only code one myself if I could not achieve the same thing relatively easily using native functions.

  15. #15
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Re: How does this lookup work?

    I did a side-by-side eye-comparison of the three algorithms calculated 16000 times:

    1. =REPLACE(A1,1,LOOKUP(2^15,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")
    2. =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))
    3. =RIGHT(A1,FIND("/",reverse(A1))-1)

    And only found a slight noticeable delay in the third algorithm.

    So simpler doesn't always necessarily mean "faster."

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

    Re: How does this lookup work?

    I'd go with #3 -- brevity and clarity, IMO, are beneficial in maintaining workbooks long after your bout of cleverness in creating them has subsided. I hate the feeling of looking back at a workbook a few years old and thinking, "HTF does that work?"

    Buy on the other hand, some environments don't allow macros, and people do remarkably clever stuff with formulas.

    But on the third hand, not having a StrReverse worksheet function should have been redressed a long time ago. It took Microsoft 15(?) years to add an IFERROR function.

  17. #17
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Re: How does this lookup work?

    Just to confirm: There is a Reverse function (or something of the like) in Office 2007 isn't there?

  18. #18
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    Another option would be to utilise morefunc.xll (compiled in C and therefore pretty good performance wise), ie:

    =RIGHT(A1,FIND("/",TEXTREVERSE(A1))-1)

  19. #19
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How does this lookup work?

    StrReverse(str) is not availiable in vba in 97
    error sub or function not defined

    and alot of people out there still have it!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

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

    Re: How does this lookup work?

    StrReverse is alive and well in my Excel 2007, Martin.

  21. #21
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How does this lookup work?

    but lots of companies still use 97.

  22. #22
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    in which case they're most likely running on unsupported OS' so I suspect they have bigger fish to fry than worrying about the existence of StrReverse

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

    Re: How does this lookup work?

    StrReverse(str) is not availiable in vba in 97
    Sorry, Martin, you said "97" and I read "2007"

    Are your dot-matrix printers still buzzing along?

  24. #24
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How does this lookup work?

    this is true, but depends on how you use it, no good sending a sheet to someone with that function still in it is there?
    and people do with all sorts of weird stuff that wont run
    couldn't open 07 at work until they allowed the converter globally same, sort of thing
    and no i use a daisy wheel not those new fangled dot matrix jobbies
    Last edited by martindwilson; 06-16-2009 at 04:31 PM.

  25. #25
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How does this lookup work?

    Quote Originally Posted by mdw
    no good sending a sheet to someone with that function still in it is there.
    they have e-mail ?

    Quote Originally Posted by shg
    Are your dot-matrix printers still buzzing along?
    can they be heard over the grind of the A:\ ?

    (hijack over)

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

    Re: How does this lookup work?

    they have e-mail ?
    DO, that's insulting. A well-maintained teletype has lots of good years left. They probably have a converter to translate incoming pictures to BUAG.

  27. #27
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How does this lookup work?

    Quote Originally Posted by shg View Post
    But on the third hand......
    Which part of Texas are you from again?

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

    Re: How does this lookup work?

    Commanche Peak, near the power plant ... the nuclear power plant ...

  29. #29
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How does this lookup work?

    sounds of banjos duelling quietly in the distance.....................................................

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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