Lookup address of a number

[tony0682]

Is there a way to get the cell address of where the min and max is?

Book5
	A	B	C	D	E	F	G
1	45.9536	24.53737	93.76101	80.87473		Min	2.790352
2	54.29203	91.62978	40.3526	70.15085		Address
3	13.23746	63.9152	62.59817	55.8505
4	27.95283	69.19199	72.22199	81.1749		Max	98.85546
5	11.85504	75.75607	95.20483	20.72568		Address
6	46.29988	82.90548	32.98858	87.23957
7	87.45566	68.41997	13.36191	76.73568
8	94.29179	36.19207	43.93363	9.164768
9	3.420072	40.75212	76.67443	3.850973
10	50.17276	16.94928	98.85546	47.3043
11	51.80895	39.3869	77.13636	35.26832
12	8.02637	60.33588	98.12426	2.790352
13	77.50228	81.27553	31.33476	98.29505
Sheet1

 

[pgc01]

Edit: Deleted.

 

[Domenic]

Try the following formulas which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

G2:

=CELL("address",INDEX(A1:D13,MIN(IF(A1:D13=G1,ROW(A1:D13)-ROW(A1)+1)),MATCH(G1,INDEX(A1:D13,MIN(IF(A1:D13=G1,ROW(A1:D13)-ROW(A1)+1)),0),0)))

or

=SUBSTITUTE(CELL("address",INDEX(A1:D13,MIN(IF(A1:D13=G1,ROW(A1:D13)-ROW(A1)+1)),MATCH(G1,INDEX(A1:D13,MIN(IF(A1:D13=G1,ROW(A1:D13)-ROW(A1)+1)),0),0))),"$","")

G5:

=CELL("address",INDEX(A1:D13,MIN(IF(A1:D13=G4,ROW(A1:D13)-ROW(A1)+1)),MATCH(G1,INDEX(A1:D13,MIN(IF(A1:D13=G4,ROW(A1:D13)-ROW(A1)+1)),0),0)))

=SUBSTITUTE(CELL("address",INDEX(A1:D13,MIN(IF(A1:D13=G4,ROW(A1:D13)-ROW(A1)+1)),MATCH(G1,INDEX(A1:D13,MIN(IF(A1:D13=G4,ROW(A1:D13)-ROW(A1)+1)),0),0))),"$","")

 

[Domenic]

Note that if the target value is found in both A10 and C5, my formula will return the topmost address, C5, whereas pgc01's formula will incorrectly return A5...

 

[tony0682]

Worked great. Thanks.

 

[pgc01]

Hi tony0682

I hope you have read Domenic's last post. My formula may give a wrong result in case of duplicate numbers. I have erased it.

Thank you Domenic for pointing out the error.