nth non blank cell in excel

[Reggieneo]

Dear All,
I have this combo (index match lookup) formula that works in finding the nth non blank cell in column. but when i tried using it to extract the non blank from the bottom in different column placing, the formula in different sheet it doesn't work as expected. it either returns 0 or a distant cell value.

can somebody please help me to understand why?
Excel:
=INDEX(Sheet1!$M$8:M10000,MATCH(LOOKUP(2,1/(Sheet1!$M$8:M10000<>""),Sheet1!$M$8:M10000),Sheet1!$M$8:M10000,0)-2)

this returns : 3562 (as expected) the 3rd value from the bottom

M Column
4,156.00
2,498.00
4,628.00
3,859.00
2,239.00
4,203.00
3,562.00
2,060.00
3,371.00


but this :=INDEX(D8:D34,MATCH(LOOKUP(2,1/(D8:D34<>""),D8:D34),D8:D34,0)-2)
returns 4000 ( wrong result) the 2nd value from the bottom

D Column
4000
2000
4000
4000
4000
4000
4000
4000
4000
6000
4000
6000

 

[Hareesh_KLD]

Hi

data in A1:A9

Find Nth non-blank cell from top (3rd value from the bottom)

=OFFSET(A1,COUNTA(A1:A9)-3,0)

Find Nth non-blank cell from bottom (3rd value from the top)

=OFFSET(A9,-(COUNTA(A1:A9)-3),0)

 

[Reggieneo]

So short yet efficient.

Thnaks