Retrieve second last, third last, or Nth value from a column

Currently I have data in Range (Column) Q36:Q65 which is derived using combination of formulas If Index Max Match etc., and typically it would have zero value towards end of column. I mean, say Q36 to Q46 or so would have formula derived values, but Q47 onwards till Q65 formula would have returned "0" value. (this would vary each time zero value from Q47 to Q65, say from Q47, Q52, Q60 till last Q65)

Currently I retrieve last non zero value, using following formula

=((LOOKUP(2,1/($Q$36:$Q$65<>0),$Q$36:$Q$65)))

But, what i now seek is to derive 3rd or 5th or 7th value from last non zero value. How to change above formula or use alternative formula in different cell to get 3rd last, 5th last, or nth value from bottom (non-zero) value.

Sample looks like this
http://imgur.com/a/0MhJE

Screenshot_1.jpg

Thanks

Based on your sample data, try this for the third last:

=INDEX($Q$36:$Q$65,MATCH(0,$Q$36:$Q$65,0)-4,0)

The number in red needs to be one higher than the match you are looking for. This will not work if there are 0 values earlier in the range, however.

It works because it is indexing the first blank value it finds in the range (using the MATCH function to establish the row). Once it finds that, we take 4 from the row value to find the correct row for your third to last non-blank value. If there were a 0 value in the middle of your range, it would use that as its starting point, and return an incorrect result.

Glad to have helped! Merry Christmas!

Originally Posted by analystbank

Is there any better way to ignore, zero value altogether and just retrieve last nth from last non-zero value?

This solution is insensitive to zeroes among the values:

Formula:

Please Login or Register  to view this content.

It's an array formula, it has to be entered with Ctrl + Shift + Enter.