# find last value

#### mcarter973

##### Board Regular
is there an excel formula for determining the last value entered in a range (column of data) or can the answer only be achieved thru writing code?

thanks

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
=lookup(9.99999999999999E+307,A:A)

This will return the last numeric entry in column A

thanks cam - that did the trick.

If your data is in say B5:B50 -

To find the last number:

=INDEX(B5:B50,MATCH(9.99999999999999E+307,B5:B50))

or

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

If your range includes text (or a mixture of numbers and text) use:

=INDEX(B5:B50,COUNTA(B5:B50),1)

Regards

Mike

On 2002-08-29 12:31, Ekim wrote:
If your data is in say B5:B50 -

To find the last number:

=INDEX(B5:B50,MATCH(9.99999999999999E+307,B5:B50))

or

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

If your range includes text (or a mixture of numbers and text) use:

=INDEX(B5:B50,COUNTA(B5:B50),1)

Regards

Mike

The last one won't work when you have blanks interspersed with mixed data.
This message was edited by Aladin Akyurek on 2002-08-29 12:42

mcarter973,

If there exist no empty cells in the range then following will work:

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

If there exist empty cells then following array-formula will find the last value:
{=INDEX(A:A,MAX(ROW(1:1000)*(A1:A1000>0)))}

(You use Ctrl+Shift+Enter when entering this formula and XL will add the brackets.)

If You´re not comfortable with array-formulas following non-array formula will also find the last value:
=OFFSET(A1,MATCH(MAX(A1:A1000)+1,A1:A1000)-1,0)

You may need to adjust the width of the range, i e change A1000.

Kind regards,
Dennis

Replies
1
Views
79
Replies
1
Views
237
Replies
5
Views
188
Replies
3
Views
318
Replies
8
Views
560

1,219,819
Messages
6,150,404
Members
450,960
Latest member
GB2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back