How do I use the INDEX or OFFSET functions in Google Spreadsheets?

Question

I have my spreadsheet set up as follows: first 10 columns full of descriptive data followed by 24 columns of month data (2 columns per month).

The last 24 columns are static and the values are updated manually. I may add columns between the first 10 columns as needed.

So one of the columns in the first 10 is called Current Price and each month section also has one column called Price.

What I want to do is have the Current Price column updated automatically depending on the current date.

I created a named range and have this formula =COLUMN(VendorsByMonth)+(MONTH(NOW())-1)*2+1 to get the index of the Price column of the current month.

But I'm not sure how to apply the INDEX or OFFSET functions to get and set the cell value.

Answer 1

The answer is this:

INDEX(RANGE,ROW,COL)

For example:

INDEX(B2:K4,1,5) the range has three rows (From 2 to 4). The 1 is row 1 within the range which is row 2 of the whole spreadsheet. The 5 is column 5 within the range which corresponds to Column F (the 6th column of the spreadsheet).