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

#### shyam

##### Active Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Krishnakumar

##### Well-known Member
Hi,

Try,

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

HTH

##### MrExcel MVP
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

##### Active Member
Dear Mr. Kris

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

Tks kris.

rgds
shyam

Replies
5
Views
60
Replies
1
Views
91
Replies
0
Views
80
Replies
4
Views
66
Replies
0
Views
44