Current Challenge      Past Challenges
About Mr.Excel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Media
Contact
Home

 

 


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 non-breaking 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 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.

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
d

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a Registered Trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.