I have my monthly sales in my Excel sheet below. We have sales until March 2016.  I always want to return the value in Column B to be the most most recent months sales.

There are a few way to do this but I have found using INDEX and COUNT the most effective and simple.

INDEX is a useful function. It will return the value of a cell at the intersection of a row and column in a range of cells.  It has 3 arguments.

array-A range of cells.
row_number The row number in the array to use to return the value.
column_number- Optional. It is the column number in the array to use to return the value.

So in this case if we type the following formula

=INDEX(C2:I2,1)

We get 25000 as the result as we are using the array of cells C2 to I2, and only one row, but if we dont specify a column then Excel defaults to 1(this is optional) which correctly returns the first row and column in our aray of cells. So that is cool, but we want Excel to always return the last populated cell in our row, this where we can specify the column_number argument to use the COUNT function.

COUNT will count cells that contain numbers, so by using COUNT as the column_number argument we can instruct Excel to count along as many cells that contain a number.  Cool huh?.

The formula looks like this-

… and we get the result 38,950. The formula has used the COUNT formula to move along 3 columns in the row as we have 3 cells that contain numbers.

We can then just populate our data set by dragging the formula or double clicking to populate the formula to other cells in the data range we need to.  JOB DONE.