Referencing a Cell in Min/Max Formulas

[kivikatz]

Hi all. I am trying to reference a cell to use in a range of cells in MIN/MAX formulas. Not sure if it can be done, or if there is an alternative solution. Below is a sample worksheet to give you a better idea of what I am trying to accomplish. Basically, I am trying to find the MIN within a range by using the MAX within the range as the starting cell.

I tried adding the MAX formula within the MIN formula but it doesn't work as intended, for example, =MIN(CELL("address",MAX($A$1:A29)):A29). In the example, the MAX starting point used in cell C29 formula, would be A11 (0.15), which should return A22 (0.00), and not A1 (-0.01), the pre-MAX MIN.

I am probably overlooking the obvious solution, and would appreciate your help. Thanks.

Book1
	A	B	C	D	E	F	G	H	I	J
1	-0.01	$A$1
2	0.02	$A$2
3	0.05	$A$3
4	0.09	$A$4
5	0.10	$A$5
6	0.11	$A$6
7	0.12	$A$7
8	0.13	$A$8
9	0.14	$A$9
10	0.14	$A$10
11	0.15	$A$11
12	0.14	$A$11
13	0.14	$A$11
14	0.13	$A$11
15	0.12	$A$11
16	0.12	$A$11
17	0.10	$A$11
18	0.08	$A$11
19	0.07	$A$11
20	0.05	$A$11
21	0.02	$A$11
22	0.00	$A$11
23	0.01	$A$11
24	0.03	$A$11			Pre-Max Min
25	0.05	$A$11			Max
26	0.07	$A$11			Min
27	0.08	$A$11
28	0.10	$A$11		Bad Formula:			Bad Formula:
29	0.12	$A$11		MIN(CELL("contents",B29):A29)			MIN(CELL("address",MAX($A$1:A29)):A29)
30
Sheet1

Cell Formulas
Range	Formula
B1:B29	B1	=CELL("address",XLOOKUP(MAX($A$1:A1),$A$1:A1,$A$1:A1,"",0))

 

[Fluff]

How about

=MIN(INDEX(A1:A29,MATCH(MAX(A1:A29),A1:A29,0)):A29)

 

[kivikatz]

Thanks! That works great. Guess I need to learn how to use Index/Match functions.

 

[Fluff]

Glad to help & thanks for the feedback.