Look Up based on names

[tgordon2]

I have two sheets that I pull down and have to add a column from one of them to the other. I want to do a search based on the names but one sheet has Last, First MI and the other is Last, First. The primary sheet is the one with only the last name, first name. I would like to have that sheet search the other one and return information in another column.

Example
Sheet 1
Name Rank AFSC
Doe, John E-6
Doe, Jane O-2
Smith, Tommy E-7

Sheet 2
Name Rank AFSC
Doe, John P. E-6 1A471
Doe, Jane R. O-2 11R
Smith, Tommy M. E-7 1C871


I appreciate any help that anyone could provide!

 

[AlanY]

try this

Excel 2012
	A	B	C
1	Name	Rank	AFSC
2	Doe, John	E-6	1A471
3	Doe, Jane	O-2	11R
4	Smith, Tommy	E-7	1C871
Sheet1

Cell Formulas
Range	Formula
C2	{=INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!A:A&Sheet2!B:B,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2012
	A	B	C
1	Name	Rank	AFSC
2	Doe, John	E-6	1A471
3	Doe, Jane	O-2	11R
4	Smith, Tommy	E-7	1C871
Sheet2

 

[tgordon2]

That would work if Sheet 2 didn't have middle initials. Sheet 2 has middle initials for the names, but not all names in sheet

I hope the image shows up. If not, I can email you a copy and you can see what I am talking about. Thank you very much Alan for your help so far!

 

[AlanY]

could you upload/share the file on dropbox/google drive or similar

 

[Aladin Akyurek]

Sheet2

Row\Col	A​	B​	C​
1​	Name	Rank	AFSC
2​	Doe, John P.	E-6	1A471
3​	Doe, Jane R.	O-2	11R
4​	Mess, Xavier Dan	Z-1	6XAD
5​	Smith, Tommy M.	E-7	1C871

Sheet1

Row\Col	A​	B​	C​
1​	Name	Rank	AFSC
2​	Doe, John	E-6	1A471
3​	Doe, Jane	O-2	11R
4​	Mess, Dan	Z-1	6XAD
5​	Smith, Tommy	E-7	1C871

<strike></strike>
In C2 of Sheet1 control+shift+enter, not just enter, and copy down:

=INDEX(Sheet2!$C$2:$C$5,MATCH(SUBSTITUTE(A2,",","*")&"*",IF(Sheet2!$B$2:$B$5=$B2,SUBSTITUTE(Sheet2!$A$2:$A$5,",","*")),0))
<strike></strike>