VLookup Formula

[greatcookies]

I am using Excel 2013 and would like to have a Vlookup formula look up a certain values and return the sum of it and 6 cells directly beneath. Can this be done? For example, lets say I look up the date 01/01/14 in a table and based off my formula it returns the value 10 from cell b2. The 6 cells below (B3:B8) each contain the value 5, thus I would like it to return the sum of B2:B8 (40). So, basically lookup a value and return the sum of it and the 6 cells below. Hopefully this makes sense. Any help is much appreciated.

 

[njimack]

Does this give you the expected result?
=SUM(OFFSET(INDEX(A2:A100,MATCH(41640,A2:A100,0)),0,0,7))

 

[greatcookies]

No, it is returning a #N/A

 

[BrianMH]

Try entering it as an array formula (hit ctrl+shift+enter when entering the formula).

 

[njimack]

ADVERTISEMENT

You shouldn't need to enter the formula as an array. What does the below return?
=MATCH(41640,A2:A100,0)

 

[greatcookies]

It is still giving the #N/A. Here is my formula:{=Sum(OFFSET(ArbM!E1:I14,MATCH(Product!A7,ArbM!E1:I14,0),0,0,7))} The value in cell A7 is 01/06/14.

 

[njimack]

ADVERTISEMENT

That's not the formula I gave you! Which column are your dates in? Which column are the values to be summed in?

 

[greatcookies]

My lookup value (date) is in a sheet called "Product", cell A7. The range to lookup is in a table called "ArbM" with the dates listed in column E. You could use the range E1:L20.

 

[greatcookies]

Values to be summed are in column I, sheet "ArbM".

 

[njimack]

=SUM(OFFSET(INDEX(ArbM!I2:I20,MATCH(41640,ArbM!E2:E20,0)),0,0,7))