Forum Discussion
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: A. Phone number, B. First Name, and C, Last Name.
I would like to match on First and Last Names and add phone number to column D of spreadsheet one. Thanks, Joel.
Device: PC/Windows 10
Microsoft Office Home and Business 2013
Excel Product ID: 00196-20943-85146-AA714
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
18 Replies
- What you could do, is have a series of VLOOKUP commands on Table1 to lookup the values of Table2. Though I wouldn't recommend this for large datasets (in excess of 3000 rows) due to the time it would take to recalculate.
Could you follow the guidelines from Welcome to your Excel discussion space! please
=> This will help to help you- Thanks L z. See my edited message above. Hope that's what you need. Joel
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
