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

[Derek Brown]

Perhaps I should add that if the last argument is set to TRUE, the function will perform in the same way as VLOOKUP. However, it is possible to set that last argument to a specific cell reference and then be able to 'switch' between the two types of result by just changing the value of one cell.

[stanleydgromjr]

My last try (shorter code).

Thank you Daniel Ferry.


Thanks, had a lot of fun with this.

Option Explicit
Sub ColorAssignedTo()
    Dim LRA&, LRD&
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        LRA& = Cells(Rows.Count, 1).End(xlUp).Row
        LRD& = Cells(Rows.Count, 4).End(xlUp).Row
        Range("B2:B" & LRA&).FormulaR1C1 = "=LOOKUP(32767,FIND(R2C[2]:R" & LRD& & "C[2],RC[-1]),R2C[3]:R" & LRD& & "C[3])"
        .CutCopyMode = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Have a great day,
Stan

[bosco_yip]

1] =lookup(2,1/find(d$2:d$10,a2),e$2:e$10)

2] =lookup(0,1/find(d$2:d$10,a2)-1,e$2:e$10)

3] {=index($e$2:$e$10,match(1,--isnumber(find($d$2:$d$10,a2)),0),0)}

[bosco_yip]

Originally Posted by bosco_yip

1] =lookup(2,1/find(d$2:d$10,a2),e$2:e$10)

2] =lookup(0,1/find(d$2:d$10,a2)-1,e$2:e$10)

3] {=index($e$2:$e$10,match(1,--isnumber(find($d$2:$d$10,a2)),0),0)}

Add 2 more formula :

4] =LOOKUP(0,-FIND(D$2:D$10,A2),E$2:E$10)

5] =LOOKUP(0,FIND(D$2:D$10,A2)-1,E$2:E$10)

[bbotzong]

here's a function. useage is as follows. In cell B2 enter formula =sLookup(A2,$D$2:$D$10,2) which calls the sLookup function as follows:

Function sLookup(txtInput As String, rngList As Range, wIndex As Integer)
Dim wRow As Integer
Dim wCol As Integer

' find how many keywords in rngList

wRow = 1
wList = rngList

For Each cell In rngList
If InStr(1, txtInput, cell) > 0 Then
wRow = cell.Row
wCol = cell.Column
sLookup = Cells(wRow, wCol + wIndex - 1)
Exit Function
End If
Next 'cell
End Function

[Rhino_Dance]

I'm new here and really appreciate this forum. Thanks to everyone for your... brilliance. I realize I have so much more to leverage in xl.

Ok, I'm writing this post for anyone like me- who really scratched their heads over this one. Perhaps there are a few? I read Richard's explanation which got me halfway there but couldn't pull off the juggling necessary. Thought I'd explain how I finally understood it to those who may be having my experience. The formula to be interpreted is:

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

First, the SEARCH function has the argumants backward from what I'm used to. Usually with the SEARCH /FIND function I'm looking for 1 thing in an ocean of things. In this case an ocean of things are looking for one thing and returning 9 results. I kept thinking that a SEARCH involving 'A2' must only return one number. I was also visually thrown off by the lack of Braces {} reminding me of array formula.

So, the SEARCH function returns an array of 9 results each corresponding to the colors in the order they appear. The array always has 8 errors and one valid number like this:

#VALUE!
#VALUE!
#VALUE!
12
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

The lookup function will always match the huge number (32,767) to the one row with the valid number- row 4 in this case- and that is of course the row of the matching color. The Lookup function then goes 4 rows down in column 'E' to find the correct person's name.

I realize this is known to most but though I'd describe the formula in the way it finally came to me. And I agree the formula is beautiful in its simplicity.

My formula was pretty standard. I added ascending numbers next to the color/name table though.

={INDEX(Color_Tab,SUM(IF(ISERROR(FIND(Colors,$A2,1)),0,Color_Nums)),3))

Cheers

[andrewman]

Originally Posted by MrExcel

I've posted a new challenge of the month today. Like the last challenge, this one will have many different approaches. Post your entries here. While the ultimate "best" entry wins the LiveLessons DVD, several podcast DVD's are available to anyone who proposes a significant advancement towards a cool solution.

Entries are due by 15 July 2008.

Bill Jelen

When we have the result for June/July 2008 Challenge?

[jonmo1]

Seriously, here we are in October. I'm actually curious, this is the first challenge I've participated in. When/How will we know who wins? And when might there be a new challenge?

[stanleydgromjr]

When/How will we know who wins? And when might there be a new challenge?


Have a great day,
Stan

[gavinkelly]

I think a name change is in order, perhaps "Challenge once in a blue moon".

Come on pick a winner and make a new challenge please