Combining a VLOOKUP and PRODUCT function

**andrewc** · 09-11-2009, 06:48 AM

Hi,

I am using the following formula to calulate the compounded rate of return for a series of monthly returns:

{=(PRODUCT(1+(b1:b100)/100)-1)*100}

I would like to be able to integrate a VLOOKUP function into the above formula so that it looks at the dates in column A and then apply the PRODUCT function above to the return values in column B that fall between start and end dates specified in cells C1 and D1

Can someone please suggest a solution.

Thanks!

**daddylonglegs** · 09-11-2009, 07:29 AM

Try like this

=(PRODUCT(1+(IF(A1:A100>=C1,IF(A1:A100<=D1,B1:B100)))/100)-1)*100

confirmed with CTRL+SHIFT+ENTER

**andrewc** · 09-11-2009, 07:41 AM

Thanks!

As I'm going to have lots of columns of numbers to look at, is it possible instead to integrate a VLOOKUP function so that I could change the column to consider simply by altering the Col_Index_Num criterion?

Thanks again

**daddylonglegs** · 09-11-2009, 07:51 AM

You can't return an array of values with VLOOKUP. If you just want to change column B to C or D etc then you can use INDEX like this

=(PRODUCT(1+(IF(A2:A100>=C1,IF(A21:A100<=D1,INDEX(B2:Z100,0,3))))/100)-1)*100

The 3 indicates the 3rd column of B2:Z100, i.e. D2:D100 so you could change that to get the column you want.....or replace the 3 with a cell reference then just change the value in that cell.....