Finding the last value in a row of data is a great technique to learn in Excel and this post will show you how. The method is very useful when creating Excel dashboards and automating any kind of regular Excel report where you always want to return the last value in a row, read on to find out how…
What does finding the last value in a row mean?
The basic principal is that an Excel cell displays the result; the result is the last value in a row of data.
In the example Excel image below there are monthly balances. The monthly balances are in row 2 and in cell A2 is where the Current Balance is displayed, this will always need to be the last value in row 2 because that is the most recent balance:
How to find the last value in a row of data
To calculate the last value in row 2 in the example we enter the following formula into cell A2:
And by doing the last value in row 2, which is currently £850 is displayed:
There is no need to change the formula next month when April’s balance is updated as it will automatically find the value:
How the formula works
To find the last value in a row of data in Excel there are a couple of functions to use.
OFFSET function: The OFFSET function will enable you to move around the Excel worksheet, it takes the format =OFFSET(Reference, Rows, Columns) where the Reference is your starting point and the rows and columns part are values telling Excel how many rows and columns to move down/across.
For example, if we enter the formula = OFFSET(B2,0,2) into our Current Balance example it will tell Excel to start in Cell B2, move down 0 rows and move across 2 columns, this will return £850:
The OFFSET function alone will not achieve the goal of finding the last value in a row of data as each month you would need to update the formula to move across an extra column. This is where the next function comes in, the MATCH function.
Match Function: The MATCH function takes the format =MATCH(Lookup Value, Lookup Array, [Match Type].
The neat trick that can be applied here is to tell Excel to find the highest value in our row of data and +1 to that value, this will become our lookup value. The lookup array is our range of data so that becomes all cells in the row 2 from column B. Finally the match type we will set to 1 which is to find all values less than our lookup value – it sounds a little messy at first but all becomes clear when you break it down.
For example if you look at the MATCH part of the formula on its own it returns the value 3 when we have 3 columns of data:
This is because the formula has found the maximum value in row 2, which is £1250, and then added 1 to that making £1251 and then it looks in row 2 and counts how many values are below that amount which will be 3 values in this case.
If there are more values it will return that new value without changing the formula and it even covers blanks so if a month is missed it will still find the last column of data from B2 onwards:
The MATCH function then forms the columns part of the original OFFSET:
If you would like more information on the OFFSET and MATCH functions there is an earlier post going into a detailed example using them, I highly recommend you have a read through to boost your knowledge of some very cool functions in Excel.