| Current Challenge Past Challenges |
 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
 |
|
Challenge for for June/July 2008: Wildcard VLOOKUP
The VLOOKUP function is one of my most-used tools in Excel. But in this challenge, VLOOKUP won't quite work. The data in column A contains a series of phrases. Every phrase contains one color in the phrase. The goal of the challenge is to use the lookup table in D2:E10 to assign the phrase to one of the names in column E.
You can use a formula. You can use a User Defined Function. You can use a macro. The solution should be adaptable to any size data in column A and any size table in columns D:E. Yes, someone with Excel 2007 could nest 8 if statements to solve the current problem, but this will not extend to a table with 34 entries, so this is not a valid approach.
Download the zipped challengejune2008.zip file to build your solution.
Deadline: the end of the day on Tuesday, July 15, 2008.
Prizes: include copies of the LiveLessons Power Excel DVD and copies of the Podcast DVD.
Entry: This will be a collaborative challenge. Many people will come up with similar approaches. Post your formula, function, or macro to this thread in the message board. Whoever comes up with a significant improvement to the best solution thus far will win a copy of the podcast DVD. The LiveLessons DVD will be awarded to the best formula, the best user defined function, and to the best macro. While "best" is a subjective measure, the decision of the judges is final.
Back to top