Index Match with multiple criteria

[Celia9]

Hello,

I need to have a reference from a table with 2 headers to a different table.

The number of the week and the type of planned work are horizontal in the original file.

In the new table, the number of the week is vertical and the type of planned work horizontal.

In both tables, the area is vertical.

Is there a way to use Index Match for all these criteria?

See attached file please. Thank you!

[#2]

@Celia9 I would recommend that you look into Power Query for these kinds of transformations. The attached file contains a query that you can use over and over again. 

I named the data in the "Original file" tab MyData and loaded it into PQ. Than I did some relatively simple transformations, just by pressing the correct buttons in the user interface. The green table in Planned is what PQ makes of it. No need for complicated formulas.

[#3]

@Celia9 

=INDEX('Original file'!$B$3:$S$34,MATCH([@area],'Original file'!$A$3:$A$34,0),MATCH(1,([@week]='Original file'!$B$1:$S$1)*(Planned!D$1='Original file'!$B$2:$S$2),0))

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.