OweVerthemoon

Question

I HAVE BEEN TRYING TO FIND: WHAT IS THE FORMULA FOR THE LAST ITEM IN A COLUMN

Answer 1

To return the last non-empty value in a column in Excel, use a formula that looks upward from the bottom of the range.

In a standard worksheet range, the most reliable option is INDEX with COUNTA. Example for column A.

=INDEX(A:A,COUNTA(A:A))

This returns the last populated cell in column A as long as the column contains only values you want counted.

If the column may contain blanks and you still want the last used cell, use LOOKUP.

=LOOKUP(2,1/(A:A<>""),A:A)

This scans the entire column and returns the final non-blank entry.

In Microsoft 365, XMATCH with INDEX is another option.

=INDEX(A:A, XMATCH("zzz",A:A))

For numbers instead of text, replace "zzz" with a very large number.

=INDEX(A:A, XMATCH(9.99999999999999E+307, A:A))

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin