VLookup Contains?

[A00005764]

What I am trying to do is determine if a value from a list exists in a range of columns and return a value from a corresponding column.

In my list from sheet 1 column A I have values like:
U03370
W71101
X990DJ

In sheet 2 I will have in column A:
TESTSITE INC #U03370 / JMO BUSINESS / BLISH-MIZE
TESTSITE INC #X838MM/RADIUS SOLUTIONS/WEGMAN'S FOOD
TESTSITE INC #W71101/INFOLOGIX/FIELD CONTAINER
TESTSITE INC #X990DJ/PROPHET 21/MAGNUS INDUSTRIES
TESTSITE INC #X9950H/RUSH TRACKING/ODOMS TENNESSEE PRIDE

and in sheet 2 Column B I will have
28832
46375
47524
49119
49166

I want to return to sheet 1 column B the value from sheet 2 column B when sheet 1 column A is contained in sheet 2 column A.

Any help would be greatly appreciated. Most of the data I have is not as nicely formated as what I have provided here, so I can not do a text to columns or the like to reformat the information.

 

[Aladin Akyurek]

=INDEX(Sheet2!$B$2:$B$6,MATCH("*"&A2&"*",Sheet2!$A$2:$A$6,0))

 

[A00005764]

THANK YOU

Thank you for the quick and accurate response.

You have just saved one of my co-workers 4 hours a week.