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