Name matching in different sheets with different formats

[bugalabuga]

Excel 2007, Windows 8.1

Sheet_A contains cell C1 with a full name (first and last).
Sheet_B contains cell B1 with a full name (first and last).

The problem is Sheet_A has some names referenced as JR SMITH and Sheet_B has the name listed as J.R. Smith. I reference both of the sheets in order to bring values over to a master sheet. I have to add the initials in to Sheet_A so that the names all match. I'd prefer to just match Sheet_A names to Sheet_B using VLookup or Index by creating another cell in Sheet_A (so that this new cell will return names that match the names from Sheet_B) but I am having problems creating the correct command to do this.

I'd like to search for the first initial and then the last name from C1 in Sheet_A and match it to B1 from Sheet_B and return that value into cell D1 in Sheet_A.

I've tried entering this into :

=VLOOKUP(LEFT(C1,1)&"*"&FIND(" ",C1)+1,2,3&"*",Sheet_B!B1:B500,1,0)...but this was a total fail.

Thanks in advance for suggestions...

 

[bugalabuga]

I was able to solve this for my purposes with the following:

=INDEX(Sheet_B!$B$1:$B$499,MATCH(LEFT(C1,1)&"*"&(MID(C1,FIND(" ",C1),5))&"*",Sheet_B!$B$1:$B$499,0))

Thanks!

T

 

[bugalabuga]

I was able to solve this for my purposes with the following:

=INDEX(Sheet_B!$B$1:$B$499,MATCH(LEFT(C1,1)&"*"&(MID(C1,FIND(" ",C1),5))&"*",Sheet_B!$B$1:$B$499,0))

Thanks!

T

Well, I thought this solved my problem but it did not quite do it. This particular command returned Bo Dangle in place of Brad Dangle.

Is there any way to match the 3rd (or any other chosen character) in addition to the characters I am searching above?

 

[bugalabuga]

Well, I thought this solved my problem but it did not quite do it. This particular command returned Bo Dangle in place of Brad Dangle.

Is there any way to match the 3rd (or any other chosen character) in addition to the characters I am searching above?

Anyone?