Get Data bettween dates

[swapnilk]

Hi,

So i have some data in the following format

	A	B	C	D	E	F	G	H	I
1	Emp ID	1234
2	Date_1	30-Nov-2016
3
4	From	Pay	Allowance	Total
5	01-Jul-2016	50000	20000	70000
6	01-Jul-2015	40000	10000	50000
7
8
9	RESULT:
10	APR-2016	MAY-2016	Jun-2016	Jul-2016	Aug-2016	Sept-2016	Oct-2016	Nov-2016
11	40000	40000	40000	50000	50000	50000	50000	50000
12	10000	10000	10000	20000	20000	20000	20000	20000
13	50000	50000	50000	70000	70000	70000	70000	70000

<tbody>
</tbody>

In above case, once you enter Emp ID in B1 data is displayed in B2 and A5:D6 using vlookup/array formulas. Now, i want to get the result i.e. A10:H13. A10:H13 displays the data of the last 8 months from the date in B2 based on the information in A5:D6. For e.g. Nov-2016 - 8 months is Apr-2016 so months from Apr-2016 to Nov-2016 are displayed in row 10 and subsequent data is displayed in the rows from 11 to 13.

Can someone help me with the formulas in A10:H13?

Thanks in advance!

 

[Martin2404]

I had a try at this and I believe the following works.

Create A11 to H11 cells with month parameter going from -7 to 0
=EDATE($B$2,-7) this is A11
=EDATE($B$2,0) this is H11

In A12 enter
=INDEX($B$5:$B$6,(IF(ISNA(MATCH(A$11,$A$5:$A$6,-1)),0,MATCH(A$11,$A$5:$A$8,-1)))+1)

In a13 enter
=INDEX($C$5:$C$6,(IF(ISNA(MATCH(A$11,$A$5:$A$6,-1)),0,MATCH(A$11,$A$5:$A$8,-1)))+1)

In A14 enter
=INDEX($D$5:$D$6,(IF(ISNA(MATCH(A$11,$A$5:$A$6,-1)),0,MATCH(A$11,$A$5:$A$8,-1)))+1)

Copy A12-A14 to columns B to H