Finding Cell Address

[Faisal]

I need to get the value of the last cell,where there is a
value in a column range. Can any one help me in writing a
formula for this.The values will be filled from top to
bottom of the column from time to time.
Thanks
Faisal

 

[Jack Schitt]

Until someone posts a more elegant solution, you could do it this way:
Define a named range
LastUsedCellInColD
refers to
=OFFSET(Sheet1!$D$5,COUNTA(Sheet1!$D:$D),0)
(or something similar, row 6 being header row)
Then in your formula
=LastUsedCellInColD

 

[Aladin Akyurek]

I assume that you want the last numerical value...

=LOOKUP(9.99999999999999E+307,A:A)

The cell address of the last numerical value...

=CELL("Address",INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

[Jack Schitt]

That certainly works, but can you please explain why? I find it surprising,
by reference to the Help system on Lookup function.
I tried entering
A1 = 3
A2 = 4
A3 = 5
A4 = 1
A5 = 2
A6 = 1

The formula accurately returns 1 (and varying the value of A6 is accurately
reflected in the returned value).
However, as the function has two arguments I assume it is using the array
form, and per Help system: "If LOOKUP can't find the lookup_value, it uses
the largest value in the array that is less than or equal to lookup_value".
The value 9.99999999999999E307 is not contained anywhere in column A. The
largest value in Column A is 5 (in cell A3). So why does the formula not
return the value 5?

 

[Jack Schitt]

Oh I think I get it. LOOKUP always seems to return the last value when the
array is unsorted. At least, so it seems when I try it out. Not very
helpful Help system ... it warns that the incorrect result will be returned
if the array is not sorted, but if it is a simple algorithm to determine
what result WILL be returned in that event it would take up very little
space to state it.

 

[Norman Jones]

Hi Jack,

For a detailed explanation, see the following old post by Aladin:

http://tinyurl.com/6jna2

 

[Aladin Akyurek]

Jack,

See also my contrib in:

http://tinyurl.com/3nhhw

Aladin

 

[Faisal]

Thankx

-----Original Message-----
I assume that you want the last numerical value...

=LOOKUP(9.99999999999999E+307,A:A)

The cell address of the last numerical value...

=CELL("Address",INDEX(A:A,MATCH (9.99999999999999E+307,A:A)))




.