Forum Discussion
How do I use a cross reference cell to populate data from one excel sheet to another
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 nu...
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, "") ), "", "" )