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

[_Zark]

Re: June/July 2008 Challenge of the Month

[edit]: attempted delete.

 

[mborg]

Re: June/July 2008 Challenge of the Month

Barry,
Can you explain what the "2^15" does in the lookup? I am trying to wrap my head around this, but can't do it... thanks!

 

[sous2817]

Hello mborg,

Have a look at post #34 of the thread (http://www.mrexcel.com/forum/showpost.php?p=1599318&postcount=34)

 

[paulsoliman]

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)
...this is simply brilliant!!!


my first match solution (i know, too complex)...

{=INDEX($E$2:$E$10,MAX(IF($D$2:$D$10=MID(A2,MIN(IF(ISERROR(SEARCH($D$2:$D$10,A2)),LEN(A2),SEARCH($D$2:$D$10,A2))),LEN($D$2:$D$10)),ROW(INDIRECT("1:"&COUNTA($D$2:$D$10))),0)))}

 

[Stefyu89]

Re: June/July 2008 Challenge of the Month

i've got one which is less lenghty : =RECHERCHE(2;CHERCHE("*"&$B$7:$B$9;A13);$C$7:$C$9) ( recherche : french for lookup and cherche : french for search).
The array after evaluation, always gives me a 1 for any item found rather than the position of the lookup value in the cell as for Barry Houdini formula.

Hope to have found something valuable.

 

[Aladin Akyurek]

Re: June/July 2008 Challenge of the Month

i've got one which is less lenghty : =RECHERCHE(2;CHERCHE("*"&$B$7:$B$9;A13);$C$7:$C$9) ( recherche : french for lookup and cherche : french for search).
The array after evaluation, always gives me a 1 for any item found rather than the position of the lookup value in the cell as for Barry Houdini formula.

Hope to have found something valuable.

Try rather:

=RECHERCHE(9,99999999999999E+307;CHERCHE("*"&$B$7:$B$9;A13);$C$7:$C$9)

See for a good reason:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

 

[Rick Rothstein]

Re: June/July 2008 Challenge of the Month

See for a good reason:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

I just added this post at the above referenced link...

Maybe in place of using 9.99999999999999E+307 for our largest number in LOOKUP's, we could use this 7-character shorter number instead... 10*1E+307

 

[Stefyu89]

@ Aladin

Thanks a lot Aladin for pointing me to some useful references. Meanwhile, i was already aware of this "Big Value" lookup method through you, actually through Mike Excelisfun Girvin who refers to you as the reference in formulas. A privilege to learn from you. Actually, i really wanted to emphasize the result from Search function which , like Barry Houdini case, gives the first position of the lookup text and when added an "*" indicates "1" for an item and "#VALUE" error for not found item.

It's much more similar to and less accurate than Ron Coderre formula on the same subject in another topic where "COUNTIF" is used to evaluate cell and "SUMPRODUCT" for adding all found items.

Hope to be clear. Once more time for your dedication to Excel Community. You're a model for us.

 

[Aladin Akyurek]

@ Aladin

Thanks a lot Aladin for pointing me to some useful references. Meanwhile, i was already aware of this "Big Value" lookup method through you, actually through Mike Excelisfun Girvin who refers to you as the reference in formulas. A privilege to learn from you. Actually, i really wanted to emphasize the result from Search function which , like Barry Houdini case, gives the first position of the lookup text and when added an "*" indicates "1" for an item and "#VALUE" error for not found item.

It's much more similar to and less accurate than Ron Coderre formula on the same subject in another topic where "COUNTIF" is used to evaluate cell and "SUMPRODUCT" for adding all found items.

Hope to be clear. Once more time for your dedication to Excel Community. You're a model for us.

Ma parole! Pas de problem. I know what you mean. And thanks for the kind words. To recap a small history:

1) Look up functions ignore error values if they possibly can (they can't if they are just fed with, e.g., {#VALUE!;#VALUE!}).

2) Look up functions with match type set to 1 (or TRUE) are really fast.

3) When a look up function is given a numerical look up value for looking it up in a numeric reference, with BigNum sufficiently large

LOOKUP(BigNum,Reference)

returns invariably the last numeric value of Reference and does so very fast.

MATCH(BigNum,Reference,1)

returns the position of the last numeric value of Reference and does so very fast.

4) Sufficiently large means:

LOOKUP(MAX(Reference)+Delta,Reference)

where Delta is very small number (or just 1 for convenience's sake).

5) The reason for not using MAX(Reference)+Delta is threefold:

• it means an additional function call;
• as function MAX is slow because it examines every cell in Reference;
• MAX is sensitive to the presence of error values in Reference;

6) I've met the last value usage for the first time in a post by Dave Hawley and it's me (I believe) who introduced and showed with many others the usage worldwide thru forums (mrexcel, newsgroups, and so on).

7) I had something like 1E+30 as BigNum initially. After the following exchange with Mark W. in 2001...

Excel last number in column

I opted ever since for Excel's 9.99999999999999E+307.

8) Since there is no reason for choosing an ever-changing value for BigNum,
I still use 9.99999999999999E+307 anchored in Excel itself (or a Pi-like contraction: 9.99E+307).

9) It would be really great if MS would provide names for such constants it is bounded with.

 

[Marcelo Branco]

Aladin,

Excellent explanation!

M.