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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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




Book1
ABCD
1LnameFnamebdatescore
2lname1fnmae11/1/197055
3lname2fnmae22/7/198066
4lname3fnmae33/18/196644
5lname4fnmae43/18/197796
6lname5fnmae54/18/197733
7lnotin1Fnotin18/7/1980
8lname7fnmae77/20/199099
9lname8fnmae87/27/199722
10lname9fnmae97/20/199990
11lname10fnmae107/20/199170
12lname11fnmae117/20/199060
Sheet2
Cell Formulas
RangeFormula
D2{=IFNA(INDEX(Sheet1!$D$2:$D$12,MATCH(Sheet2!A2&Sheet2!B2&Sheet2!C2,Sheet1!$A$2:$A$12&Sheet1!$B$2:$B$12&Sheet1!$C$2:$C$12,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
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)),"")
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top