# VLookup Formula

##### Board Regular
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.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### njimack

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

##### Board Regular
No, it is returning a #N/A

#### BrianMH

##### Well-known Member
Try entering it as an array formula (hit ctrl+shift+enter when entering the formula).

#### njimack

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

##### Board Regular
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

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

##### Board Regular
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.

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

#### njimack

##### Well-known Member
=SUM(OFFSET(INDEX(ArbM!I2:I20,MATCH(41640,ArbM!E2:E20,0)),0,0,7))

Replies
5
Views
172
Replies
4
Views
257
Replies
3
Views
301
Replies
5
Views
358
Replies
2
Views
105

1,195,964
Messages
6,012,592
Members
441,714
Latest member
mcgeesusana

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back