Column Lookup last values previous value or values

Did you try:

=INDEX(T:T,MATCH(99^99,T:T,-1)

Try:

Formula:

Please Login or Register  to view this content.

@Ali: think that should be

Formula:

Please Login or Register  to view this content.

That is, 1 rather than -1.

Sorry none of those work.

For clarification, say I have a column of numbers 3,4,6,2,........,4,6,7,8 and I want to return the second to last number in that column, ie 7. What formulas could I use?

Thanks.

There are instructions at the top of the page explaining how to attach your sample workbook.

This works in your sample workbook:

=INDEX(A:A,MATCH(999,A:A)-1)

The penultimate number value is 3.

C3 cell formular

HTML Code: 

D3 cell ,Array formular

HTML Code: 

E3 cell formular

HTML Code: 

F3 cell ,Array formular

HTML Code: 

That’s the last value, not what the OP wants, which is the second to last.

It works in my example but doesn't in my other spreadsheet. The other spreadsheet has over 2000 entries in the column. Non entries contain a zero while some entries actually have a zero as a value. Plus the reference column is on another worksheet. Does this make a difference?

We can only provide solutions for the data provided. Provide a more realistic dataset.

I don’t know what you mean by non entries containing 0 - either they are non entries (null or blank) or they aren’t. Do these cells contain formulae?

If the reference column is on another sheet, then you need to adjust the ranges to point to the other sheet.

Yes, that makes a difference. Excel won’t be able to distinguish between a 0 that’s part of the run and 0s after the run.

The formula that I provide will return the next to last value regardless of whether the column contains all numeric, all alphanumeric/alphabetic, or a mix of both. Did you try that? See post #4.

If you eliminate those extraneous zero values with IFERROR, my formula should work.