+ Reply to Thread
Results 1 to 5 of 5

Combining a VLOOKUP and PRODUCT function

  1. #1
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    773

    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!
    Last edited by andrewc; 09-11-2009 at 08:03 AM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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. #3
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    773

    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. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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. #5
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    773

    Re: Combining a VLOOKUP and PRODUCT function

    Great solution, thanks very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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