# 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!

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

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

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.....

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

Great solution, thanks very much!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1