# Excel- any formula to return last number in a column

#### Ebraham

##### Board Regular
Hi.

In sheet one. In cell b20. I want to return last number of column d of sheet2.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try
=LOOKUP(2,1/(sheet2!D:D<>""),sheet2!D:D)

Try
=LOOKUP(2,1/(sheet2!D:D<>""),sheet2!D:D)

Although that will work, it will have to process more than a million cells. Whether the last number happens to be in row 3 or row 1048576 is indifferent; it will still be forced to check every single cell in the column.

=INDEX(Sheet2!D:D,MATCH(9.9E+307,Sheet2!D:D))

is much less resource-heavy.

Regards

Last edited:
thanks for that tip - noted the formula
but not working in my test sheet

thanks for that tip - noted the formula
but not working in my test sheet

Are you referring to the previous formula I had there? Apologies - I amended my post.

Regards

no
=INDEX(Sheet2!D:D,MATCH(9.9E+307,Sheet2!D:D))

I assume we're talking about numbers here, since that's what the OP asked for? And not e.g. numbers formatted as text?

Can you post a small dataset for which that formula does not return the last number in the column?

Regards

my bad - missed the numbers - so works fine

No worries.

Actually, even if the column contains mixed datatypes and the OP wished to return the last non-blank entry, it would still be far better to use e.g.:

=INDEX(Sheet2!D:D,AGGREGATE(14,6,MATCH(CHOOSE({1,2},REPT("z",255),9.9E+307),Sheet2!D:D),1))

than your LOOKUP construction. Despite the length of the above, it will still process far, far more quickly.

Of course, if there are null strings ("") within the column which need to be ignored then this will not be sufficient.

Regards

Actually, even if the column contains mixed datatypes and the OP wished to return the last non-blank entry, it would still be far better to use e.g.:

=INDEX(Sheet2!D:D,AGGREGATE(14,6,MATCH(CHOOSE({1,2},REPT("z",255),9.9E+307),Sheet2!D:D),1))

Provided, of course, that the user is working in XL2010, or later, as that is when the AGGREGATE function was introduced.

Replies
7
Views
215
Replies
2
Views
262
Replies
3
Views
288
Replies
4
Views
302
Replies
3
Views
150

1,219,892
Messages
6,150,814
Members
450,985
Latest member
Andynair7

### 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