***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[earthworm]

Re: June/July 2008 Challenge of the Month

The formula you are referring to:

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

has the same structure as one often runs up against:

[1] LOOKUP(9.99999999999999E+307,MatchReference,ResultReference)

Reference stands for a vector like A2:A10 or an array like {1,#N/A,2,5,2}.

This expression returns the value from ResultReference that corresponds to the last numeric value in MatchReference. Note that:

[2] LOOKUP(9.99999999999999E+307,Reference)

always returns the last numeric value if one is available. How this works is explained in Post #7 at the following link:

http://www.mrexcel.com/forum/showthread.php?t=310278

Also, note that 9.99999999999999E+307 is a big constant mentioned in Excel's Help under "limits." So, 2^15 plays the same role, and in that sense, it's an unfortunate variation.

Back to [1]... The Search bit

SEARCH(D$2:D$10,A2)

yields an array reference of ten items, consisting of the #VALUE! errors and integer numbers. When a search value from D2:D10 is not in A2, we get a #VALUE! error, otherwise the start position of that search value in A2. When LOOKUP with the big number looks at this reference, it gets last position (integer) value and fetches the value that corresponds to it from E2:E10.

i m laughing at the moment as soon as i start reading in the middle of paragraph i m lost . please elaborate in very very simple terms . i m not that hardcoder vba guy . suppose you want to make a calculation in paper how would you do it . what i m facing problem is my basic understanding of this formula is not clear . i even read the link which you posted , couldnt get a clue.

 

[mgirvin]

Thanks Aladin!

I always use the metaphor of:

1) the lookup value starting at the beginning
2) looking at each value and asking "Am I bigger than you?"
3) when the answer is yes, it jumps back one row

But your color coded post of how it divides in two and checks is very helpful!!

They both will come to the same conclusion, but the "divide in two..." has to be faster!

 

[Aladin Akyurek]

Thanks Aladin!

I always use the metaphor of:

1) the lookup value starting at the beginning
2) looking at each value and asking "Am I bigger than you?"
3) when the answer is yes, it jumps back one row

But your color coded post of how it divides in two and checks is very helpful!!

They both will come to the same conclusion, but the "divide in two..." has to be faster!

Mike,

We have two algorithms regarding lookup functions: Linear search and binary search. VLOOKUP, HLOOKUP, and MATCH with the match-type set to 0 or FALSE does (a form of) linear search. With the match-type set to 1 or TRUE (or omitted) and LOOKUP by default does (a form of) binary search. That's why the latter are very fast: no cell by cell evaluation.

Regards,

Aladin

 

[Aladin Akyurek]

Re: June/July 2008 Challenge of the Month

i m laughing at the moment as soon as i start reading in the middle of paragraph i m lost . please elaborate in very very simple terms . i m not that hardcoder vba guy . suppose you want to make a calculation in paper how would you do it . what i m facing problem is my basic understanding of this formula is not clear . i even read the link which you posted , couldnt get a clue.

Consider:

<TABLE style="WIDTH: 364pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=485 x:str><COLGROUP><COL style="WIDTH: 163pt; mso-width-source: userset; mso-width-alt: 7936" width=217><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 163pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=217> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=76> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Ontario meets GAD in a snowy day</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla="=LOOKUP(9.99999999999999E+307,SEARCH(D2:D3,A2),E2:E3)">Canada</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>DAF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>America</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>GAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Canada</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>ZAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Kazakhstan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR></TBODY></TABLE>

A2: Ontario meets GAD in a snowy day

D2: DAF
D3: GAD
D4: ZAD

E2: America
E3: Canada
E4: Kazakhstan

B2:

=LOOKUP(9.99999999999999E+307,SEARCH(D2:D4,A2),E2:E4)

The SEARCH bit gives:

{#VALUE!;15;#VALUE!}

which means: D2 is not in A2, D3 is in A2, starting at the 15th char, and D4 is not in A2.

The last numeric value in {#VALUE!;15;#VALUE!} is 15, which is the 2nd item. LOOKUP then returns the 2nd item from E2:E4, which is Canada.

Does this help?

 

[earthworm]

^ is the power symbol
2 to the 15th power = 32768

Open up the calculator
press 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2
That results in 32768

That's how the Power works..


According to Excel Help Files, A cell can hold no more than 32767 characters.
Search returns the position # where the search value is found in the string.
If a string can only be 32767 characters long, then it is impossible for search to return a number larger than 32767.
so 32768 (2^15) is used to ensure the lookup value is larger than any possible result of the SEARCH function.

If the lookup value is larger than any value in the search range, then lookup returns the Next largest number value.
When the data is not sorted, that means it returns the last number value.

Now thats what i call user friendly explanation

1) A cell can hold no more than 32767 characters , now i understand the concept behind 2^5 , it means lookup value looks for value bigger then other value if first condition is not met so what he did was he set the highest limit for the search and obviously the data has to exist in such high limit

2) now what does this formula do is ? as far as i have learned
lookup value from the cell , then match the value from the table and then give the result from result column.

Now tell me how will excel read / calculate the above formula . you can explain in words only , i will catch it but please in simple words

 

[Jonmo1]

I can't imagine explaining it any better than Aladin did in his last post...

Except maybe add this

=LOOKUP(9.99999999999999E+307,SEARCH(D2:D4,A2),E2:E4)

The SEARCH bit gives:

{#VALUE!;15;#VALUE!}

So the formula can then be looked at like this, by just replacing the search function with it's resulting array of values.

=LOOKUP(9.99999999999999E+307,{#VALUE!;15;#VALUE!},E2:E4)

Then replace E2:E4 with it's values

=LOOKUP(9.99999999999999E+307,{#VALUE!;15;#VALUE!},{"America","Canada","Kazakhstan"})

So taking that, the lookup searches for the 9.9999 number in the FIRST array,
If exact match is not found (which we know it won't because of using the large number) then it matches the LAST Numeric Value from the right. So whatever position # the LAST numeric value is in the First array, the lookup returns the corresponding value from the Second array.
In this case, it's Canada


And to avoid the confusion...

Aladin used 9.99999999999999E+307 instead of 2^15 (or 32768).
Both numbers serve Exactly the same purpose, to be larger than any possible result of the Search Funciton.
It's just a personal choice of which one to use..

 

[xymptlx]

Great, but for someone who is trying to learn, it demonstrates that it works but not why....

 

[Aladin Akyurek]

Great, but for someone who is trying to learn, it demonstrates that it works but not why....

Care to be specific? Is that LOOKUP with the big number?

 

[earthworm]

This is one of the best thread i have encountered soo far , the formula helped me alot and learned a lot from this .

 

[Hooseria]

I've posted this answer before, but couldn't post an example, I see you guys like Complicated long formulas and Macro's.

This simple array Vlookup formula works well on the challenge question.

Firstly The KeyWord List is A named Range "Keyword" & the Keyword and Assigned to List is combined in a named range "Table". The length of both named ranges is the size of the phrase column. This makes it a continuos user friendly formula.
A B C D E
Phrase Formula Keyword Assigned To

{=VLOOKUP((OFFSET($D$3,MATCH(TRUE,ISNUMBER(SEARCH(Keyword,A3)),0)-1,0)),Table,2,FALSE)}