# Thread: Last value in column

1. 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?

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

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

3. Thank you. Can you explain how that works please?

[quote name='HansV' post='771321' date='19-Apr-2009 22:44']The following formula will return the last numeric value in column A:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))[/quote]

4. 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.

5. Very clever. Thanks for the explanation.

[quote name='HansV' post='771325' date='19-Apr-2009 23:01']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.[/quote]

6. [quote name='Don_Sadler' post='771686' date='22-Apr-2009 01:55']Very clever. Thanks for the explanation.[/quote]
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•