Results 1 to 5 of 5

Thread: Data Matching Names from One Spreadsheet to Another

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Data Matching Names from One Spreadsheet to Another

    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?

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,527
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Data Matching Names from One Spreadsheet to Another

    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
    Sheet1



    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



    Array Formulas
    CellFormula
    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)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by Scott T; Sep 10th, 2019 at 03:57 PM.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,110
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Data Matching Names from One Spreadsheet to Another

    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)),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Matching Names from One Spreadsheet to Another

    Thank you both so much for the quick reply! I ended up using the IFERROR method and it worked perfectly!

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,110
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Data Matching Names from One Spreadsheet to Another

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •