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.
The Rows and Columns formula to get the last value
Using the ROWS and COLUMNS functions: This method uses the ROWS and COLUMNS functions to return the last value in a column. The ROWS function returns the number of rows in a range, and the COLUMNS function returns the number of columns in a range. Here’s how to use this method:
- Enter the following formula into a cell: =INDEX(A1:A100,ROWS(A1:A100))
- Replace A1:A100 with the range that contains the column you want to retrieve the last value from.
- The ROWS function returns the number of rows in the range, which is the position of the last cell in the column.
- The INDEX function returns the value in the cell at that position, which is the last value in the column.
Regardless of the method you choose, you can return the last value in a column in Excel using these simple steps.
