# Look Up based on names

#### tgordon2

##### New Member
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!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
try this

Excel 2012
ABC
1NameRankAFSC
2Doe, JohnE-61A471
3Doe, JaneO-211R
4Smith, TommyE-71C871
Sheet1
Cell Formulas
RangeFormula
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
ABC
1NameRankAFSC
2Doe, JohnE-61A471
3Doe, JaneO-211R
4Smith, TommyE-71C871
Sheet2

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!

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>

Replies
2
Views
127
Replies
2
Views
153
Replies
6
Views
514
Replies
19
Views
422
Replies
11
Views
478

1,203,506
Messages
6,055,807
Members
444,826
Latest member
aggerdanny

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back