excel - Reference cells with no gaps to fill table with gaps

Question

I have included 2 tables below to illustrate my problem.

Table 1

Table 2

I am trying to find a formula that fills rows 140, 143 & 146 (Table 2) from rows 15,16 & 17 (Table 1). There is over 100 so it is quite time consuming to input =B15 etc over and over again.

The offset method e.g. =OFFSET($B$15,(ROW()-1)*3,0) only works when I'm referencing gaps, not trying to fill them.

Essentially, where B140's formula is =B15, B143's will be =B140 + 1 row i.e. B16

Thanks for your help!

Answer 1

If you are trying find value for appropriate month you can use INDEX/MATCH entered as array formula:

=IFERROR(INDEX($B$1:$B$4,MATCH(TRUE,MONTH(A10)=MONTH($A$1:$A$4),0)),"")

Array formula after editing is confirmed by pressing ctrl + shift + enter

Edit

To find by month & year use:

=IFERROR(INDEX($B$1:$B$4,MATCH(1,(MONTH(A10)=MONTH($A$1:$A$4))*(YEAR(A10)=YEAR($A$1:$A$4)),0)),"")

it's also array formula