Report data from an excel file in another excel file

[Loli]

Hi everyone, 

I am wondering if there is a way to find a data when we only have a reference on the position of that data. I explain: 

I have an excel file with the references below: 

	Year 3	Year 2	Year 1
Salt	(T01, r10, c10)	(T01, r10, c20)	(T01, r10, c30) + (T01, r30, c30)
Pepper	(T01, r20, c10)	(T01, r20, c20)	(T01, r20, c30) + (T01, r30, c30)

In a second excel file, I have the data that I am trying to report on the table above. The format of my second file is as below: 

T01
	c10	c20	c30
r10	22.14	45.2	45.5
r20	23.5	22.6	58.2
r30	41.3	41.3	44.3

Is there a way to report the data from the second table in the first one by only having the references mentioned (T for Table of reference, r for the row, and c for the column)? e.g for example at the end, I would have on the cell (T01, r10, c10) : 22.14

Please note that these are two different excel files.

Thank you for your help,

[mceleavey]

 @Loli ,

can you please provide the data in excel and mock up the example of what you're trying to achieve?

Thanks,

M.

[Loli]

Hi, thanks for replying. Please see attached the files. 

What I am trying to obtain is the following table (e.g complete the table in the "references" excel file by using the data from the excel file named "amounts"): 

	Year 3	Year 2	Year 1
Salt	22.14	45.2	89.8
Pepper	23.5	22.6	102.5

[srvas17]

This might be a oversimplified approach, but what I did was construct the key in the reference table, then lookup those values to find-replace them in the dataset.  Would welcome an additional solution if someone can come up with a more efficient approach.

[srvas17]

Cleaned it up by by re-arranging both tables so that it fits a wider use case, hope this helps!

[Loli]

Thanks very much @srvas17 ! 

@srvas17 @mceleavey , any idea on which tool to use if the data I'm trying to report is included in 2 different sheets within the file? For example, in the following table, columns refer to two different tables T01 and T02 in the attached excel file: 

Year 3	Year 2	Year 1
Salt	(T01, r10, c10)	(T02, r10, c20)

Thank you,