Vlookup

[Jose]

Is it possible to use a vlookup when the lookup value is
a single word, but the values in the lookup ranges
contain the lookup value within a sequence of words. For
example, if I have something as follows, how could I use
Vlookup to look at the word "Pie" in column A, find that
Pie is located within the sequence of words in B2, and
therefore return the word "Apple" in C2. Note that I
would only want this to occur if an exact match for the
word "Pie" was found. Thanks.


Lookup Value: Lookup Range: Value to Returned:
Pie Piece of Cake Chocolate
Piece of Pie Apple
Piece of Pizza Pepperoni

 

[Myrna Larson]

Add wildcards to the beginning and end of the thing you're looking for, like
this:

=VLOOKUP("*"&A1&"*",B1:C3,2,0)

 

[Biff]

You'll have to include a space or the lookup will also
match: Piece ...

=VLOOKUP("*"&" "&A1&"*",B1:C3,2,0)

Biff

 

[Myrna Larson]

Probably a space on both sides, i.e.

"* "&A1&" *"

 

[Myrna Larson]

Of course my 2nd suggestion will fail when the string falls at the beginning
or end of the text, since there's not a space on *both* sides of it.