Value of Second-to-Last Non-empty cell

[ScruffyMonkey]

So what I am trying to do, is find the second to last filled cell in a row. The last value (which I also needed) can be found using this formula that I found:

=LOOKUP(2,1/(D2:Z2<>""),D2:Z2)

But I'm not sure how to use that to find the second to last value. I've done some googling but I can't seem to find any formulas that are independent of the values being a maximum or a minimum. My values will not necessarily be sequential, by that I mean they will not always increase or always decrease.

[Paul]

Hi Scruffy,

The following should return the value from the cell that is two cells to the left of the last value found in D2:Z2...

=INDEX(D2:Z2,MATCH(9.99E+307,D2:Z2)-2)

If you need the value from the cell that is one cell to the left, change the -2 to -1.

[tigeravatar]

Alternate version, works with numbers or text, always finds the penultimate value (unless there are fewer than two values populated)

Please Login or Register  to view this content.

[ScruffyMonkey]

Hi Scruffy,

The following should return the value from the cell that is two cells to the left of the last value found in D2:Z2...

=INDEX(D2:Z2,MATCH(9.99E+307,D2:Z2)-2)

If you need the value from the cell that is one cell to the left, change the -2 to -1.

Thanks! This worked perfectly!

[daddylonglegs]

If that works then your data is presumably numeric and without gaps, if so try also

=LOOKUP(9.99E+307,E2:Z2,D2:Y2)