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

[Richard Schollar]

Hi Pharmacom

Check out this brief explanation:

http://www.mrexcel.com/forum/showpost.php?p=1599318&postcount=34

 

[Phamacom80]

Richard,

After reading the post, I seem to be more confuse. Could you please explain it in lament's term. Thanks.

Phamacom80

 

[Fowmy]

This solution for the challenge was submitted by
Barry Houdini

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

Explanation:

SEARCH received D$2:D$10 as the findtext argument. So It returned the results as an array like this {14;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
since the keyword "Blue" in A2 was found in the 1st row of the keyword column, SEARCH returned 14 (the position of the keyword from A2).

Then, the results returned by the SEARCH is used for the Lookup_vector argument of the Lookup function.

A cell can contain characters up to 32767 which is same as 2^15.

LOOKUP trie to match 32767 in the lookup_vector which is the search results shown above, since it could not find it, it matched the largest value that is 14 (the 1st value) sitting in the 1st position. so the lookup returned what ever found in the same position in Assigned to column which is Joe.

Formula Result:

=LOOKUP(2^15,{14;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},{"Joe";"Bob";"Mary";"Fred";"Ralph";"Lora";"Tracy";"Earl";"Jenny"})

 

[Phamacom80]

Thank you thank you. That's exactly what I needed. I now fully understand the answer.

 

[excelproz]

=IF(NOT(ISERROR(FIND("blue",A3))),VLOOKUP("blue",D:E,2),"") &
IF(NOT(ISERROR(FIND("red",A3))),VLOOKUP("red",D:E,2),"") &
IF(NOT(ISERROR(FIND("yellow",A3))),VLOOKUP("yellow",D:E,2),"") &
IF(NOT(ISERROR(FIND("pink",A3))),VLOOKUP("pink",D:E,2),"") &
IF(NOT(ISERROR(FIND("orange",A3))),VLOOKUP("orange",D:E,2),"") &
IF(NOT(ISERROR(FIND("brown",A3))),VLOOKUP("brown",D:E,2),"") &
IF(NOT(ISERROR(FIND("white",A3))),VLOOKUP("white",D:E,2),"") &
IF(NOT(ISERROR(FIND("lavendar",A3))),VLOOKUP("lavendar",D:E,2),"") &
IF(NOT(ISERROR(FIND("magenta",A3))),VLOOKUP("magenta",D:E,2),"")
adapted to any size via dragging down or double-clicking the corner .. no nesting used so it should be legit solution

 

[Oorang]

True. But that is some rough calculation time.

 

[alexhett]

Re: June/July 2008 Challenge of the Month

2^15 = 32,768
32,768 is exactly 1/2 of the maximum number of rows in an Excel 97-03 spreadsheet (65,536).
2^16 = 65,536
I'm not sure exactly why it's 1/2 the max rows rather than the full amount of max rows, but...

The most elegant solution is always the simplest solution, provided that it is sufficient.

And from a pragmatic viewpoint, there's no point in wasting processing power handling that super large 3.9999999E307 number when you can use a smaller, but still sufficient one.

 

[excelproz]

Re: June/July 2008 Challenge of the Month

2^15 = 32,768
32,768 is exactly 1/2 of the maximum number of rows in an Excel 97-03 spreadsheet (65,536).
2^16 = 65,536
I'm not sure exactly why it's 1/2 the max rows rather than the full amount of max rows, but...

The most elegant solution is always the simplest solution, provided that it is sufficient.

And from a pragmatic viewpoint, there's no point in wasting processing power handling that super large 3.9999999E307 number when you can use a smaller, but still sufficient one.

are you talking about my solution or the "chosen" solution?

 

[Aladin Akyurek]

Re: June/July 2008 Challenge of the Month

2^15 = 32,768
32,768 is exactly 1/2 of the maximum number of rows in an Excel 97-03 spreadsheet (65,536).
2^16 = 65,536
I'm not sure exactly why it's 1/2 the max rows rather than the full amount of max rows, but...

The most elegant solution is always the simplest solution, provided that it is sufficient.

And from a pragmatic viewpoint, there's no point in wasting processing power handling that super large 3.9999999E307 number when you can use a smaller, but still sufficient one.

Are you claiming that

2^16

is processed faster than

9.99999999999999E+307

which is mentioned in Help under Excel specifications and limits? If so, would you also explain why?

 

[Owen Luo]

Cause I just found this forum. And I am so interested in Excel and VBA.

Here is my solution as an UDF.

Function proLookUp(refCell As Range, rangeSrc As Range, indexNum As Integer)

     Dim i As Integer
     
     For i = 1 To rangeSrc.Rows.Count
           
        If InStr(1, refCell.Value, rangeSrc(i, 1).Value, vbTextCompare) > 0 Then proLookUp = rangeSrc(i, indexNum): Exit For
        
    Next i
     
End Function

sample usage, input "=proLookUp(A2,$D$2:$F$10,2)" in cell B2