Excel help needed. How to return the 2nd to last value in a row.

[IamDavid]

I can get the last value easily:
=LOOKUP(2,1/(11:11<>0),11:11)

Problem is I need the second to last to return. I'll actually use the same formula to find the 2nd, 3rd, 4th and so on "last" value in a row.

Any ideas?


Basic sheet. I NEED 88 RETURNED.

A D C D
10
11 90 95 88 99
12
13
14

 

[nickbits]

Probably not the best way but...

Make a new (hidden) column that only has a value if it is 2nd to last (like =if(row+2="", value, "") then do a sum on the whole column.

 

[IamDavid]

That would work but it not feasible for my situation. I'll be applying this to over 20 worksheets, all interconnected and a mess already.

 

[Charles Kozierok]

Is there a ceiling on the data, meaning, a number the data can never go above?

If so, this will work:

=OFFSET(A11,0,MATCH(9999999999,A11:IV11)-2)

(Change the big number and the column range as appropriate.)

 

[postmark]

You could use index instead.

This is assuming this is not going on the same row as the data:

=INDEX(11:11,COUNT(11:11)-1)