Finding the Last Value in a Column in Excel

Sometimes, we may need to find the last value in a column that contains data in Excel. This can be useful for various purposes, such as analyzing the most recent data, finding the end of a list, or extracting the last entry from a column. In this article, we will learn how to find the last value in a column in Excel using different methods, such as formulas, functions, and VBA code. We will also explain the basic theory behind each method and provide a detailed example with real data.

Method 1: Using the LOOKUP Function

One of the simplest ways to find the last value in a column in Excel is to use the LOOKUP function. The LOOKUP function can search for a value in a range of cells and return the corresponding value from another range of cells. The syntax of the LOOKUP function is:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

where:

• lookup_value is the value that you want to find in the lookup_vector.
• lookup_vector is the range of cells that contains the values to be searched.
• result_vector is the range of cells that contains the values to be returned. If omitted, the lookup_vector is used as the result_vector.

To find the last value in a column using the LOOKUP function, we can use the following formula:

=LOOKUP(2,1/(column<>""),column)

where column is the column that contains the data. This formula works by using the following logic:

• column<>"" checks the whole column for empty cells and returns TRUE or FALSE for each cell. If the cell is not empty, it returns TRUE, otherwise, it returns FALSE.
• 1/(column<>"") performs a division operation. It divides 1 by the value from the previous step, which may be TRUE or FALSE. If TRUE, the result is 1, and if FALSE, the result is an error (#DIV/0!) because we cannot divide any number by zero.
• LOOKUP(2,1/(column<>""),column) looks for the value 2 in the list of values produced in the previous step. Since it cannot find the value 2, it looks for the next largest value, which is 1. It searches for this value starting from the end of the list and moving to the beginning of the list. The process stops when it finds the first occurrence of 1. This is the last cell in the column that contains a value. The formula then returns the value from the same cell in the column range.

Example

Suppose we have a column of sales data in Excel, as shown below:

To find the last value in this column, we can use the formula:

=LOOKUP(2,1/(A:A<>""),A:A)

where A:A is the column that contains the sales data. The formula returns 400, which is the last value in the column, as shown below:

Method 2: Using the INDEX and COUNTA Functions

Another way to find the last value in a column in Excel is to use the INDEX and COUNTA functions. The INDEX function can return the value or reference of a cell at the intersection of a specific row and column within a given range. The syntax of the INDEX function is:

=INDEX(array, row_num, [column_num])

where:

• array is the range of cells that contains the values to be returned.
• row_num is the row number within the array from which to return a value.
• column_num is the column number within the array from which to return a value. If omitted, the array must be a one-dimensional range.

The COUNTA function can count the number of cells that are not empty within a specific range. The syntax of the COUNTA function is:

=COUNTA(value1, [value2], ...)

where:

• value1, value2, … are the values or ranges of cells to be counted.

To find the last value in a column using the INDEX and COUNTA functions, we can use the following formula:

=INDEX(column,COUNTA(column))

where column is the column that contains the data. This formula works by using the following logic:

• COUNTA(column) counts the number of cells that are not empty in the column range. This returns the row number of the last cell that contains a value in the column.
• INDEX(column,COUNTA(column)) returns the value from the column range at the row number obtained from the previous step. This is the last value in the column.

Example

Using the same sales data as before, we can use the formula:

=INDEX(A:A,COUNTA(A:A))

where A:A is the column that contains the sales data. The formula returns 400, which is the last value in the column, as shown below:

Method 3: Using VBA Code

The third method to find the last value in a column in Excel is to use VBA code. VBA is a programming language that allows us to automate tasks and customize Excel. To use VBA code, we need to open the Visual Basic Editor (VBE) by pressing Alt + F11 on the keyboard. Then, we need to insert a new module by clicking Insert > Module on the menu bar. In the module window, we can write the VBA code that we want to use.

To find the last value in a column using VBA code, we can use the following code:

Function LastValueInColumn(column As Range) As Variant
    Dim lastRow As Long
    lastRow = column.Cells(column.Rows.Count, 1).End(xlUp).Row
    LastValueInColumn = column.Cells(lastRow, 1).Value
End Function

This code defines a custom function called LastValueInColumn that takes a column range as an argument and returns the last value in that column as a result. The code works by using the following logic:

• column.Cells(column.Rows.Count, 1).End(xlUp).Row finds the last row number in the column range that contains a value. It does this by starting from the bottom of the column and moving up until it finds a non-empty cell.
• column.Cells(lastRow, 1).Value returns the value from the column range at the last row number obtained from the previous step. This is the last value in the column.

After writing the code, we can save the module and close the VBE. Then, we can use the custom function in our worksheet as any other Excel function.

Example

Using the same sales data as before, we can use the formula:

=LastValueInColumn(A:A)

where A:A is the column that contains the sales data. The formula returns 400, which is the last value in the column, as shown below: