Extract Max and relative date

[Muthukrishnan V]

Excel 2007


Column A contains dates in dd-mm-yyyy
Column B contains values in numericals.
Column C1 = Start Date 01-01-2019
Column C2 = End Date. 31-01-2019

A B

16-12-2018 7000
21-01-2019 10305
22-01-2019 9785
24-01-2019 11400 (> Max during C1 to C2)
05-02-2019 18310



I want to extract the maximum value and the relative date
during the period C1 to C2 (01-01-2019 to 31-01-2019)



In the above data, I should get 11400 and 24-01-2019.
Thanks

 

[RasGhul]

Hi,

There's probably an easier way to do this but this seems to work;

Book1
	A	B	C	D
1			01-01-19	Start
2	Dates	Values	31-01-19	End
3	16-12-18	7000
4	21-01-19	10305
5	22-01-19	9785
6	24-01-19	11400
7	05-02-19	18310
8
9	Date	Max During	Criteria Dates	Criteria Values
10	24-01-19	11400	21-01-19	10305
11			22-01-19	9785
12			24-01-19	11400
Sheet1

Cell Formulas
Range	Formula
C2	=EOMONTH(C1,0)
C10	{=IF(ROWS($B$10:B10)>COUNTIFS($A$3:$A$7,">="&$C$1,$A$3:$A$7,"<="&$C$2),"",INDEX($A$3:$A$7,SMALL(IF($A$3:$A$7>$C$1,ROW($A$3:$A$7)-ROW($A$2)),ROWS($B$10:B10))))}
A10	=INDEX($C$10:$C$12,MATCH(B10,$D$10:$D$12,0))
B10	{=MAX(D10:D14)}
D10	{=IF(ROWS($B$10:B10)>COUNTIFS($A$3:$A$7,">="&$C$1,$A$3:$A$7,"<="&$C$2),"",INDEX($B$3:$B$7,SMALL(IF($A$3:$A$7>$C$1,ROW($A$3:$A$7)-ROW($A$2)),ROWS($B$10:B10))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Muthukrishnan V]

Thank you Sir, It works fine.