Help with VLOOKUP or Contains Formula

[tbruce]

Hi All,

I'm working on a VLOOKUP formula but I'm falling a little short on results. What I'm trying to do is to see if sheet 2's Full Name column is contained (either first or last name) in the Last Name column in Sheet 1. As you can see either the first or last name (or any combo) could appear on Sheet 2 so I need to do a contains vs. an exact match...

If there is a match to either the first or last name from Sheet 2 column B to Sheet 1 Column A, I then need to populate what name matched in Sheet 1 Column E, the full name from Sheet 2 column b, and also the phone number from Sheet 2 in column A

Hope that makes sense - would appreciate any help...

Sheet 1

	A	B	C	D	E	F	G
1	Last name	First Name	Age	City	Matched Name	Full Name Match	Phone Number
2	Doe	John	25	Dallas
3	Smith	Don	45	Fort Worth
4	Banks	James	35	Plano

<tbody>
</tbody>

Sheet 2

	A	B
1	Phone Number	Full Name
2	123-123-1234	Don Smith
3	555-555-1212	Banks James
4	765-123-5432	J Doe

<tbody>
</tbody>

 

[Special-K99]

This is gonna be a bit hit and miss.

Your Sheet 2 Full name column:

1) Contains names with first name last name in no specific order (This shouldnt be much of a problem, you could search for the Full name within a concatenated (First name&Last name&First Name) then the order wouldnt matter.
2) You havent even got a name row 4 just a letter J. So is J Doe the same as John Doe ? It's impossible to tell.
3) Depending how much data you have could there be more than one, say, John Smith ? How will you know whcih has the correct telephone number?