Tom’s Tutorials for Excel: Locating the Last Value in a List

Tom’s Tutorials for Excel – Locating the Last Value in a List. Tom Urtis latest tip for you. Happy midsummer! Get ready for fun facts & quick tips as the weekend approaches.
The picture shows a list of numeric and text values in column A.



Last number

The last number in the list is returned by this formula in cell D2:
=LOOKUP(9.99999999999999E+307,A:A)

The row of the last number in the list is returned by this formula in cell E2:
=MATCH(9.99999999999999E+307,A:A)

The address of the last number in the list is returned by this formula in cell F2:
=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)

Last text

The last text value in the list is returned by this formula in cell D3:
=INDEX(A:A,MATCH("*",A:A,-1))

The row of the last text value in the list is returned by this formula in cell E3:
=MATCH(REPT("z",255),A:A)

The address of the last text value in the list is returned by this formula in cell F3:
=ADDRESS(MATCH(REPT("z",255),A:A),1,1)

Last of either number or text (that is, the last item in the list)

The last item in the list is returned by this formula in cell D4:
=INDEX(A:A,COUNTA(A:A))

The row of the last item in the list is returned by this formula in cell E4:
=MAX((ROW($A1:$A1000)*($A1:$A1000<>"")))
Note, this is an array formula, committed to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just with Enter. The curly braces are not entered by the user; Excel places them automatically when the array formula is properly entered.
An alternative formula which is not an array (so it can be entered normally), but which evaluates column A twice to return the correct answer, is
=MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",255),A:A))

The address of the last item in the list is returned by this formula in cell F4:
=ADDRESS(COUNTA($A:$A),COLUMN($A$1),1)