Dragging Down Skipping Rows

Question

I have spent the last many hours working on this. I have read previous posts, but I still can't resolve my problem. I want to paste the following data into cell G4 after copying a formula there. M4 = Cell G4. G10 = M5, and G16 = M6. M7 in cell G22. each 6th row is skipped, and so on until row 280.

Answer 1

Try this:

IF(IFERROR(MATCH(ROW(),$C$4:$C$12,0),0)>0,INDIRECT("M"&4+IFERROR(IF(ROW()=4,0,MATCH(ROW(),$C$4:$C$12,0)-1),0)),"")

You can also use the help column in C4:C12, which you will need to expand to get to 280, to see whether there is a method to shorten the formulae.