Function to return the last value in a row or column.

[.NET Developer]

Hello.

Can anyone please tell me what function returns the value at the end
of a row or column of cells?

For instance, say I have a column with 3 values:

3
55
135

The function in question will return 135.

If I add a few more numbers to the column, like this:

3
55
135
243
533

The function then automatically returns 533.

Thanks very much for any help.

- Robert.

 

[Josie Dethiers]

Maybe something simpler exists but what I would do is:

- Add a column on the left and fill it with a counter ID
1
3

2
55

3
135

4
243

5
533



- Use a VLOOKUP function along with the MAX function

=VLOOKUP(MAX(A:A);A:B;2;FALSE)

Hope it helps
Josie

--+

 

[Josie Dethiers]

Thanks for telling me about this formula. This technique looks better than
mine but I do not see how it works. So I have posted a new thread called
"MATCH last or largest". Have a look if you want more info about this.

Josie

 

[Jarda]

If your data are for exaple in column A, starting in A2, try formula:

=INDEX(A2:A65536,COUNT(A2:A65536))
If your data are not only values; use COUNTA instead of COUNT.

Jarda

 

[Fred]

Try this

=INDEX(A:A,MAX((A1:A65535<>"")*ROW(INDIRECT("1:65535"))))
entered with ctrl+shift&enter

Replacing the " A " column reference with the column
you are using


(e-mail address removed) (Jarda) wrote in