Need an alternative non-volatile formula

[motilulla]

Hello,</SPAN></SPAN>

For now using in the cell C5 following formula, is there any replacement of this non-volatile formula to regular formula </SPAN></SPAN>

Book1
	A	B	C
1
2
3		12
4
5			12
6			1
7			2
8			3
9			5
10			6
11			10
12			12
13			40
14			60
15			65
16			72
17			38
18			61
Sheet2

Cell Formulas
Range	Formula
C5	=LOOKUP(2,1/(C6:INDIRECT("C"&B3)<>""),C6:INDIRECT("C"&B3))

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Rick Rothstein]

Give this formula a try...

=LOOKUP(2,1/(C6:INDEX(C:C,B3)<>""),C6:INDEX(C:C,B3))

 

[Tetra201]

See if the following formula works for you:

=LOOKUP(2,1/(C6:INDEX(C:C,B3)<>""),C6:INDEX(C:C,B3))

Edit: Rick beat me to it by several seconds

 

[motilulla]

Give this formula a try...

=LOOKUP(2,1/(C6:INDEX(C:C,B3)<>""),C6:INDEX(C:C,B3))

Rick Rothstein, Great help for giving this non-volatile formula-worked fine ,thanks a lot </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[motilulla]

See if the following formula works for you:

=LOOKUP(2,1/(C6:INDEX(C:C,B3)<>""),C6:INDEX(C:C,B3))

Edit: Rick beat me to it by several seconds

Tetra201, Never mind thank you </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[Rick Rothstein]

See if the following formula works for you:

=LOOKUP(2,1/(C6:INDEX(C:C,B3)<>""),C6:INDEX(C:C,B3))

Edit: Rick beat me to it by several seconds

You snooze, you lose.

 

[gerald24]

Just browsing along the threads and want to learn something new. anyone can share what the formula does?

 

[Tetra201]

The formula returns the last non-null-string value (<>"") from the range that starts in cell C6 and ends in column C on the row whose number is entered in cell B3.

 

[gerald24]

The formula returns the last non-null-string value (<>"") from the range that starts in cell C6 and ends in column C on the row whose number is entered in cell B3.

I might find this useful in the future. thank you!