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)