Forum Discussion
Multi-worksheet Comparison ingestion
- Jul 14, 2023oops, my bad. totally missed that. lol.
do you know how to create tables and use structured references? I recommend that if you can.
OK I didn't go back into the data but just looking at that formula I see a few things and what you said in the comment also.
As for the formula I see [#ALL] which means include both the data AND the headers so ROW1 will be the headers and ROW2 is the first line of data. I think you want ... INDEX( OriginalData, MATCH(...
Then COLUMN(OriginalData[[#Headers],[V1(2019)]]) has a couple things.
a) if you just need COLUMN() of it then just OriginalData[V1(2019)] should be enough since that returns that column of data. by adding [#Headers],[V1(2019)] you are calling out that specific header cell, which is fine but not necessary for this purpose.
b) COLUMN() returns the WORKSHEET column NOT the column number in the table. most likely the table starts in Column A and therefor it doesn't matter, but if not you need to offset that amount by the beginning column of the table. For Example in a general case: COLUMN(Table1[[Column1]])-@COLUMN(Table1)+1
c) So IF you use the little drag box to fill to the right then the table column will increment accordingly for the V1 but will also increment for the column [id] and you don't want that so either use [ID}:[ID] to trick it into staying locked or use something like
MATCH(C$1, OriginalData[#Headers],0)
Where C$1 is the header of the new table and in this case would have V1(2019)
Then instead of the drag fill option use the copy paste option to copy to the cells to the right and the table references won't change but the cell reference C$1 will move.
I hope that helps
BTW, if you are using Excel 365 then I would recommend using FILTER, XMATCH, and other newer functions.