Assuming that F2:F10 is the lookup column, G2:G10 is the return column,
and A2 contains the lookup value, try...

=LOOKUP(9.99999999999999E+307,SEARCH($F$2:$F$10,A2 ),$G$2:$G$10)

Note, however, if F2:F10 contains or can contain empty/blank cells, try
the following formula instead...

=LOOKUP(9.99999999999999E+307,IF($F$2:$F$10<"",SE ARCH($F$2:$F$10,A2)),$G
$2:$G$10)

....confirmed with CONTROL+SHIFT+ENTER. Also, note that if for example
the lookup value is 'reddish, white & blue', the formula will return a
match, since 'red' occurs within the text string.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Ken K wrote:

I am trying to match a list of data using Vlookup with an array that isn't an
exact match. Example, cell may have "red, white & blue" and the array has
"red". I want this to return a positive match, not N/A#.

Is there a way to do this using Vlookup or some other function?