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

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

Thanks for following my page.

Try for example:

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