The most important thing to understand is the OFFSET function. There are lots of great resources out there that have explanations and examples that can be found with Google searches (and check out Excel's help files on functions - they are pretty good). Here is one: https://www.techonthenet.com/excel/formulas/offset.php
The other important piece is the ROW function. When left blank, like ROW(), it simply returns the row number of the cell the formula is found in. We can use this to our advantage, as we populate our formula down a column, it will increment by 1 each time, i.e.
for the first formula, placed in cell C1, ROW() returns 1,
for the second formula, placed in cell C2, ROW() returns 2,
So, you put those two things together to get what you need.