Find value in a variable range

[susom]

I am attempting to identify a max value within a range of values that correspond with a particular date. Specifically, I have created a large file that looks at S&P 500 price data going back to 1950 and I am attempting to identify every time the index experienced a [10%] or greater correction. I've identified all the dates when a low was established at some point in the future that was < 90% of the high price on any particular day.

However, this results in multiple dates that can exist for the same low price--i.e. if the correction was > 10% there typically are multiple days where 'Current High Price' < '90% of Range of Future Low Prices' as the market continues to rally to a peak before ultimately correcting.

How might I go about finding the max 'High Price' within the range of values that occur with the same 'Low Price'? Here is a sample from my data that illustrates my ask. Note that the low price of 16.68 was set on 7/17/1950 and this was 10% or lower than the high prices on a number of dates, but the max high was 19.40 that occurred on 6/12/1950.

I have created a separate list of all the unique 'low dates' that I suspect maybe helpful in the solution--there are 28 of them actually.


Date High Last Min Low Date
05/18/1950 18.56 18.56 16.68 07/17/1950
05/19/1950 18.68 18.68 16.68 07/17/1950
05/22/1950 18.60 18.60 16.68 07/17/1950
05/23/1950 18.71 18.71 16.68 07/17/1950
05/24/1950 18.69 18.69 16.68 07/17/1950
05/25/1950 18.69 18.69 16.68 07/17/1950
05/26/1950 18.67 18.67 16.68 07/17/1950
05/29/1950 18.72 18.72 16.68 07/17/1950
05/30/1950 18.72 18.72 16.68 07/17/1950
05/31/1950 18.78 18.78 16.68 07/17/1950
06/01/1950 18.77 18.77 16.68 07/17/1950
06/02/1950 18.79 18.79 16.68 07/17/1950
06/05/1950 18.60 18.60 16.68 07/17/1950
06/06/1950 18.88 18.88 16.68 07/17/1950
06/07/1950 18.93 18.93 16.68 07/17/1950
06/08/1950 19.14 19.14 16.68 07/17/1950
06/09/1950 19.26 19.26 16.68 07/17/1950
06/12/1950 19.40 19.40 16.68 07/17/1950
06/13/1950 19.25 19.25 16.68 07/17/1950
06/14/1950 18.98 18.98 16.68 07/17/1950
06/15/1950 18.93 18.93 16.68 07/17/1950
06/16/1950 18.97 18.97 16.68 07/17/1950
06/19/1950 18.92 18.92 16.68 07/17/1950
06/20/1950 18.83 18.83 16.68 07/17/1950
06/21/1950 19.00 19.00 16.68 07/17/1950
06/22/1950 19.16 19.16 16.68 07/17/1950
06/23/1950 19.14 19.14 16.68 07/17/1950
07/24/1952 25.24 25.24 22.71 09/14/1953

Apologies for not publishing the data above in a xls table, but the add-in is not permitted on my machine.

 

[Sam_D_Ben]

Something like this,

Book1
	A	B	C	D	E	F
1	Date	High	Last	Min	Date	High Price
2	5/18/1950	18.56	18.56	16.68	7/17/1950
3	5/19/1950	18.68	18.68	16.68	7/17/1950
4	5/22/1950	18.60	18.60	16.68	7/17/1950
5	5/23/1950	18.71	18.71	16.68	7/17/1950
6	5/24/1950	18.69	18.69	16.68	7/17/1950
7	5/25/1950	18.69	18.69	16.68	7/17/1950
8	5/26/1950	18.67	18.67	16.68	7/17/1950
9	5/29/1950	18.72	18.72	16.68	7/17/1950
10	5/30/1950	18.72	18.72	16.68	7/17/1950
11	5/31/1950	18.78	18.78	16.68	7/17/1950
12	6/1/1950	18.77	18.77	16.68	7/17/1950
13	6/2/1950	18.79	18.79	16.68	7/17/1950
14	6/5/1950	18.60	18.60	16.68	7/17/1950
15	6/6/1950	18.88	18.88	16.68	7/17/1950
16	6/7/1950	18.93	18.93	16.68	7/17/1950
17	6/8/1950	19.14	19.14	16.68	7/17/1950
18	6/9/1950	19.26	19.26	16.68	7/17/1950
19	6/12/1950	19.40	19.40	16.68	7/17/1950	19.40
20	6/13/1950	19.25	19.25	16.68	7/17/1950
21	6/14/1950	18.98	18.98	16.68	7/17/1950
22	6/15/1950	18.93	18.93	16.68	7/17/1950
23	6/16/1950	18.97	18.97	16.68	7/17/1950
24	6/19/1950	18.92	18.92	16.68	7/17/1950
25	6/20/1950	18.83	18.83	16.68	7/17/1950
26	6/21/1950	19.00	19.00	16.68	7/17/1950
27	6/22/1950	19.16	19.16	16.68	7/17/1950
28	6/23/1950	19.14	19.14	16.68	7/17/1950
29	7/24/1952	25.24	25.24	22.71	9/14/1953	25.24
Sheet3

Cell Formulas
Range	Formula
F2:F29	F2	=IF(B2=MAXIFS(B:B, E:E, E2), B2, "")

 

[susom]

Perfect; THANK YOU!