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

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Last name[/TD]
[TD]First Name[/TD]
[TD]Age[/TD]
[TD]City[/TD]
[TD]Matched Name[/TD]
[TD]Full Name Match[/TD]
[TD]Phone Number[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Doe[/TD]
[TD]John[/TD]
[TD]25[/TD]
[TD]Dallas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Smith[/TD]
[TD]Don[/TD]
[TD]45[/TD]
[TD]Fort Worth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banks[/TD]
[TD]James[/TD]
[TD]35[/TD]
[TD]Plano[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Phone Number[/TD]
[TD]Full Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123-123-1234[/TD]
[TD]Don Smith[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]555-555-1212[/TD]
[TD]Banks James[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]765-123-5432[/TD]
[TD]J Doe[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?