I don't think these will work, Ron. Wouldn't you need MATCH not LOOKUP?, i.e.

=INDEX(A1:A20,MATCH(2,1/(A1:A20<0)))

confirmed with CTRL+SHIFT+ENTER

"Ron Coderre" wrote:

Actually.....these are shorter:

For the cell address:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20)))

or....to be used in a formula (as you mentioned):
=INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

To deteremine the address of the LAST non-blank, non-zero value above cell A21

Try this:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20))))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Neil Hindry" wrote:

From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use it in
a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!