Excel- any formula to return last number in a column

etaf

Well-known Member

Joined

Oct 24, 2012

Messages

8,276

Office Version

1. 365

Platform

1. MacOS

• May 30, 2015

• #2

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

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• May 30, 2015

• #3

etaf said:

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

Click to expand...

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: May 30, 2015

Upvote 0

etaf

Well-known Member

Joined

Oct 24, 2012

Messages

8,276

Office Version

1. 365

Platform

1. MacOS

• May 30, 2015

• #4

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

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• May 30, 2015

• #5

etaf said:

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

Click to expand...

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

Regards

 

Upvote 0

etaf

Well-known Member

Joined

Oct 24, 2012

Messages

8,276

Office Version

1. 365

Platform

1. MacOS

• May 30, 2015

• #6

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

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• May 30, 2015

• #7

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

 

Upvote 0

etaf

Well-known Member

Joined

Oct 24, 2012

Messages

8,276

Office Version

1. 365

Platform

1. MacOS

• May 30, 2015

• #8

my bad - missed the numbers - so works fine

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• May 30, 2015

• #9

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

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• May 30, 2015

• #10

XOR LX said:

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.

Click to expand...

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

 

Upvote 0