Insert data from an Excel column into the same column of another spreadsheet

New Contributor

I need to insert into an Excel spreadsheet (which I'll call "integrative") the data coming from other spreadsheets, whose data may be in different column positions. I don't want to do it manually, i.e. copy and paste, but automatically the data from (for example) the column named 'LastName' in an external spreadsheet, is inserted exactly into the column named 'LastName' of the spreadsheet "integrative". Can this be done using Microsoft Query? Or maybe through Power Pivot? If so, can you explain me step by step? Thanks a lot.

2 Replies


=INDIRECT(ADDRESS(ROW(),MATCH(C$1,'another spreadsheet'!$1:$1,0),,,"another spreadsheet"))

Maybe with this formula. In the example the formula is copied down and to the right in order to add data from the "address" column into the 'integrative' sheet.

data from another sheet same column header.JPG 

Thank you @Quadruple_Pawn. I will explore this option. Regards.