# Thread: Last Value in range (X2K)

1. ## Last Value in range (X2K)

I am looking for a formula that will give me the last value in a column of figures. I am sure this has been posted before but I could not find it in search <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

Many Thanks

Peter

2. ## Re: Last Value in range (X2K)

For instance, to find the value of the last used cell in column A, use this formula:

=OFFSET(A2,MATCH(9.99999999999999E+307,A2:A65536), 0)

I found this formula by Rory in <post#=168137>post 168137</post#> by searching for "last cell column", search option "And".

3. ## Re: Last Value in range (X2K)

Thanks Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

4. ## Re: Last Value in range (X2K)

How about the last numerical (as opposed to text) value in a row?

0

5. ## Re: Last Value in range (X2K)

Try something like:
=OFFSET(A2,0,MATCH(9.99999999999999E+307,2:2)-1)
if your formula is in A1. You will need to adjust the cell and row numbers according to where your formula is and which row you're looking in. (It's basically the same formula as for columns, but swapping the 0 and the MATCH expression round in the OFFSET function)
HTH.

6. ## Re: Last Value in range (X2K)

You don't need volatile OFFSET...

Either

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

or

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

7. ## Re: Last Value in range (X2K)

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

or

=INDEX(2:2,MATCH(9.99999999999999E+307,2:2)

will return the last numeric value (a number, a date, or a time value).

The formula is also applicable to definite ranges...

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