MrExcel.com - Your One Stop for Excel Tips & Solutions

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.

Wildcard VLOOKUP
Figure 1

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.

Results

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.