How do I use a cross reference cell to populate data from one excel sheet to another

Occasional Visitor

I'm wondering if this can be done. This project will take days if not. I have two spreadsheets. I need to merge data from one to another in a specific location.

Column A in both sheets have phone numbers.

Worksheet 1 is the MAIN file, and the data is to be listed with one phone number and then the corresponding info from that phone number in the other columns all the way across. 

Worksheet 2 has data in column E that I need to merge into Worksheet 1 under columns AC - AD - AE - AF (depending on how many entries are in E of Worksheet 1) that match the same phone number in Column A of BOTH worksheets?


2 Replies



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In the example the formula is in cell AC2 and filled across range AC2:AH27.

cross reference.JPG


If you are using Excel 365, Excel 365 for the Mac, or Excel for the web, there is another solution that may be more understandable (and thus easier to maintain).

=TRANSPOSE( FILTER('Worksheet 2'!E:E, ('Worksheet 2'!A:A=A2)*('Worksheet 2'!E:E<>""), "") )
But if you care to retain blank entries from Worksheet 2's column E, you can instead use the formula for row 6:
=SUBSTITUTE( TRANSPOSE( FILTER('Worksheet 2'!E:E, 'Worksheet 2'!A:A=A6, "") ), "", "" )