# Find the high after a low

#### matthewlouis

##### Active Member
I have two dates, one is the date of the high price and one is the date of the low price. Only the high price is shown. My goal is to create a formula that will look in the high price column and find the highest high AFTER a the FIRST low price is made in the high price column. When I look at the table visually, the answer is 93.02, the highest value made since 88.20 0n 3/15/1966. Is there a formula that can automatically find this number?

I tried an array formula =MAX(IF(\$H\$5:\$H\$24895>\$H\$2,\$I\$5:\$I\$24895)) but that didn't work.

H = Date column
\$H\$2 = highest price date
\$I\$2 = Lowest price date
Starting in cell I5 (letter I) is the high price column

Visually, I see 93.02 for several rows but the then it starts dropping as the high drops. I need 93.02 to be the answer.

Here's the data:

 From Date​ To Date​ (HIGH PRICE DATE) 2/9/1966 (cell H2)​ (LOW PRICE DATE) 10/7/1966 (Cell I2)​ Column H​ Column I​ (Cell H3) Date​ (Cell I3) High Price​ (cell H5) 2/9/1966​ (cell I5) 94.72​ 2/10/1966​ 94.70​ 2/11/1966​ 94.52​ 2/14/1966​ 94.40​ 2/15/1966​ 94.04​ 2/16/1966​ 93.74​ 2/17/1966​ 93.58​ 2/18/1966​ 93.14​ 2/21/1966​ 92.83​ 2/23/1966​ 92.21​ 2/24/1966​ 91.81​ 2/25/1966​ 91.88​ 2/28/1966​ 91.95​ 3/1/1966​ 91.65​ 3/2/1966​ 90.65​ 3/3/1966​ 90.03​ 3/4/1966​ 90.25​ 3/7/1966​ 89.39​ 3/8/1966​ 89.00​ 3/9/1966​ 89.21​ 3/10/1966​ 90.14​ 3/11/1966​ 89.63​ 3/14/1966​ 88.92​ 3/15/1966​ 88.20​ 3/16/1966​ 88.55​ 3/17/1966​ 88.60​ 3/18/1966​ 89.23​ 3/21/1966​ 89.73​ 3/22/1966​ 90.28​ 3/23/1966​ 89.80​ 3/24/1966​ 89.80​ 3/25/1966​ 90.14​ 3/28/1966​ 90.41​ 3/29/1966​ 90.04​ 3/30/1966​ 89.57​ 3/31/1966​ 89.70​ 4/1/1966​ 90.37​ 4/4/1966​ 91.33​ 4/5/1966​ 92.04​ 4/6/1966​ 92.10​ 4/7/1966​ 92.42​ 4/11/1966​ 92.60​ 4/12/1966​ 92.51​ 4/13/1966​ 92.81​ 4/14/1966​ 92.80​ 4/15/1966​ 92.75​ 4/18/1966​ 92.59​ 4/19/1966​ 92.31​ 4/20/1966​ 92.75​ 4/21/1966​ 93.02​ 4/22/1966​ 92.87​ 4/25/1966​ 92.86​ 4/26/1966​ 92.77​ 4/27/1966​ 92.49​ 4/28/1966​ 91.92​ 4/29/1966​ 91.86​ 5/2/1966​ 91.75​ 5/3/1966​ 91.10​ 5/4/1966​ 90.11​ 5/5/1966​ 89.77​ 5/6/1966​ 88.52​ 5/9/1966​ 87.96​ 5/10/1966​ 87.88​ 5/11/1966​ 88.38​ 5/12/1966​ 87.49​ 5/13/1966​ 86.31​ 5/16/1966​ 86.04​ 5/17/1966​ 85.03​ 5/18/1966​ 85.64​ 5/19/1966​ 86.33​ 5/20/1966​ 85.79​ 5/23/1966​ 86.91​ 5/24/1966​ 87.70​ 5/25/1966​ 87.48​ 5/26/1966​ 87.88​ 5/27/1966​ 87.42​ 5/31/1966​ 87.65​ 6/1/1966​ 86.65​ 6/2/1966​ 86.85​ 6/3/1966​ 86.55​ 6/6/1966​ 86.28​ 6/7/1966​ 85.54​ 6/8/1966​ 85.43​ 6/9/1966​ 85.98​ 6/10/1966​ 86.97​ 6/13/1966​ 87.59​ 6/14/1966​ 87.57​ 6/15/1966​ 87.74​ 6/16/1966​ 87.18​ 6/17/1966​ 87.11​ 6/20/1966​ 87.03​ 6/21/1966​ 87.28​ 6/22/1966​ 87.38​ 6/23/1966​ 87.73​ 6/24/1966​ 87.31​ 6/27/1966​ 87.31​ 6/28/1966​ 86.43​ 6/29/1966​ 85.98​ 6/30/1966​ 85.37​ 7/1/1966​ 86.08​ 7/5/1966​ 86.41​ 7/6/1966​ 87.38​ 7/7/1966​ 88.02​ 7/8/1966​ 88.04​ 7/11/1966​ 88.19​ 7/12/1966​ 87.78​ 7/13/1966​ 87.06​ 7/14/1966​ 87.34​ 7/15/1966​ 87.68​ 7/18/1966​ 87.59​ 7/19/1966​ 87.17​ 7/20/1966​ 86.64​ 7/21/1966​ 86.24​ 7/22/1966​ 86.11​ 7/25/1966​ 85.57​ 7/26/1966​ 84.67​ 7/27/1966​ 84.83​ 7/28/1966​ 84.76​ 7/29/1966​ 84.30​ 8/1/1966​ 83.50​ 8/2/1966​ 83.04​ 8/3/1966​ 83.71​ 8/4/1966​ 84.54​ 8/5/1966​ 84.70​ 8/8/1966​ 84.31​ 8/9/1966​ 84.36​ 8/10/1966​ 83.83​ 8/11/1966​ 83.53​ 8/12/1966​ 83.88​ 8/15/1966​ 83.69​ 8/16/1966​ 82.71​ 8/17/1966​ 81.90​ 8/18/1966​ 81.38​ 8/19/1966​ 80.78​ 8/22/1966​ 79.88​ 8/23/1966​ 79.24​ 8/24/1966​ 79.63​ 8/25/1966​ 79.79​ 8/26/1966​ 77.85​ 8/29/1966​ 76.24​ 8/30/1966​ 76.46​ 8/31/1966​ 78.06​ 9/1/1966​ 78.50​ 9/2/1966​ 78.20​ 9/6/1966​ 78.16​ 9/7/1966​ 77.26​ 9/8/1966​ 76.95​ 9/9/1966​ 76.94​ 9/12/1966​ 78.34​ 9/13/1966​ 79.16​ 9/14/1966​ 79.43​ 9/15/1966​ 80.60​ 9/16/1966​ 80.81​ 9/19/1966​ 80.50​ 9/20/1966​ 79.90​ 9/21/1966​ 79.15​ 9/22/1966​ 78.41​ 9/23/1966​ 78.43​ 9/26/1966​ 78.34​ 9/27/1966​ 79.10​ 9/28/1966​ 78.36​ 9/29/1966​ 77.28​ 9/30/1966​ 77.09​ 10/3/1966​ 76.98​ 10/4/1966​ 75.76​ 10/5/1966​ 76.10​ 10/6/1966​ 75.09​ 10/7/1966​ 74.67​
 ​ ​

 ​

#### matthewlouis

##### Active Member
Wow, this is quite impressive! Man, you even went and got the historical bear markets!! And RECESSIONS.

You've outdone yourself here, Kirk. This is amazing!!

I will keep tinkering and let you know if I see anything askew.

Thanks so much for this!!

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### KRice

##### Well-known Member
For convenience, you may want to build a table of From/To dates that represent certain time periods of interest. I assembled a notional example drawn from public listings of bear markets and recession periods, as well as a few other periods...in Q4:V46. The drop-down selection cell (A5) uses Data > Data Validation from a list...and that list is presently set to Q4:Q45. Feel free to change the contents of those cells Q4:Q45 and add or delete content from that table (and adjust the Data Validation list range) to make it easier/more intuitive to determine which From/To date set to analyze. If you modify the selection table, keep in mind that the "Select Idx" column is what you'll see when the drop-down selector is used in A5, and the Start and End dates will be copied into B5:C5, and the # of data points in column U will also be copied into D5. Those are the only direct connections between the far left and far right sides of the analysis sheet.

In the cases that I've examined, I haven't encountered any yet where the green points on the high/low price curves are "incorrect"...at least to my untrained eye. Post back if/when you have any questions or discover any issues.

#### matthewlouis

##### Active Member
Will do!! Great work here, Kirk, much, much appreciated.

Replies
4
Views
164
Replies
0
Views
74
Replies
13
Views
88
Replies
2
Views
202
Replies
8
Views
160

1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back