Data Matching Names from One Spreadsheet to Another

bobothesmart

New Member
Joined
Sep 10, 2019
Messages
2
I am trying to do a data matching activity and I'm having difficulty figuring out how to do it.

I have two spreadsheets. Sheet1 has the columns LastName, FirstName, Birthdate, and Score. Sheet2 also has LastName, FirstName, Birthdate, and Score, but the Score is blank. There are a lot of names in Sheet1 and fewer in Sheet2.
I want to compare the names and birthdates. Any time the full name and birthdate in Sheet1 is found to match a full name and birthdate in Sheet2, it will copy the Score from Sheet1 to Sheet2. There will be many times that there will be no match and in those cases it will leave the Score blank.

Any help?
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,606
Office Version
365, 2016
Platform
Windows
Try
A
B
C
D
1
Lname
Fname
bdate
score
2
lname1
fnmae1
1/1/1970
55
3
lname2
fnmae2
2/7/1980
66
4
lname3
fnmae3
3/18/1966
44
5
lname4
fnmae4
3/18/1977
96
6
lname5
fnmae5
4/18/1977
33
7
lname6
fnmae6
7/20/1990
50
8
lname7
fnmae7
7/20/1990
99
9
lname8
fnmae8
7/27/1997
22
10
lname9
fnmae9
7/20/1999
90
11
lname10
fnmae10
7/20/1991
70
12
lname11
fnmae11
7/20/1990
60

<TBODY>
</TBODY>
Sheet1



<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Lname</td><td style=";">Fname</td><td style=";">bdate</td><td style=";">score</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">lname1</td><td style=";">fnmae1</td><td style="text-align: right;;">1/1/1970</td><td style="text-align: right;;">55</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">lname2</td><td style=";">fnmae2</td><td style="text-align: right;;">2/7/1980</td><td style="text-align: right;;">66</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">lname3</td><td style=";">fnmae3</td><td style="text-align: right;;">3/18/1966</td><td style="text-align: right;;">44</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">lname4</td><td style=";">fnmae4</td><td style="text-align: right;;">3/18/1977</td><td style="text-align: right;;">96</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">lname5</td><td style=";">fnmae5</td><td style="text-align: right;;">4/18/1977</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">lnotin1</td><td style=";">Fnotin1</td><td style="text-align: right;;">8/7/1980</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">lname7</td><td style=";">fnmae7</td><td style="text-align: right;;">7/20/1990</td><td style="text-align: right;;">99</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">lname8</td><td style=";">fnmae8</td><td style="text-align: right;;">7/27/1997</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">lname9</td><td style=";">fnmae9</td><td style="text-align: right;;">7/20/1999</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">lname10</td><td style=";">fnmae10</td><td style="text-align: right;;">7/20/1991</td><td style="text-align: right;;">70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">lname11</td><td style=";">fnmae11</td><td style="text-align: right;;">7/20/1990</td><td style="text-align: right;;">60</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=IFNA(<font color="Blue">INDEX(<font color="Red">Sheet1!$D$2:$D$12,MATCH(<font color="Green">Sheet2!A2&Sheet2!B2&Sheet2!C2,Sheet1!$A$2:$A$12&Sheet1!$B$2:$B$12&Sheet1!$C$2:$C$12,0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
Assuming you are looking at columns A, B, C with score in D. How about
=IFERROR(INDEX(Sheet1!$D$2:$D$25,MATCH(A2&"|"&B2&"|"&C2,INDEX(Sheet1!$A$2:$A$25&"|"&Sheet1!$B$2:$B$25&"|"&Sheet1!$C$2:$C$25,0),0)),"")
 

bobothesmart

New Member
Joined
Sep 10, 2019
Messages
2
Thank you both so much for the quick reply! I ended up using the IFERROR method and it worked perfectly!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,831
Messages
5,386,213
Members
401,985
Latest member
hahphd

Some videos you may like

This Week's Hot Topics

Top