MAX Value between 2 Dates in a Range

[investntrade]

Hello,
I have data in three columns as follows:

[TABLE="width: 434"]
<tbody>[TR]
[TD="align: right"]Sr. No.[/TD]
[TD="align: right"]Date[/TD]
[TD="align: right"]Close Price[/TD]
[TD="align: right"]DeadLine[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]17-Aug-12[/TD]
[TD="align: right"]187.1[/TD]
[TD="align: right"]7-Sep-12[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]21-Aug-12[/TD]
[TD="align: right"]193.65[/TD]
[TD="align: right"]7-Sep-12[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]22-Aug-12[/TD]
[TD="align: right"]190.5[/TD]
[TD="align: right"]7-Sep-12[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]23-Aug-12[/TD]
[TD="align: right"]190.4[/TD]
[TD="align: right"]7-Sep-12[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]24-Aug-12[/TD]
[TD="align: right"]191.9[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]27-Aug-12[/TD]
[TD="align: right"]187.5[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]28-Aug-12[/TD]
[TD="align: right"]182.4[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]29-Aug-12[/TD]
[TD="align: right"]172.35[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]30-Aug-12[/TD]
[TD="align: right"]172.55[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]31-Aug-12[/TD]
[TD="align: right"]171.45[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]3-Sep-12[/TD]
[TD="align: right"]169.6[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]4-Sep-12[/TD]
[TD="align: right"]169.95[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]5-Sep-12[/TD]
[TD="align: right"]164.15[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]6-Sep-12[/TD]
[TD="align: right"]163.1[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]7-Sep-12[/TD]
[TD="align: right"]167.05[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]8-Sep-12[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]10-Sep-12[/TD]
[TD="align: right"]169.75[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]11-Sep-12[/TD]
[TD="align: right"]159.3[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]12-Sep-12[/TD]
[TD="align: right"]162.6[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]13-Sep-12[/TD]
[TD="align: right"]160.15[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
</tbody>[/TABLE]

My requirement is as follows: For a given date (column 2), pickup the DeadLine (column 4), and then select the MAXIMUM value of Close Price (column 3) between the given date and the deadline. For e.g., for 17Aug12 (row. No. 1), the Deadline is 07Sep12. So the returned value should be the MAX from Close Price between row no. 1 and row no. 15, which is 193.65.

I've tried a lot with VLOOKUP and MATCH and ADDRESS functions, but none (or their combinations) is working.

Any help will be greatly appreciated.

Thanks

 

[investntrade]

Is it possible to use OFFSET for this?

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=MAX(IF(($B$2:$B$21>=B2)*($B$2:$B$21<=OFFSET(B2,0,2)),$C$2:$C$21))

 

[DHayes]

Hi give this a try.
Set your start date in E1 and end date in F1. Adjust the formula to suit you.

Use SHIFT+CTL+ENTER not just enter

{=MAX(IF((B2:B20 > =$E$1),IF(D2:D20 < =$F$1,C2:C20,"")))}

 

[investntrade]

Hello DHayes,

Thanks a ton!
Your reply works perfectly, appreciate the quick turnaround.

Thanks Tetra201, somehow, I am unable to get your formula working, but thanks again for the inputs!.

Hi give this a try.
Set your start date in E1 and end date in F1. Adjust the formula to suit you.

Use SHIFT+CTL+ENTER not just enter

{=MAX(IF((B2:B20 > =$E$1),IF(D2:D20 < =$F$1,C2:C20,"")))}

 

[investntrade]

Hello Tetra201, Your solution also worked. I had misplaced a cell reference in my earlier try. Sorry about that.
Thanks again