retrieve last filled value in a column

D

Diane

In column A I have a formula that fills rows 1 - 40,000. Sometimes the
formula returns a value, sometimes not. I would like cell D1 to reflect the
last filled cell in column A, for example if A34000 was the last the last
cell whose formula returned a value - which was DOG then i want cell D1 to
reflect DOG.

Any help would be greatly appreciated.
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Diane

Fantastic! Thank you!

Bob Phillips said:
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
D

Diane

What of I wanted the first in the column?

Bob Phillips said:
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
D

Dave Peterson

One way:

=INDEX(A1:A65535,MATCH(TRUE,A1:A65535<>"",0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.
 
D

Diane

Thank you - worked like a charm!

Dave Peterson said:
One way:

=INDEX(A1:A65535,MATCH(TRUE,A1:A65535<>"",0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.
Click to expand...
 
Top