Finding max value in a range based on a certain row

[jungle125]

Hi

With help from this forum, I have identified the low value in a range of 20 rows below my point of interest (identified in column I by the value 1) using an XLOOKUP and MIN function in column J. In the example the point of interest is in row 141, and the identified low point (from column D) is in row 160. However, now I would like to know what the maximum value (in column C) of the 40 rows succeeding the identified low (in other words from row 160). The trouble I´m having is that the row of the low point is dynamic, it can be any of the rows in the defined 20-row range in the initial XLOOKUP formula, so I can´t define the xlookup range by a fixed offset. Keep in mind that these formulas are applied to up to 10,000 lines of datapoints, this is just an example of one identified row of interest. If the formula works, the row that should be returned is row 182, corresponding to the high value of 4.2 (highlighted in yellow, cell C182).

Thanks in advance for any help!

LAC.TO.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2	Date	Open	High	Low	Close	Adj Close	Volume	% Difference between HH and LL last 42 days	Point of Interest	Row of Flag Low	Row of highest within 40 days of flag low	High Point (Column C)
141	03/02/2009	4.55	4.9	4.4	4.5	4.5	70000	791	1	160
142	04/02/2009	4.45	4.5	4.25	4.5	4.5	20780	718
143	05/02/2009	4.5	4.5	4.25	4.35	4.35	19500	718
144	06/02/2009	4.3	4.35	4.05	4.25	4.25	17560	691
145	09/02/2009	4.25	4.25	3.95	4.1	4.1	21160	608
146	10/02/2009	4.3	4.4	4	4.05	4.05	23360	633
147	11/02/2009	4.25	4.25	4	4.05	4.05	6380	507
148	12/02/2009	4.05	4.25	3.85	3.85	3.85	23580	507
149	13/02/2009	4	4	3.8	3.8	3.8	11280	471
150	17/02/2009	3.75	4	3.75	3.75	3.75	23640	471
151	18/02/2009	3.75	4	3.75	3.95	3.95	3220	471
152	19/02/2009	4	4	3.8	3.9	3.9	9820	471
153	20/02/2009	3.9	3.9	3.6	3.85	3.85	10480	457
154	23/02/2009	3.85	4	3.7	3.7	3.7	18480	471
155	24/02/2009	3.7	3.75	3.5	3.75	3.75	5820	436
156	25/02/2009	3.75	3.75	3.6	3.75	3.75	8440	436
157	26/02/2009	3.6	3.7	3.55	3.7	3.7	5880	429
158	27/02/2009	3.55	3.75	3.5	3.5	3.5	17680	317
159	02/03/2009	3.65	3.65	3.3	3.4	3.4	19120	204
160	03/03/2009	3.45	3.45	3.15	3.15	3.15	42460	188
161	04/03/2009	3.35	3.45	3.25	3.4	3.4	5320	188
162	05/03/2009	3.5	3.5	3.15	3.25	3.25	15380	100
163	06/03/2009	3.25	3.25	2.85	2.9	2.9	51100	86
164	09/03/2009	3.15	3.15	2.75	2.75	2.75	42680	50
165	10/03/2009	2.8	2.8	2.65	2.7	2.7	9100	22
166	11/03/2009	2.9	2.95	2.7	2.75	2.75	15200	28
167	12/03/2009	2.95	2.95	2.65	2.65	2.65	13900	26
168	13/03/2009	2.75	3	2.75	2.95	2.95	22600	20
169	16/03/2009	2.95	3	2.85	2.85	2.85	9000	13
170	17/03/2009	2.95	2.95	2.7	2.7	2.7	14160	11
171	18/03/2009	2.9	2.9	2.8	2.9	2.9	18100	9
172	19/03/2009	3	3	2.5	2.5	2.5	94840	13
173	20/03/2009	2.65	2.65	2.5	2.5	2.5	18600	6
174	23/03/2009	2.45	2.45	2.4	2.45	2.45	81300	-2
175	24/03/2009	2.4	2.4	2.35	2.4	2.4	30680	0
176	25/03/2009	2.4	2.65	2.2	2.5	2.5	118360	13
177	26/03/2009	2.65	2.7	2.6	2.7	2.7	13100	23
178	27/03/2009	2.7	2.7	2.7	2.7	2.7	0	23
179	30/03/2009	2.8	2.8	2.65	2.8	2.8	5680	27
180	31/03/2009	2.8	3.05	2.8	3.05	3.05	45020	39
181	01/04/2009	3.15	3.85	3	3.85	3.85	253980	75
182	02/04/2009	3.8	4.2	3.65	3.95	3.95	34760	91
183	03/04/2009	3.95	4	3.75	3.9	3.9	17840	82
184	06/04/2009	3.75	3.75	3.25	3.45	3.45	10640	70
185	07/04/2009	3.35	3.35	3.1	3.2	3.2	12500	52
186	08/04/2009	3.1	3.2	3.1	3.1	3.1	2840	45
187	09/04/2009	3.15	3.2	3	3	3	14920	45
188	13/04/2009	3.05	3.5	3	3.45	3.45	10260	59
189	14/04/2009	3.45	3.45	3.25	3.35	3.35	10680	57
190	15/04/2009	3.5	3.5	3.25	3.25	3.25	31560	59
191	16/04/2009	3.4	3.4	3.2	3.2	3.2	7880	55
192	17/04/2009	3.2	3.45	3.2	3.25	3.25	24660	57
193	20/04/2009	3.25	3.65	3.1	3.25	3.25	8100	66
194	21/04/2009	3.15	3.15	2.95	2.95	2.95	19900	43
195	22/04/2009	3.05	3.05	2.75	2.75	2.75	15300	39
196	23/04/2009	2.85	2.85	2.75	2.8	2.8	16180	30
197	24/04/2009	2.8	2.9	2.7	2.9	2.9	23760	32
198	27/04/2009	2.8	3	2.7	3	3	7860	36
199	28/04/2009	3	3	2.75	2.9	2.9	1080	36
200	29/04/2009	2.75	2.9	2.75	2.9	2.9	20760	32
Sheet1 (2)

Cell Formulas
Range	Formula
H141:H200	H141	=(C141-MIN(D99:D140))/MIN(D99:D140)*100
I141:I200	I141	=IF(AND(H141>=100,H141=MAX(H131:H151)),1,"")
J141:J200	J141	=IF(I141=1,XLOOKUP(MIN(D142:D161),D142:D161,ROW(A142:A161),,,-1),"")

 

[jasonb75]

Do you specifically need to show the row in column K?

I've included a 2 part formula with the row returned in column K, then referenced that in column L to get the final result. I've also added another formula in column M that gets the final result of 4.2 directly from the row of interest without the need for column K as a helper.

Book1
	J	K	L	M
2	Row of Flag Low	Row of highest within 40 days of flag low	High Point (Column C)	Alt High Point formula
141	160	182	4.2	4.2
Sheet2

Cell Formulas
Range	Formula
J141	J141	=IF(I141=1,INDEX(ROW(A142:A161),MATCH(MIN(D142:D161),D142:D161,)),"")
K141	K141	=IF(J141="","",LET(r,INDEX(C:C,J141):INDEX(C:C,J141+40),INDEX(ROW(r),MATCH(MAX(r),r,0))))
L141	L141	=IF(K141="","",INDEX(C:C,K141))
M141	M141	=IF(J141="","",LET(r,INDEX(C:C,J141):INDEX(C:C,J141+40),INDEX(r,MATCH(MAX(r),r,0))))

 

[Eric W]

Another option:

Book1
	J	K	L
2	Row of Flag Low	Row of highest within 40 days of flag low	High Point (Column C)
141	160	182	4.2
Sheet9

Cell Formulas
Range	Formula
J141	J141	=IF(I141=1,XLOOKUP(MIN(D142:D161),D142:D161,ROW(A142:A161),,,-1),"")
K141	K141	=IF(I141="","",LET(r,INDEX(C:C,J141):INDEX(C:C,J141+40),XLOOKUP(L141,r,ROW(r),,0,-1)))
L141	L141	=IF(I141="","",MAX(INDEX(C:C,J141):INDEX(C:C,J141+40)))

 

[jasonb75]

I'm still asking myself why I thought it was necessary to match the max of the range to the range in order to return the same max (in the alt formula in my suggestion)

 

[Eric W]

I'm still asking myself why I thought it was necessary to match the max of the range to the range in order to return the same max (in the alt formula in my suggestion)

Yeah, I wondered that too! But I've done things like that before, you work your way to something that works, and don't realize there's a better way. Just chalk it up to being late on a Saturday night.

 

[jasonb75]

I don't think I even looked at it properly, I remember thinking that removing the ROW() function from the formula in K141 would allow the formula to pull the result directly from the array without needing to get the row number first. I certainly didn't pay any attention to the other steps.

Just chalk it up to being late on a Saturday night.

In all honesty, it was just me not paying attention to what I was doing, but I'll take any excuse that makes it sound a bit better

 

[jungle125]

That seems to have done the trick, you guys are machines!