Excel - Return cell data with multiple variables

Question

I was given a spreadsheet that I need to reformat. I'm hoping there's a formula (INDEX + MATCH? HLOOKUP? XLOOKUP?) to make this simpler, but I can't figure out what it would be.

The source document has most of the variables in rows 1 and 2, with one variable in column A. I need to reformat it so that row 1 also becomes a column. Here's some screenshot examples with made-up information:

Source Document looks like this (with actual data in all the cells, I left them blank in my example)

I need the countries to be in a column and the years as only three columns, like this

Is there a way to do this? Transposing the data doesn't give me the results I want, so I think it needs to be a combination of some form of INDEX and MATCH with multiple variables. Any ideas are greatly appreciated!

Answer 1

This one? Could you share some test data on it and show an expected result.

=SUM((Sheet1!$B$1:$G$1=$B11)*(Sheet1!$A$3:$A$5=$A11)*(Sheet1!$B$2:$G$2=C$10)*(Sheet1!$B$3:$G$5))

Answer 2

Alternatively:

In C10: =B2

Fill to the right to E10.

In A11: =INDEX(A:A, MOD(ROW()-2, 3)+3)

In B11: =INDEX($1:$1, ROW()-9)

Fill both down as far as needed.

In C11: =INDEX($1:$5, MOD(ROW()-2, 3)+3, 3*QUOTIENT(ROW()-2, 3)+COLUMN()-10)

Fill to the right to E11, then down as far as needed.