Retrieve the MIN of a list, based between values in another column

[Silverhorne]

Good day!

I am trying to find the MIN of a list of numbers based on the "Start" and "End" placed in another column.
In the attached example, I would like the minimum in column C between where the Start and End is in column E.
The start and end will change cells, the numbers in column C are not in any order and may have duplicates.
Cell B31 gives the value I am looking for.
I thought I could used a cell address function for this but it doesn't work....

Thanks in advance for any help.....

MR Excel Min.xlsx
	A	B	C	D	E
1	19	28-04-23 7:16:54	879	10.90
2	20	28-04-23 7:17:54	3507	10.91
3	21	28-04-23 7:18:54	1034	10.92
4	22	28-04-23 7:19:54	1198	10.94
5	23	28-04-23 7:20:54	1362	10.95	Start
6	24	28-04-23 7:21:54	1539	10.96
7	25	28-04-23 7:22:54	3207	10.97
8	26	28-04-23 7:23:54	1869	10.98
9	27	28-04-23 7:24:54	4267	10.99
10	28	28-04-23 7:25:54	2226	11.00
11	29	28-04-23 7:26:54	4119	11.00
12	30	28-04-23 7:27:54	2546	11.00
13	31	28-04-23 7:28:54	2697	10.97
14	32	28-04-23 7:29:54	4554	10.94
15	33	28-04-23 7:30:54	879	10.91
16	34	28-04-23 7:31:54	3207	10.85
17	35	28-04-23 7:32:54	3361	10.75
18	36	28-04-23 7:33:54	3507	10.68
19	37	28-04-23 7:34:54	3657	10.59
20	38	28-04-23 7:35:54	3810	10.52	END
21	39	28-04-23 7:36:54	3965	10.44
22	40	28-04-23 7:37:54	879	10.38
23	41	28-04-23 7:38:54	4267	10.31
24	42	28-04-23 7:39:54	4414	10.25
25	43	28-04-23 7:40:54	4554	10.19
26	44	28-04-23 7:41:54	4691	10.12
27
28		$C$5
29		$C$20
30		#VALUE!
31		879.00
Sheet1

Cell Formulas
Range	Formula
B28	B28	=ADDRESS(MATCH("Start",Sheet1!$E$1:$E$26,0),3)
B29	B29	=ADDRESS(MATCH("End",Sheet1!$E$1:$E$26,0),3)
B30	B30	=-MIN(ADDRESS(MATCH("Start",Sheet1!$E$1:$E$26,0),3),ADDRESS(MATCH("End",Sheet1!$E$1:$E$26,0),3))
B31	B31	=MIN(C5:C20)

 

[kweaver]

need to rethink.

 

[kweaver]

Not too much head scratching on my part, so I'm sure there's a better way but this seems to do it:

=MIN(INDEX(C1:C100,SEQUENCE(MATCH("End",E1:E100,0)-MATCH("Start",E1:E100,0)+1,,MATCH("Start",E1:E100,0))))

 

[Silverhorne]

I tried it in the example sheet I posted and it works, my actual workbook has 2 sheets and it pulls the values from another sheet.
I thought I could modify it to make it work but it's not giving me the correct number.
Here is what my formula looks like...

=MIN(INDEX(Sheet1!D4:D1500,SEQUENCE(MATCH("Off Strength",Sheet1!F4:F1500,0)-MATCH("On Strength",Sheet1!F4:F1500,0)-MATCH("On Strength",Sheet1!F4:F1500,0)+1,,5)))

I took out the MIN part and it's giving me a list of the first 8 lines of Sheet1!F4:F1500. I am not sure what the last "5" is doing, is that my problem?

 

[kweaver]

Try the edit I made in post #3. You may have looked at it before I change the post.

 

[Silverhorne]

Thank you very much, worked like a charm!

 

[Peter_SSs]

Another way would be

=MIN(XLOOKUP("Start",E1:E26,C1:C26):XLOOKUP("End",E1:E26,C1:C26))

 

[kweaver]

Another way would be

=MIN(XLOOKUP("Start",E1:E26,C1:C26):XLOOKUP("End",E1:E26,C1:C26))

That a pretty clever way to use XLOOKUP.