How to find the last row number with formula?

Hello

In column A there is a list which may increase or decrease.Is it possible to find the refference of the last empty row with formula just like the vba
iRow=cells(rows.count,columnindex).end(xlup).row

Best Regards
Imran Bhatti

Yes, it is. Look at these:

=LOOKUP(2,1/(A2:A10<>0),A2:A10)

or:

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

or for text:

=LOOKUP(REPT("z",255),A2:A10)

and for numbers:

=LOOKUP(9.99999999999999E+307,A2:A10)

You can change the ranges to suit.

@Phuocam
=MATCH("zzzzz",A:A)
This working even with blanks.Just we need to be careful while entering the meaningless text like"zzzz" as if this one exists in the range then it will destroy the formula.Any way it is Brilliant.

@Ali

Your formula is returning the value or text of the last row.

Originally Posted by ImranBhatti

@Ali

Your formula is returning the value or text of the last row.

Isn't that what you asked for?

As I said, change the ranges to suit. Use A:A, for example, instead of a specifically defined range.

I was after finding the last row number like A7 A300 etc.
Your formula is worth commendable where someone would like to return the value of the last cell.
I also saved it in my formula sheet for future reference

Thanks for your solution too.

Ah, I see. In that case, try this:

=ROW(LOOKUP(2,1/(A2:A10<>0),A2:A10))

I think a part of the formula is not copied here. formula does not leave the edit mode and says
there is problem with this formula
type
1+1 cell shows 2


etc

Well, never mind - you have a solution from Phuocam.