# Combining a VLOOKUP and PRODUCT function

1. ## Combining a VLOOKUP and PRODUCT function

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!  Register To Reply

2. ## Re: Combining a VLOOKUP and PRODUCT function

Try like this

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

confirmed with CTRL+SHIFT+ENTER  Register To Reply

3. ## Re: Combining a VLOOKUP and PRODUCT function

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  Register To Reply

4. ## Re: Combining a VLOOKUP and PRODUCT function

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.....  Register To Reply

5. ## Re: Combining a VLOOKUP and PRODUCT function

Great solution, thanks very much!  Register To Reply