

Solve an Excel Challenge and WIN A PRIZE! Check out our challenge of the month and see what you can do! We periodically post a challenge to our Excel user community. Whoever successfully solves the problem wins a cool
MrExcel.com prize and bragging rights! What more could you ask for?

Challenge for for June/July 2008: Wildcard VLOOKUP
This was a fascinating challenge. After reading through all the solutions, there are three main winners, all of whom used a formula to solve the problem:
 Barry Houdini receives a podcast DVD and a LiveLessons Power Excel DVD for his formula posted here. The formula is =LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10). His formula returns the last match in the range. It is not an array formula. Superb solution.
 Aladin Akyurek also wins a podcast DVD and the LiveLessons DVD for improving upon Barry's formula here. Aladin's formula will return complete words only, blueline would not be a match for blue. Later, Aladin offered a method for dealing with character 160 spaces instead of regular spaces.
 Andrew Fergus is the 3rd winner for his interesting variation using iteration to return multiple matches. See the formula here.
The rules offered a LiveLessons DVD to the best UDF and macro solution. I'll award three here as well:
 Jonmo1 for a UDF solution in post #10 & #31. Returns first match but doesn't deal with text containing nonbreaking spaces. This aspect was subsequently corrected by Jonmo1. Also subsequently wrote UDF to return nth color in string. Also amended to include compound matches (or exclude).
 Jindon for a UDF solution in post #60. Interesting UDF! First match only.
 Schielrn for a Macro solution in post #8. Only identifies last match in match range. Simple, easy, and expandable.
Then, all of these entries will receive a podcast DVD as honorable mention for their entry:
 RichardSchollar for a Formula solution in post #2. Last/first match in range, plus most frequent match
 Oorang for a Formula solution in post #4. Complicated formula for first match
 pepijn for a Formula solution in post #27. Returns first match.
 zapicm for a UDF solution in post #36. Variation on a theme using Regular Expressions to perform the match. Returns all matching colors in cell
 rgdwar1 for a Formula solution in post #39. Variation on similar theme. Nice use of Match.
 wsjackman for a Formula solution in post #43. Somewhat different approach using Indirect/Sumproduct  fails on multiple matches though
 Daniel Ferry for a Formula solution in post #52. Somewhat different approach using Offset  fails on multiple matches though
 Quasi for a Formula solution in post #59. INDEX/MATCH variant. Doesn't fail on multiple matches
 Stanleydgromjr for a Macro solution in post #61. First match. Reasonably quick  avoids looping (writes formulas to cells, then copies values over)
 ghatfanazzam for a Formula solution in post #80. Using Vlookup. CSE. Returns first match
 D Kelly O'Day for a Macro solution in post #86. Not particularly fast. Returns the last match
 DiscoPistol for a Macro solution in post #87. Handles multiple return colors
 drwhittle for a UDF solution in post #93. Works
 squiresk for a Macro solution in post #94. Works
 Jerry0 for a Macro solution in post #98. Works
 Gene Khalyapin for a Formula solution in post #102. Fails on multiple matches
 parmel for a Macro solution in post #105. returns last occurrence. Uses autofilter.
 gavinkelly for a UDF solution in post #109. returns 1st occurrence
 WinteE for a UDF solution in post #113. returns 1st occurrence
 indiaravi for a Macro solution in post #120. Finds last occurrence
 Nenad Stojkovski for a UDF solution in post #122. returns 1st occurrence
 Derek Brown for a UDF solution in post #126. returns 1st occurrence
 pjzamudio for a Macro solution in post #129. returns all occurences
 bosco_yip for a Formula solution in post #134. returns last occurrence
 EugeneCarter for a Formula solution in post #137. Fails on multiple matches
 Andrewman for a UDF solution in post #138. returns 1st occurrence
 kevinmintz006 for a Macro solution in post #142. Simple macro
 bbotzong for a UDF solution in post #156. returns 1st occurrence
 smartchap for a Macro solution in post #164. returns last occurrence
 gardnertoo for a Formula solution in post #172. returns last occurrence
 bridgesm for a Macro solution in post #191. returns last occurrence
 Bidyut Saha for a Macro solution in post #192. returns last occurrence
 KaranVBExpert for a Macro solution in post #193. returns last occurrence
 QuinDavis for a UDF solution in post #207. returns 1st occurrence
Thanks to everyone who entered. Drop a note to bill at mrexcel.com with your mailing address to claim your prize.
Excel Challenge for for June/July 2008: Wildcard VLOOKUP
The VLOOKUP function is one of my mostused 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.
Challenge 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.
Don’t forget to check out all of MrExcel’s past Excel challenges!
Back to top 