In a huge table, I need sometimes to know the value of the last cell, no matter if it is in column or in row and without taking care of empty cells. Also it may happen that I need to know in which number, for instance, the last cell of the row 3 or the column A.

When I use the formula ?

To know the value of the last cell.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is/are the formula(s) ?

=LOOKUP()

=ROW()

=COLUMN()

For the column:

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

It will look for the last cell in the column A and display its last value. If you want to display the value of another column, put this one:

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

So it will look in the column A for the last cell then it will display the value located in the column B.

If I want to know the row number of the last cell in column, put this formula:

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

In the other hand, if I want to look for a particular data, I just have to replace “<>""” by “="chocolate"”:

=LOOKUP(2,1/(A:A="chocolate"),ROW(A:A))

So when it will find the word “chocolate” in the column A, it will tell me the row number.

Now, let’s explain for the row:

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

It will look for the last cell in the row 2 and display its last value. If you want to display the value of another row, put this one:

=LOOKUP(2,1/(A2:B2<>""),A3:B3)

So it will look in the row 2 for the last cell then it will display the value located in the row 5.

If I want to know the column number (not the letter) of the last cell in row, put this formula:

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

As for the column, if I am looking for a specific word, I just replace “<>""” by “="chocolate"”:

=LOOKUP(2,1/(A2:B2="chocolate"),COLUMN(A2:B2))