In this Excel tutorial, you will learn how to create a formula which returns the last value in a column.
This tutorial is useful when you have a column of data and sometimes you add some data there. In this case you can easy to return the last value to some cell you want.
The Index Match formula to get the last value
Use the INDEX MATCH functions combinations for that purpose. To return the last value from the last cell in the A column just use this Excel formula: =INDEX(A:A;MATCH(MAX(A:A);A:A;1))
This is an array formula so to execute it you should use this keyboard keys: CTRL + SHIFT + ENTER.
Tip: This formula also works when you have some empty cells in the column.
The Lookup formula to get the last value
This method uses the LOOKUP function to return the last value in a column. The LOOKUP function returns the last value in a range that matches a specified value. Here’s how to use this method:
- Enter the following formula into a cell: =LOOKUP(2,1/(A1:A100<>””),A1:A100)
- Replace A1:A100 with the range that contains the column you want to retrieve the last value from.
- The formula creates an array of 1s and 0s that corresponds to the cells in the range. The 1s correspond to non-empty cells, and the 0s correspond to empty cells.
- The LOOKUP function then searches for the last 1 in the array and returns the value in the corresponding cell in the range.
The Index Match formula to get the last value
This method uses the MAX function to return the last value in a column. The MAX function returns the largest value in a range. Here’s how to use this method:
- Enter the following formula into a cell: =MAX(A1:A100)
- Replace A1:A100 with the range that contains the column you want to retrieve the last value from.
- The MAX function returns the largest value in the range, which is the last value in the column if the values are sorted in ascending order.
