Last value in column

Topic

New Reply

How would I use a formula to find the last value or entry in a column. For example – a simple checkbook register where you keep a running balance. How you you reference that last balance if you wanted to be able to refer to it from another worksheet?

Reply | Quote

Replies

The following formula will return the last numeric value in column A:

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

Reply | Quote

Thank you. Can you explain how that works please?

The following formula will return the last numeric value in column A:

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

Reply | Quote

9.99999999999999E+307 is the largest value one can enter in an Excel sheet.

MATCH without a third argument looks for the largest value in the range that is less than or equal to the search value, searching from top to bottom. Since the search value isn’t found in this case, MATCH returns the last numeric value it encountered.

Reply | Quote

Very clever. Thanks for the explanation.

9.99999999999999E+307 is the largest value one can enter in an Excel sheet.

MATCH without a third argument looks for the largest value in the range that is less than or equal to the search value, searching from top to bottom. Since the search value isn’t found in this case, MATCH returns the last numeric value it encountered.

Reply | Quote

Very clever. Thanks for the explanation.

Hi Don
One point in addition to Hans’ explanation. The MATCH function in this case returns the location of the last and not necessarily the largest entry because it expects the range to be sorted in ascending order.

Reply | Quote