pick up the last entry in a coloum which is not zero

[shyam]

I have details as follows:

row 1 1
row 2 2
row 3 3
row 4 4
row 5 5
row 6 6
row 7 0
row 8 0

=LOOKUP(9.999999999999E+306,B:B)

With the above formula we can pick up the last entry, i.e. 0, but if the last entry in that column happens to be 0, then I need to pick up the previous entry which is not 0. The answer in the above problem would be 6 of row 6.

Would appreciate, if someone would revert back on the above.

Tks n rgds
shyam

 

[Krishnakumar]

Hi,

Try,

=LOOKUP(2,1/(B1:B100<>0),B1:B100)

HTH

 

[Aladin Akyurek]

Define BigNum as reffering to:

=9.99999999999999E+307

Then invoke:

=LOOKUP(BigNum,1/(B2:INDEX(B:B,MATCH(BigNum,B:B))),B2:INDEX(B:B,MATCH(BigNum,B:B)))

 

[shyam]

Dear Mr. Kris

Your formula is working fine... could you please explain,

1. what does 2,1/ in your formula is referring to ?. and
2. in case if I do not give the exact range and want to refer to the whole column, then what would the syntax be.

tks n rgds
shyam

 

[Krishnakumar]

Hi,

Use Aladin's formula.

what does 2,1/ in your formula is referring to ?

See this

HTH

 

[shyam]

Tks kris.

alladin's explanation is very excellent.

rgds
shyam