Return second to last non blank cell

Good morning all and Happy New Year!

I have a column of figures and want to return the last non-blank cell value, and second-to-last non-blank value.

The first part was easy enough to resolve using:

=LOOKUP(2,1/B5:B35,B5:B35)

Now, silly me, but I thought using an offset function would be a simple was of returning the second-to-last value. However, when I attempted to incorporate it into my original formula:

=OFFSET(LOOKUP(2,1/B5:B35,B5:B35),-1,0)

I get the 'the formula you entered contains an error' message. Am I missing something really obvious here?

Thanks

Steve

Hello,

the lookup function does not return a cell address, but the contents of the cell. That cannot be used as a parameter for the Offset function. Instead of the Lookup, you could use an Index/Match combo like this

=INDEX(B5:B35,MATCH(99^99,B5:B35,1))

Then you can easily manipulate the Match part to return one row above the match:

=INDEX(B5:B35,MATCH(99^99,B5:B35,1)-1)

Maybe

=LOOKUP(99^99,INDIRECT("B5:"&ADDRESS(MATCH(99^99,B:B)-1,2)))

The only problem with the nice lmp's suggestion is if there are empty cells in the range.

A Non-Volatile version hich replaces INDIRECT is this.

=LOOKUP(9.99E+307,B5:INDEX(B:B,MATCH(9.99E+307,B:B)-1))

how about just changing the second range in the original ?

=LOOKUP(2,1/B5:B35,B4:B34)

It's the largest number that Excel can store, Steve. This "Big number" is used to look up the last number in a range. Do a simple Google search and you'll find more details for this.

You are welcome and thanks for the reb*

Originally Posted by daddylonglegs

how about just changing the second range in the original ?

=LOOKUP(2,1/B5:B35,B4:B34)

I think that this formula also has problem with empty cells, Teacher.

Example: B9= Last number in the range. B8= Empty, B7=Number.

This formula, gives 0

As it turns out, I solved my own question.
Well, actually Rick Rothstein (moderator in Mr. Excel forum) solved my problem with a similar thread back in 2011.

He came up with this formula:

Please Login or Register  to view this content.

Which I revised for my own use as such:

Please Login or Register  to view this content.

Hi Big.Moe,

Glad you found your solution, but for future references.

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

No worries.

The two moderators on this thread are rather infrequent on their visits to the forum lately.