Extract Data to Template - Headers Change

[k.mak]

Hi All,
This is my first post/inquiry. I am not certain if my inquiry has been resolved before. If so, please do advise where I can read up on it.
If not, here is my question...
I have a "DATA" worksheet that I pull from a source. In this worksheet, the number of columns can change as well as the name of the headers.
As far as rows, the rows can be large number of rows.
Also...on the data file, there are values/cells that are duplicated and unique.

I have a separate worksheet that I call a "Template" that I would like the information from the "DATA" worksheet extracted to.

I've attached a very simplified one tab Excel file showing the DATA and Template(what the expected result will look like).
I've tried VLookUP. I've tried INDEX MATCH. I tried INDEX AGGREGATE, which was way out of my comprehension, and I still can't get the correct results.
Any help is greatly appreciated!

[ORoos]

Hi k.mak,
If the raw DATA is changing in the number of columns you have or the order of the columns, we can work with that by picking the header dynamically as log as there is some logic.
For excample, in your sample file you have 'last_name' in the DATA and 'SURNAME' in your template. Will the 'SURNAME' always be 'last_name' in the DATA sheet or will that vary too (familyname, LastName, main-name...).
Is there any column in the DATA which always will be unique?

[k.mak]

Hi ORoos,
The answer is no. The column headers will not be unique.
I had thought of creating a tab called "categories" in which several columns will have these list of variations.
Therefore, the template will have to look up these variation names, find the column that display these names, then extract the info from each row of this column.
I know it's a crazy task.

[ORoos]

Yep, sounds crazy!
Simplest solution would of course be to get the source data standardised if that is possible at all.
Otherwise, reference tables with all the possible names for each heading would be required to link the data.