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!!!