• Status: Solved
• Priority: Medium
• Security: Public
• Views: 144

# Formula referencing the results of a multi-column Vlookup

I have a data range along the lines of:-

A                 B                   C                  D               E
Jan-14        Feb 14       Mar 14       Apr 14
1         AAA                1%              2%             1.5%             2%
2        BBB                2%              1%              1%                2%
3        CCC                3%              2%              1.5%             3%
4        DDD               1%              2%              1%               1.5%

I need to calculate the compounded return for various periods for each row. Eg to calculate the return for BBB between Feb-14 and Apr 14 (inclusive) I could use the formula =FVSCHEDULE(1,C2:E2)-1 manually  - However I need the formula to look up the value in column B within the range along the lines of a vlookup function

For example:-

The following vlookup calculates the sum for the selected period:-

={=SUM(VLOOKUP(A2,A1:E4,{3,4,5},FALSE))}

How do I do exactly the same but instead of the sum calculate the compounded return of the selected period?

Thanks !
0
doolinn
1 Solution

Commented:
How about using an offset function?

=FVSCHEDULE(1,offset(\$C\$1:\$E1,match("BBB",\$A\$1:\$A\$4,0)-1,0,,))-1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.