Formula Question... LookUP

[Guest]

I am using the follwing formula to calculate the last entry in a range..

=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05 Details'!C416:C428)

Is there a simple way to calculate the last but one ???

Any help greatly appreciated..

Thanks

 

[Oliver Ferns via OfficeKB.com]

G,
rephrase your question. I'm not sure what you mean by this. Are you trying
to calculate its position on the worksheet?

O

 

[Biff]

Hi!

Is there a simple way to calculate the last but one ???

Does that mean if your formula returns the value in C420
you now want a formula that returns the value in C419?

=INDEX(A1:A15,MATCH(LOOKUP(1,1/(1-ISBLANK
(A1:A15)),A1:A15),A1:A15,0)-1)

That will work as long as you don't have duplicate values
in the range. Change the references to suit.

Biff

 

[Guest]

Hi Biff,

Thanks for the reply ..

You are correct in your assumption howvere there will be duplicate antries
in this column ..

Thanks

 

[Ragdyer]

Try this if you might have blanks and duplicates within the range:

=INDEX(A1:A100,MAX(MATCH({"zzzzzzzzzz",9.9999999E+307},A1:A100)-1))