Fetching value from another sheet for next 9 months if the month changes

[dineshiam]

Hi,

I am looking for a formula in which i need to select a month in cell G3 and months for TYPE 1 and TYPE 2 should change in G6 and G14. Next months should follow the sequence.

Once months are changed, it should fetch respective value from REFERENCE sheet through index match or vlookup.

Thank you,

[Bo_Ry]

Please try at G6

=EDATE($G$3,COLUMNS($G6:G6)-1)

[dineshiam]

Thanks, it works for changing the month in G6, but if i select november in G3, so months in ROW 6 should become November 2020 to July 2021.

and their respective value for each type in that month should be fetched from REFERENCE sheet.

[bluesky63]

Hi,

G6 and G14 both = G3,
H6, =EDATE(G6,1) copy all to the right
H14, =EDATE(G14,1) copy all to the right


then use Index, Match (Horizontal), Match (vertical)

At G7, drag right and down the following formula

=IF(INDEX(Reference!$E$5:$R$10,MATCH($F7,Reference!$D$5:$D$10), MATCH(G$6,Reference!$E$4:$R$4))=0, "",INDEX(Reference!$E$5:$R$10,MATCH($F7,Reference!$D$5:$D$10), MATCH(G$6,Reference!$E$4:$R$4)))

At G15 drag right and down the following formula
=IF(INDEX(Reference!$E$13:$R$15,MATCH($F15,Reference!$D$13:$D$15), MATCH(G$14,Reference!$E$12:$R$12))=0,"",INDEX(Reference!$E$13:$R$15,MATCH($F15,Reference!$D$13:$D$15), MATCH(G$14,Reference!$E$12:$R$12)))