Solved

# Formula referencing the results of a multi-column Vlookup

Posted on 2014-03-11
Medium Priority
141 Views
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
Question by:doolinn
1 Comment

LVL 39

Accepted Solution

nutsch earned 1600 total points
ID: 39920967
How about using an offset function?

=FVSCHEDULE(1,offset(\$C\$1:\$E1,match("BBB",\$A\$1:\$A\$4,0)-1,0,,))-1
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month15 days, 13 hours left to enroll