Excel Generating a table based on positions in another table

Question

I have a forecast for a number of countries, and I want to simulate how different launch years for these countries will impact the total sales for each year.

Say I have these forecasts for each market (Denmark, Norway and Finland):

What I would like to achieve, is to be set the launch year in a setup table like this:

And then finally end with a table, where each country's forecast is repositioned according to the launch year I define in the previous setting. This would ultimately look like this, based on my example:

I.e. the first year of Denmark's forecast is shown in year one (based on the input from cell H2), the first year of Norway's forecast is shown in year 3 (based on the input from cell H3) and vice versa for Finland.

So with this in hand, the last table (in A8:E11) will update when I change the inputs for launch year in each country.

Do you know of a good way to do this in Excel?

Answer 1

As @JosephMason said. INDEX/MATCH will return your figures.

Simple version:

In cell B9 in your example:
=IF(B$8-$H2+1<=0,0,INDEX($B$2:$E$4,1,B$8-$H2+1))

Cell B10:
=IF(B$8-$H3+1<=0,0,INDEX($B$2:$E$4,2,B$8-$H3+1))

Cell B11:
=IF(B$8-$H4+1<=0,0,INDEX($B$2:$E$4,3,B$8-$H4+1))

---

Slightly more complex version:

So you don't need to change the row number in the INDEX function (entered in B9 and dragged across/down):
=IF(B$8-$H2+1<=0,0,INDEX($B$2:$E$4,MATCH($A9,$A$2:$A$4,0),B$8-$H2+1))

---

Looks it all up version:

So the order of countries in G2:G4 doesn't matter:
=IF(B$8-INDEX($H$2:$H$4,MATCH($A9,$G$2:$G$4,0))+1<=0,0,INDEX($B$2:$E$4,MATCH($A9,$A$2:$A$4,0),B$8-INDEX($H$2:$H$4,MATCH($A9,$G$2:$G$4,0))+1))

Answer 2

The simplest way to do this that I can think of now is via pivot.

First you will need to redimension this table

via pivot to this by sum or max + tabular format + repeat item labels + off all grand totals + rename value from sum of 1 to 1 (add a space)

Copy and paste the pivot as value into a new range and add in additional column called "new seq", use the formula below for "new seq" column which references to the launch year table next to it.

=INDEX(G$2:G$4,MATCH(A2,F$2:F$4,0))+NUMBERVALUE(B2)-1

Next, just pivot the new range to get the final result.

Just change the launch year value in launch year table and refresh pivot to see the values change.

Answer 3

I would use an INDEX function with a MATCH function for the row referencing on the country, and another MATCH function for the year column referencing off your input.