INDEX / Match formulas

Hi,

I have a list of data which I need to index and match into another tab. Unfortunately, I cannot manage the formula as I'm not used to the format of the data list which needs to remain intact.

In the data list, I have a table for each year (highlighted in red) and in each table, I have 8 clients (column A in blue) and a value attributed (column G yellow).
In the result tab, I need to match the data list value to the client in column A and year in row 1

I have tried the below to no avail.

=INDEX('DATA LIST'!A2:A35,MATCH(RESULT!A2,'DATA LIST'!A2:G36,0),MATCH(B1,'DATA LIST'!G3:G10:'DATA LIST'!G15:G22:'DATA LIST'!G27:G34,0))

I have attached a spreadsheet as an example. Any help is highly appreciated.

This is obviously a simplified example so I have some questions.
1. Will the same clients always appear on each table in your data list? or will new ones be added and possibly old ones drop off?

How big is the actual data? I'm thinking of naming each table by year and then using Indirect.

Try:

=INDEX('DATA LIST'!$A$2:$G$35,MATCH(RESULT!B$1,'DATA LIST'!$A$2:$A$35,0)+MATCH($A2,'DATA LIST'!$A$2:$A$35,0)-1,7)

copied across the table.

This assumes the clients are consistent amongst the tables.

Try this formula in B2

=VLOOKUP($A2,OFFSET(INDIRECT(ADDRESS(MATCH(B$1,'DATA LIST'!$A:$A,0),1,,,"DATA LIST")),2,0,8,7),7,FALSE)

You'll need to change that "8" to whatever # of clients you have.
Does that work?

See attached:

NBVC's is more efficient as long as you always have the same clients in the same order on each table.