Create Dynamic Column Lookup Reference based on Table Header

Hi Folks,

In the "Lookup" tab (see attached) I am trying to use an Index & Match formula to return a date from the "Dbase Export" tab, based on the column headings defined in the "Reference" tab.

For example;
On the reference tab, we've assigned the milestone "Milestone_date2" the heading IFS (depending on the project these milestones can change based on client requirements, hence the need for this to be dynamic).

The index & match formula I have so far determines the header to search for. All I need to do now is include a formula that uses this header and converts it to a searchable column array.

Hope this makes sense. The attachement should be pretty self explanatory.

column lookup.xlsx

Thanks for that DBY. It works well but was hoping to not have to use HLOOKUP, is there an alternative that uses index & match instead?
The only reason I ask is that the data on the Dbase Export master file will have several thousand line items and the number of line items will increase on a daily/weekly basis. I don't really want to have to change the size of the HLOOKUP table array every time I import the database export.

The following portion of the formula allows me to find the column in question to search, I just need an Index & Match that can reference this column as an array:

INDEX(reference!$A$1:$A$10,MATCH(B$7,reference!$B$1:$B$10,0))

If DBY's formula doesnt work for you, try this, copied down and across...
=INDEX('Dbase Export'!$B$5:$BO$8,MATCH(Lookup!$A8,'Dbase Export'!$B$5:$B$8,0),MATCH(INDEX(reference!$A$1:$B$10,MATCH(Lookup!B$7,reference!$B$1:$B$10,0),1),'Dbase Export'!$B$5:$BO$5,0))

Thanks for that FDibbins, works like a charm!

Happy to help, and thanks for the rep