Look Up the Last Value in Column/Row in Excel

Very often we have to find out the last value in a column or in a row. When there are so many ways available in the internet, they all have a common issue – too long and complicated.

If the formula is too complicated, it is easy for the users to make mistakes. Sometimes, they may not even know how to edit the formula. That’s why in most of the situations we prefer simple and short formula.

In this article, I will show you how to get the last text value/ numeric value in a column/row using a short and simple formula.

Sample Workbook

In this example, there are 4 columns and 11 rows.

Each cell contain either a text value or a numeric value.

Now, we want to find out 4 pieces of information, which are:

The last numeric value in column A
The last text value in column A
The last numeric value in row 3
The last text value in row 3

Download the workbook to practice it by yourself!

Press the download button!

Expected outcome

The answers are at column F.

This is what we will be able to get at the end of this tutorial.

Formula

Formula of F2

=LOOKUP(1E+100,A:A)

Formula of F2

=LOOKUP("zzzzz",A:A)

Formula of F2

=LOOKUP(1E+100,3:3)

Formula of F2

=LOOKUP("zzzzz",3:3)

Why the formula work

1E+100 is the scientific notation of 1 followed by a hundred of 0s. Actually it doesn’t has to be 1E+100, it can be any big number. Since it’s nearly impossible you have such a cell, the formula won’t be able to return it. Instead, it will return the last numeric value it found. This is the default behaviour for LOOKUP function – approximate look up.

The concept of “zzzz” is similar. “zzzz” represents a very large text. The formula will return the last text value since it couldn’t find “zzzz”.

How to use the formula

If you want to find the last numeric value, put 1E+100 as the first parameter.

If you want to find the last text value, put “zzzzz” as the first parameter. Make sure you don’t forget the double quote.

Put a column /row number as the second parameter.