Universal Formula to Looking for the last value for last given date.

[thilag]

Hello Friends

Please refer the attached MS Excel 2007 file <last date value.xlsx>.

My requirement is to take the last value to use.i.e., in the attached file in the Column B the last value entered is B43 is 23-Jul-14 and its corresponding value is 428.10 in Cell C43. If the date is entered in the Column B then it should take the value from Column C. i.e. if i enter the date 24-Jul-14 in Cell B44 and the value 427 in C44 then it should display the value of 427 in the formula cell (say / may be E1).


1) Please make the universal formulas which uses B:B & C:C instead B5:B82 & C5:C82.
2) Also the formula should track not only numbers (here number 428.10 in Cell C43), but also able to track texts and combination of text and numbers is great.

thanks in advance

thilag

[Ace_XL]

Since your dates are in ascending order, use this

=INDEX(C:C,MATCH(MAX(B:B),B:B,0))

[thilag]

Hello Ace_XL

thanks for your formula. It work's fine. But if the date is replaced by the text in Column B:B then the formula not works.

thanks again

thilag

[boopathiraja]

Hi, check out the attachment

[Tony Valko]

Try this...

=LOOKUP(2,1/(C5:C100<>0),C5:C100)

1) Please make the universal formulas which uses B:B & C:C instead B5:B82 & C5:C82.

In this application the formula will test EVERY cell referenced so you should avoid using entire columns as range references. Use a range that will always be bigger than you will need (but less than the entire column) or use dynamic ranges.

Goto the Formulas tab>Define name

Name:Value
Refers to: ='P1'!$C$5:INDEX('P1'!$C:$C,MATCH(1E100,'P1'!$B:$B))

OK out

Then the formula becomes:

=LOOKUP(2,1/(Value<>0),Value)

[thilag]

Hello Boopathiraha, Tony Valko, Ace_XL

thanks for all your replies.

thanks

thilag

[Tony Valko]

You're welcome. Thanks for the feedback!