Match columns in SUMPRODUCT INDEX MATCH formula

[Rainmanne]

I have two worksheets. One has the source table:

And another has an aggregation table:

To get the numbers from the first table to the second I use the following formula:

=SUMPRODUCT(INDEX(FS!$A$2:$Z$250,0,MATCH(C$1,FS!$A$1:$Z$1,0)),--(FS!$B$2:$B$250=$A2))/1000

It basically checks the year in the result table, matches it with the year in the source table to determine the column with the result, then checks Table ID in the result table and matches it with the Table ID in the column B in the source table. If there are multiple entries for the Table ID in the source table, they are summed up.

But now I need to match the column with the Table ID header instead of having a static column B because I need to use the same table to various types of source tables, where Table ID might be in different column.

But I cannot figure out where to put something like

MATCH("Table ID", FS!$A$1:$Z$1,0)

to identify the column.

Could someone help please?

 

[Fluff]

How about

=SUMPRODUCT(INDEX(FS!$A$2:$Z$250,0,MATCH(C$1,FS!$A$1:$Z$1,0)),--(INDEX(FS!$A$2:$Z$250,0,MATCH("Table ID",FS!$A$1:$Z$1,0))=$A2))/1000

 

[Rainmanne]

How about

=SUMPRODUCT(INDEX(FS!$A$2:$Z$250,0,MATCH(C$1,FS!$A$1:$Z$1,0)),--(INDEX(FS!$A$2:$Z$250,0,MATCH("Table ID",FS!$A$1:$Z$1,0))=$A2))/1000

Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.