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)`

2 comments on “Tom’s Tutorials for Excel: Locating the Last Value in a List”
1. Tiffany says:

Is there are way to display the last non-zero item on a list?

• Tom Urtis says:

Thanks for following my page.

Try for example:
=LOOKUP(2,1/(A1:A10<>0),A1:A10)