Formula Question... LookUP

G

G

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
 
O

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
 
B

Biff

Hi!
Is there a simple way to calculate the last but one ???
Click to expand...

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
 
G

G

Hi Biff,

Thanks for the reply ..

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

Thanks
 
R

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))
 
Top