[VBA or excel function]- how to get value from certain cell in row/column match

[cmcamilo]

Hello eveyone,

I have multiple excel files that should "report" to a single one.
Imagine this example:

Excel 1)

Excel 1 is the one that receives the whole information from the other files. In that cell with the green circle it should get the value which has row="6" and column="JAN" on other excel (Excel 2).

Excel 2)

In this excel there is a cell that has exactly the row="6" and column="JAN". The value does not match in the cell number of each excel (in this example, the cell I want to fill in Excel 1 is E21 and in Excel 2 is E20).
Plus, I want this to be dynamic. If I now understand that the KPI I want to show in number 6 is other one I should change the number in Excel 2 and it should be getting that new value in Excel 1.

Was this clear? I'm sorry, english is not my primary language and I understand if it is a little bit confusing.
Thank you.

 

[hajiali]

where Book2 is your Excel 2 and assuming Jan is in Cell "C3" in both excel.

so starting with Cell "C4" try this the drag down and across

=INDEX([Book2]Sheet1!$C$4:$X$100,MATCH($B4,[Book2]Sheet1!$B$4:$B$100,0),MATCH(C$3,[Book2]Sheet1!$C$3:$X$3,0))