Learn How to Use the Equals Last Cell with Info Formula in Excel with Examples

Sometimes, we may need to find the last cell with a value in a column or row in Excel. This can be useful for various purposes, such as finding the end of a data range, calculating the total number of entries, or referencing the latest value in a series. In this article, we will show you how to use different formulas to find the last cell with a value in a column or row in Excel.

The basic idea behind finding the last cell with a value in a column or row is to use a function that can look up a value in a range and return the corresponding cell reference or value. There are several functions that can do this, such as LOOKUP, INDEX, MATCH, OFFSET, and INDIRECT. We will explain how to use each of these functions in the following sections.

Finding the Last Cell with a Value in a Column

Suppose we have a data set like this:

We want to find the last cell with a value in the Age column, which is C8. Here are some formulas that can do this:

Using LOOKUP Function

The LOOKUP function can look up a value in a range and return the corresponding value from another range. The syntax of the LOOKUP function is:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

where:

• lookup_value is the value to look up in the lookup_vector.
• lookup_vector is a range of cells that contains the values to look up.
• result_vector is an optional range of cells that contains the values to return. If omitted, the lookup_vector is used as the result_vector.

To use the LOOKUP function to find the last cell with a value in a column, we can use a formula like this:

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

This formula works as follows:

• C:C<>"" checks the whole column C 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/ 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.
• 2 is the lookup_value that the LOOKUP function tries to find in the list of values produced in the previous step. Since it cannot find the number 2, it looks for the next largest value, which is 1. It searches this value starting from the end of the list and going to the start of the list. The process stops when it finds the first match. This will be the last cell in the range that contains a value, in the previous step that was turned into 1.
• C:C is the result_vector that the LOOKUP function uses to return the value of the cell that matches the lookup_value. In this case, it returns the value of the last cell with a value in column C.

The result of this formula is 37, which is the value of cell C8.

Using INDEX and MATCH Functions

The INDEX function can return the value of a cell in a range based on its row and column numbers. 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 return.
• row_num is the row number of the cell in the array to return.
• column_num is an optional argument that specifies the column number of the cell in the array to return. If omitted, the array is assumed to be a one-column range.

The MATCH function can return the relative position of a value in a range. The syntax of the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

where:

• lookup_value is the value to look up in the lookup_array.
• lookup_array is the range of cells that contains the values to look up.
• match_type is an optional argument that specifies how to match the lookup_value with the values in the lookup_array. It can be one of the following values:
  • 1 or omitted: The MATCH function finds the largest value that is less than or equal to the lookup_value. The lookup_array must be sorted in ascending order.
  • 0: The MATCH function finds the exact match of the lookup_value. The lookup_array can be in any order.
  • -1: The MATCH function finds the smallest value that is greater than or equal to the lookup_value. The lookup_array must be sorted in descending order.

To use the INDEX and MATCH functions to find the last cell with a value in a column, we can use a formula like this:

=INDEX(C:C,MATCH(MAX(C:C)+1,C:C,1))

This formula works as follows:

• MAX(C:C) returns the maximum value in column C, which is 37.
• MAX(C:C)+1 adds 1 to the maximum value, which is 38.
• MATCH(MAX(C:C)+1,C:C,1) uses the MATCH function to find the relative position of 38 in column C, using the match type 1. Since there is no value in column C that is less than or equal to 38, the MATCH function returns the position of the last value in the column, which is 8.
• INDEX(C:C,MATCH(MAX(C:C)+1,C:C,1)) uses the INDEX function to return the value of the cell in column C that has the row number 8, which is C8.

The result of this formula is 37, which is the value of cell C8.

Using OFFSET Function

The OFFSET function can return the value of a cell that is a specified number of rows and columns away from a reference cell. The syntax of the OFFSET function is:

=OFFSET(reference, rows, cols, [height], [width])

where:

• reference is the cell or range of cells from which to offset.
• rows is the number of rows to move up or down from the reference. A positive value means moving down, and a negative value means moving up.
• cols is the number of columns to move left or right from the reference. A positive value means moving right, and a negative value means moving left.
• height is an optional argument that specifies the height of the range to return. If omitted, the height is the same as the reference.
• width is an optional argument that specifies the width of the range to return. If omitted, the width is the same as the reference.

To use the OFFSET function to find the last cell with a value in a column, we can use a formula like this:

=OFFSET(C1,COUNTA(C:C)-1,0)

This formula works as follows:

• COUNTA(C:C) counts the number of non-empty cells in column C, which is 8.
• COUNTA(C:C)-1 subtracts 1 from the count, which is 7.
• OFFSET(C1,COUNTA(C:C)-1,0) uses the OFFSET function to return the value of the cell that is 7 rows down and 0 columns right from cell C1, which is C8.

The result of this formula is 37, which is the value of cell C8.

Finding the Last Cell with a Value in a Row

Suppose we have a data set like this:

We want to find the last cell with a value in the Sales row, which is M2. Here are some formulas that can do this:

Using LOOKUP Function

We can use the same LOOKUP function that we used for finding the last cell with a value in a column, but with a different range. The formula is:

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

This formula works as follows:

• 2:2<>"" checks the whole row 2 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/ 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.
• 2 is the lookup_value that the LOOKUP function tries to find in the list of values produced in the previous step. Since it cannot find the number 2, it looks for the next largest value, which is 1. It searches this value starting from the end of the list and going to the start of the list. The process stops when it finds the first match. This will be the last cell in the range that contains a value, in the previous step that was turned into 1.
• 2:2 is the result_vector that the LOOKUP function uses to return the value of the cell that matches the lookup_value. In this case, it returns the value of the last cell with a value in row 2.

The result of this formula is 4000, which is the value of cell M2.

Using INDEX and MATCH Functions

We can also use the INDEX and MATCH functions to find the last cell with a value in a row, similar to how we used them for finding the last cell with a value in a column. The formula is:

=INDEX(2:2,MATCH(MAX(2:2)+1,2:2,1))

This formula works as follows:

• MAX(2:2) returns the maximum value in row 2, which is 4000.
• MAX(2:2)+1 adds 1 to the maximum value, which is 4001.
• MATCH(MAX(2:2)+1,2:2,1) uses the MATCH function to find the relative position of 4001 in row 2, using the match type 1. Since there is no value in row 2 that is less than or equal to 4001, the MATCH function returns the position of the last value in the row, which is 13.
• INDEX(2:2,MATCH(MAX(2:2)+1,2:2,1)) uses the INDEX function to return the value of the cell in row 2 that has the column number 13, which is M2.

The result of this formula is 4000, which is the value of cell M2.

Using OFFSET Function

Another function that we can use to find the last cell with a value in a row is the OFFSET function, which we also used for finding the last cell with a value in a column. The formula is:

=OFFSET(A2,0,COUNTA(2:2)-1)

This formula works as follows:

• COUNTA(2:2) counts the number of non-empty cells in row 2, which is 13.
• COUNTA(2:2)-1 subtracts 1 from the count, which is 12.
• OFFSET(A2,0,COUNTA(2:2)-1) uses the OFFSET function to return the value of the cell that is 0 rows down and 12 columns right from cell A2, which is M2.

The result of this formula is 4000, which is the value of cell M2.