# How does this lookup work?

1. ## 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.

2. ## 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.

3. ## 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. ## 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. ## Re: How does this lookup work?

Originally Posted by DonkeyOte
...
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. ## 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.

7. ## Re: How does this lookup work?

Originally Posted by starryknight64
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. ## 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. ## 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. ## Re: How does this lookup work?

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. ## Re: How does this lookup work?

Originally Posted by DonkeyOte
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. ## Re: How does this lookup work?

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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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!

20. ## Re: How does this lookup work?

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

21. ## Re: How does this lookup work?

but lots of companies still use 97.

22. ## 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. ## 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. ## 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

25. ## Re: How does this lookup work?

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

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. ## 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. ## Re: How does this lookup work?

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

28. ## Re: How does this lookup work?

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

29. ## Re: How does this lookup work?

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

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