Using Vlookup to select a range

[mawallace]

How would you use Vlookup to select a range?

Look at the table below.

i would like to add up the 12 numbers based on a date entered in another cell.

As an example, if cell C1 has a date of 1/11/21 - i would like Vlookup to add up the 12 figures from the range 1/12/20 - 1/11/21 (giving result 1380) - if C1 has a date of 1/9/21 I would like Vlookup to add up the 12 figures from the range 1/10/20 - 1/9/21) and so on (result 1140)

01/08/2020​	20​
01/09/2020​	30​
01/10/2020​	40​
01/11/2020​	50​
01/12/2020​	60​
01/01/2021​	70​
01/02/2021​	80​
01/03/2021​	90​
01/04/2021​	100​
01/05/2021​	110​
01/06/2021​	120​
01/07/2021​	130​
01/08/2021​	140​
01/09/2021​	150​
01/10/2021​	160​
01/11/2021​	170​

 

[hrayani]

Book1
	A	B	C	D
1	DATE	Value	1-Nov-20	1380
2	1-Aug-20	20
3	1-Sep-20	30
4	1-Oct-20	40
5	1-Nov-20	50
6	1-Dec-20	60
7	1-Jan-21	70
8	1-Feb-21	80
9	1-Mar-21	90
10	1-Apr-21	100
11	1-May-21	110
12	1-Jun-21	120
13	1-Jul-21	130
14	1-Aug-21	140
15	1-Sep-21	150
16	1-Oct-21	160
17	1-Nov-21	170
Sheet1

Cell Formulas
Range	Formula
D1	D1	=SUM(OFFSET(INDEX(B2:B17,MATCH(C1,A2:A17,0)),1,0):OFFSET(INDEX(B2:B17,MATCH(C1,A2:A17,0)),12,0))

 

[mawallace]

Thanks - what happens though if the rows had a month missing - say the data had a range of dates, and one (e.g 1/8/21) was missing. Is there a way of saying - select the range 12 months (or nearest) to the date

 

[hrayani]

Thanks - what happens though if the rows had a month missing - say the data had a range of dates, and one (e.g 1/8/21) was missing. Is there a way of saying - select the range 12 months (or nearest) to the date

You are welcome

You can use sumifs with 2 criteria
1st Criteria will add 1 month to the date in cell C1
2nd Criteria will add 12 months to the date in cell C1

=SUMIFS(B2:B17,A2:A17,">="&EDATE(C1,1),A2:A17,"<="&EDATE(C1,12))