# Indexation Query

#### broncos347

##### Active Member
I have a question surrounding indexation which someone can hopefully help me with because I can't get my head around what i need to do.

I have a table which holds an annual index figure, the table has two columns one for the year and one for the indexation rate.

I have another table with a base amount, says £100,000, and this amount has a base year of 2016, the sheet also has another column which shows year of construction, e.g. 2020 (this year could change), what i am trying to do is * the £100,000 to bring it up to a figure in 2020, i can do this by simply *ing the base figure by the indexation rates between 2016 and 2020, for example, £100,000*1*1.023*1.026*1.04*1.025 and this would give me the figure i need, what i can't get my head around is how i can automate this so that if the year of construction changes the £100,000 will be adjusted accordingly.

Any help would be appreciated.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### pgc01

##### MrExcel MVP
Hi

Try in B5:

=B2*PRODUCT(IF(D2:D16>=B3,IF(D2:D16<=B4,E2:E16)))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

In this example the result is 100,000*1.04*1.052*1.059*1.046*1.036, the factors from 2016 to 2020

Book1
ABCDE
1
2Amount10000020101.028
3Start201620111.048
4End202020121.034
520131.027
6Result125555.713220141.047
720151.049
820161.040
920171.052
1020181.059
1120191.046
1220201.036
1320211.022
1420221.024
1520231.031
1620241.030
Sheet1

#### broncos347

##### Active Member
brilliant works like a dream, i would never have worked that one out, many thanks.

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

Replies
6
Views
69
Replies
2
Views
104
Replies
6
Views
82
Replies
4
Views
61
Replies
4
Views
97