Formula - OFFSET, OFFSET-MATCH

An OFFSET formula is used to pull a value (or sum of values). It will use a reference point, and then move up / down and left / right based on your input. An OFFSET formula is written as follows:
=OFFSET(Reference, Rows, Columns, height, width)

If you want to start at cell A3, and move 2 to the right and 2 down (cell C5), your formula would be:
=OFFSET(A3, 2, 2)

In pretty much ever case, you are not going to have just fixed numbers to move over. Otherwise, you would just want to select that cell - there would be no need to OFFSET. Generally, you will use a MATCH at least once. So, for example, let's say you have a Summary tab that pulls off of Sheet2. On Sheet2, cells C1:N1 are the month-end dates for the year. In row 2 under the dates are the SUM of the data from row 3 down, which is the data is pulled in. On your Summary tab, B1 is the date the Summary is for. In this case, you'll want to pull the Total for the date that matches the date on the Summary Tab. Your formula will be:
=OFFSET(Sheet2!B2, 0, MATCH(Summary!B1, Sheet2!C1:N1, 0))

Formula explained: Sheet2 holds the data and the Summary tab is where the formula is going. In the tab that holds the data (Sheet2), you want to start at cell B2 (the cell just to the left of where the SUMs start). From there, you want it to move 0 rows (stay on row where the SUMs are). You don't have a fixed amount of columns you want it to use - you want it to be based on the date in cell B1 (on the Summary tab). Therefore, you don't want it to move a fixed amount, you want a MATCH. A MATCH formula is written as follows:
=MATCH(lookup_value, lookup_array, match type) -- what to look up, where to look up, and 0 for exact match.
So for the amount of columns we want to move over, we need to MATCH(what = cell B1 (the date), where = Sheet2 C1:N1 (where the dates are), and 0 for exact match)

Just to show you how this makes your life much easier, just change the date on the summary tab. This will change the value to whatever date you're inputting.

I have a few example workbooks for OFFSET-MATCH and OFFSET-MATCH-MATCH (match both rows and columns with certain values. I also have another that's even more complex that pulls based on the value in column A in the Summary tab using INDIRECT. If you would like me to teach you these formulas using an actual Excel sheet, contact me at info@excelandvba.com