Excel: How to Find Last Cell with Value in a Row

You can use the following formula in Excel to find the last cell with a value in a row:

=ADDRESS(1,MATCH(2,1/(1:1<>""),1),4)

This particular formula finds the last cell in row 1 of the spreadsheet that contains a value. For example, this formula might return H1.

If you’d like to return the actual value in the last cell with a value in a row, you can use the following formula:

=OFFSET(A1,0,MATCH(MAX(A1:XFC1)+1,A1:XFC1,1)-1)

This particular formula returns the value in the last cell with a value in row 1. For example, it might return the value 18 if this is the value in the last cell with a value.

The following example shows how to use this formula in practice.

Example: Find Last Cell with Value in a Row in Excel

Suppose we have the following row of values in Excel:

Suppose we would like to find the last cell with a value in row 1.

We can type the following formula into cell A8 to do so :

=ADDRESS(1,MATCH(2,1/(1:1<>""),1),4)

The following screenshot shows how to use this formula in practice:

Excel find last cell with value in row

The formula returns a value of H1, which is the address of the cell that contains the last value in row 1.

If you would instead like to return the actual value in cell H1, you can use the following formula instead:

=OFFSET(A1,0,MATCH(MAX(A1:XFC1)+1,A1:XFC1,1)-1)

The following screenshot shows how to use this formula in practice:

The formula returns a value of 18, which is indeed the last value in row 1.

Note: In the formula, we use A1:XFC1 because XFD1 is the last available column in the spreadsheet. This ensures that we search the entire row for the last value.