Formula to return the last value in a column

You may want to return the value from the last populated cell in a column for a number of reasons. Perhaps you have a dashboard which needs to return the ‘Current Champion’ but it needs to lookup against a dynamic list of champions so you can’t fix to one cell reference.

 

In this example we have all the historic winners of the Tour de France and need a formula to always return the bottom row in the column. So it will lookup to 2016 (cell C107) now but when the 2017 winner is added to cell C108 it will automatically lookup against that.

 

lastvalueincolumn1

 

=LOOKUP(2,1/(C:C<>””),C:C) is the formula used in cell C2. It looks up against all values in column C to see if <>”” is true, if it is true then we know the cell is not blank. So the formula will return the last cell in the range (C:C) where the value is not blank.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *