Need HLOOKUP to return an array so that I can use it with SUMPRODUCT + some compounding growth

[sykinc]

Hi All,
I am trying to use HLOOKUP to find a column and then return the entire array within that column, so that I can combine it with SUMPRODUCT from another "static column".

To make it more difficult, as I work to the right of my spread sheet I will eventually need to grow each figure in that "static column" by a growth rate each year. Is there a way to incorporate this into the above equation?

To be more clear, I have two tabs. The first tab has 5 columns representing Years 1 through 5. In each year, I have ~10 rows for Job positions - assistant, analyst, president etc. Each year, the number of Jobs changes (grows, shrinks, whatever), so 1 president, 2 analysts etc. Let's call this the Annual Positions table. Still on this first tab, I have a 6th column which is the corresponding salary for each position. I also have a 7th column that is the annual growth rate for that salary.

In the second tab, I have MONTHLY projections, so 5 years times 12 months = # of columns. Above each month, I've labeled the Year it represents. On this tab, I have a row for "Salaries". Since this tab is Monthly, I want to grab the Year the month falls under (so, the 13th column will be January in YEAR 2). I then want to HLOOKUP the Annual Positions table from the first tab, grab the whole array underneath the reference year, and then SUMPRODUCT it with the Salary data from Column 6 on Tab 1. As mentioned, I also need to find a way to grow each of the salary rows by the % growth rate in column 7 of Tab 1. So as I get to the 13th row in Tab 2 (or 13th month) each salary needs to grow by % before being SUMPRODUCT with number of positions. In the 25th row (January of Year 3), the growth rate needs to be compounded to account for it being 2 years forward.

This might sound confusing, but please let me know if you have any questions. I appreciate the help. I've been banging my head against the desk. Thanks in advance!

 

[aaronbens]

I am unsure what you are asking about with the growth rate, but to do a Sumproduct with a lookup on one of the ranges you could use a formula similar to:

=SUMPRODUCT($A$2:$A$4,INDEX($E$2:$G$4,,MATCH($C$2,$E$1:$G$1,0)))
$A$2:$A$4 = this is the static range
$E$2:$G$4 = is the range you are pulling the values to product with
$C$2 = is the lookup value
$E$1:$G$1 = is the lookup range for the previous value

 

[sykinc]

This works great, thank you so much!!!

With regards to the growth rate, the static range ($A$2:$A$4 in your example) needs to grow by 3% each year. So, if the numbers in that column are 3, 7, 10 in year one (the static range in excel), then in year two they would be 3.09, 7.21, 10.3 and year three 3.18, 7.43, 10.6. That is, they get compounded. I have another static range column that has a corresponding "growth rate" for each data point in the first static range column.

Using your formula, in my mind, I would imagine maybe a sumproduct within that sumproduct? But, I don't know how this would functionally be expressed in excel...

You've been a great help already, but please let me know if you have any thoughts on this problem? Thank you so much!!

 

[aaronbens]

OK, I see, try this:

=SUMPRODUCT($A$2:$A$4*((1+$C$4)^A1),INDEX($E$2:$G$4,,MATCH($C$2,$E$1:$G$1,0)))
$A$2:$A$4 = this is the static range
$E$2:$G$4 = is the range you are pulling the values to product with
$C$2 = is the lookup value
$E$1:$G$1 = is the lookup range for the previous value

$C$4 = The growth rate
A1 = the year (1-5)

 

[sykinc]

That works very nicely! To make it more complicated though, what if I need a different growth rate for each row in my first static range? So, for instance, A:2 grows at 3%, A:3 grows at 5%, A:4 grows at 7%? Is there a way to do that exponential multiplier in an ~array type form?? Thank you!

 

[aaronbens]

Updated:

=SUMPRODUCT($A$2:$A$4,(1+($B$2:$B$4))^A1,INDEX($E$2:$G$4,,MATCH($C$2,$E$1:$G$1,0)))
$A$2:$A$4 = this is the static range
$E$2:$G$4 = is the range you are pulling the values to product with
$C$2 = is the lookup value
$E$1:$G$1 = is the lookup range for the previous value

$B$2:$B$4 = the growth rate range in relation to $A$2:$A$4
A1 = the year (1-5)

 

[sykinc]

Awesome, thank you!!! Works perfectly.