# 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.

## 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.