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

Re: June/July 2008 Challenge of the Month

Here's one possibility.....

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

Can someone help me to break this formula out in layman's terms for me? I'm an intermediate excel user, but I don't understand how this is working.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Re: June/July 2008 Challenge of the Month

Can someone help me to break this formula out in layman's terms for me? I'm an intermediate excel user, but I don't understand how this is working.
Check out the posts from around Jun 17th, 2008 and the links within them. There were many posts on Barry's solution.
 
Re: June/July 2008 Challenge of the Month

When we have the result for June/July 2008 Challenge?:(
 
Re: June/July 2008 Challenge of the Month

That is a good question! I have been waiting too!

In fact, in the MrExcel Survey they just sent out yesterday, I said that they should do more of this monthly challenge stuff!
 
Re: June/July 2008 Challenge of the Month

A minor tweak I think could make the "fastest" formula faster...

By changing it from: =LOOKUP(9^6,SEARCH(D$2:D$10,A2),E$2:E$10)

to: =LOOKUP(LEN(A2),SEARCH(D$2:D$10,A2),E$2:E$10)

...it would only search only the number of characters actually in the cell, not the ~32k as previously defined.
 
Re: June/July 2008 Challenge of the Month

A minor tweak I think could make the "fastest" formula faster...

By changing it from: =LOOKUP(9^6,SEARCH(D$2:D$10,A2),E$2:E$10)

to: =LOOKUP(LEN(A2),SEARCH(D$2:D$10,A2),E$2:E$10)

...it would only search only the number of characters actually in the cell, not the ~32k as previously defined.

Hi Mattison,

9^6 is a number,

but,

LEN(A2) is a function

Replaced 9^6 with LEN(A2) would add one more function to the formula

…..the changes made the formula slower

Regards
Bosco
 
Re: June/July 2008 Challenge of the Month

Compared to others, I feel like such a beginner... :p

Function FLookup(FullString As String, KeywordList As Range) As String
Dim Key As String
For i = 1 To KeywordList.Rows.Count
Debug.Print i & " " & FullString & " " & KeywordList(i, 1) & " " & KeywordList(i, 2)
Key = "*" & KeywordList(i, 1) & "*"
If KeywordList(i, 1) = "" Then Exit Function
If FullString Like Key Then
For j = 1 To KeywordList.Rows.Count
If KeywordList(i, 1) = KeywordList(j, 1) Then
FLookup = KeywordList(j, 2)
Exit Function
End If
Next
End If
Next
End Function
 
Re: June/July 2008 Challenge of the Month

Compared to others, I feel like such a beginner... :p

Function FLookup(FullString As String, KeywordList As Range) As String
Dim Key As String
For i = 1 To KeywordList.Rows.Count
Debug.Print i & " " & FullString & " " & KeywordList(i, 1) & " " & KeywordList(i, 2)
Key = "*" & KeywordList(i, 1) & "*"
If KeywordList(i, 1) = "" Then Exit Function
If FullString Like Key Then
For j = 1 To KeywordList.Rows.Count
If KeywordList(i, 1) = KeywordList(j, 1) Then
FLookup = KeywordList(j, 2)
Exit Function
End If
Next
End If
Next
End Function

QuinDavis<SCRIPT type=text/javascript> vbmenu_register("postmenu_1768518", true); </SCRIPT>

Join Date: Dec 2008
Posts: 0



QuinDavis <<<<< you got zero count on posting??? :biggrin:
How you did that?
 
Re: June/July 2008 Challenge of the Month

QuinDavis <<<<< you got zero count on posting??? :biggrin:
How you did that?

I'm either a master hacker or a virgin newbee... :rolleyes: (Not hacker..)
 
Re: June/July 2008 Challenge of the Month

Posts in the Lounge do not count towards your total post count - so if QuinDavis has not posted in any other forum on the Board then he will have a post count of zero :biggrin:
 

Forum statistics

Threads
1,212,938
Messages
6,110,771
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top