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

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
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
 

Some videos you may like

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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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
Joined
Jul 18, 2005
Messages
302
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
 

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
Tks kris.

alladin's explanation is very excellent.

rgds
shyam
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top