Find last row and return value

**Bramjonker** · 06-15-2012, 07:28 AM

I hope someone can help me with this, have been searching forums for hours!

I have a sheet with customer data. I need a formula to find the last value entered in a column.
In column D new data (order#) is being entered. This order# contains a number with text (for example 2012040021A041)
I need a formula that looksup the last order number in column D.

let's say these are the order# in column D
2012050029A035
2012050028A036
2012040018A037
2012040014A038
2012050029A039
2012050030A040
2012040021A041

What formula do I place in E1 which displays the last order#? in this case 2012040021A041

Hope you can help me and thank you in advance

**zbor** · 06-15-2012, 07:32 AM

Try: =LOOKUP(REPT("Z";255);D:D)

(Replace ; with , if getting error)

Edit: You can not have only numbers in a column or it will return last TEXT value

**outofthehat** · 06-15-2012, 07:33 AM

This formula assumes you have no blank rows above to column title in column D:
=INDEX(H:H,COUNTA(H:H))
If you have blank rows, change it to
=INDEX(H:H,COUNTA(H:H))+<number of blank rows>

Edit - move the closing bracket, and reference column D!
=INDEX(D:D,COUNTA(D:D))
or
=INDEX(D:D,COUNTA(D:D)+<number of blank rows>)

**Bramjonker** · 06-15-2012, 07:42 AM

Thanks guys for helping me! in both solutions I get the following error:

#NAME?

I also tried to change ; into ,
Didn't help.... any other suggestions? Really appreciate your help

**zbor** · 06-15-2012, 07:44 AM

Did you really change both?

=LOOKUP(REPT("Z",255),D:D)

**Bramjonker** · 06-15-2012, 07:47 AM

I made a mistake, sorry, i changed it and nothing came back. But this is due to the blank rows included. This will be the case, as there a blank rows with a formula inside, so what do I add to the formula?

=LOOKUP(REPT("Z";255);D:D)