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.

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.

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 ?

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?

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.

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

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

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

Originally Posted by daddylonglegs

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

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?

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.

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."

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.

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

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

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

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

and alot of people out there still have it!

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

but lots of companies still use 97.

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

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?

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

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)

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.

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

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