Forum Discussion

JoelBauer's avatar
JoelBauer
Copper Contributor
Jul 10, 2021
Solved

Combine Matching Rows in two spreadsheets

In Excel how do you combine like rows in two different spreadsheets? Say spreadsheet one has three columns A. First Name, B. Last Name, and C. Town of Residence. Spreadsheet two has three columns: ...
excel
  • Lorenzo's avatar
    Lorenzo
    Jul 11, 2021

    JoelBauer 

    A small representative sample would have been appreciated 🙂

     

     

    With Table1 in one sheet, Table2 in another. In Table1 cell D3:

    =IF(COUNTIFS(Table2[First Name],A3, Table2[Last Name],B3),
    INDEX(Table2[Phone number],
        AGGREGATE(15,6,ROW(Table2[Last Name])-ROW(Table2[#Headers]) / COUNTIFS(A3,Table2[First Name], B3,Table2[Last Name]),1)
    ),
    "Not found"
)

    Corresponding sample attached

Resources