Lookup in Columns with Multiple Values

[ksat]

Hi guys/gals...

My first post as I'm getting desperate - been pounding my head about this one for a while and getting no where. Let's see if I can describe this clearly...


I have a list of data for which I would like to fill in a cell/column by looking up column B with the data from another sheet. Once found, I need to use column H of that other sheet to populate said field on original sheet.
Sheet 1

	Column A	Column B
1	Apple	??
2	Banana	??
3	Orange	??
4	Kiwi	??

Sheet 2

	Column A	Column B
1	John Suzie Kiwi Bob	GREEN
2	Luke Apple Betty Jim	RED
3	John Jacob Banana Quinn	YELLOW

In the above example, I would like to fill column B on Sheet 1 with the contents of Column B on Sheet 2 by matching the contents of Sheet 1!Column A with Sheet 2 ! Column A.

Normally, VLOOKUP would work perfectly...but, because I'm not looking for a cell with only one piece of data in it, VLOOKUP doesn't work. I've also attempted MATCH or FIND, but, am a bit lost with what is being returned. Obviously, there is something wrong with my formula.

I would expect the following result:
Sheet 1

	Column A	Column B
1	Apple	RED
2	Banana	YELLOW
3	Orange	MISSING
4	Kiwi	GREEN

Any help would be much appreciated!!


thanks!!!

[daffodil11]

In B1:

=VLOOKUP("*"&A1&"*",Sheet2!$A$1:$B$500,2,0)

* is a wildcard value, meaning anything. So If we look for *123* in bob123weiner it will find a match.

[ksat]

In B1:

=VLOOKUP("*"&A1&"*",Sheet2!$A$1:$B$500,2,0)

* is a wildcard value, meaning anything. So If we look for *123* in bob123weiner it will find a match.

Thanks for the quick reply! This seems to have worked perfectly! I didn't expect to be using VLOOKUP for this, so, it was good to learn about the wildcards!

[daffodil11]

Thanks for the feedback. Glad I was able to help out.