return cell address of a max

**sandsoppa** · 11-14-2012, 06:01 PM

I need to return the address of a cell (R,C) with max or min in value in 2000 lines of a column. What functions can be used to return the value?
For instance: a minimal value in (C6, R666), it easy to find the value by MIN(), but i do not know which function should be combined to return the address. it should be straight forward, but i am not familiar with the codings.

Thanks a lot,

sandy

**Ace_XL** · 11-14-2012, 06:17 PM

Try..

=CELL("address",INDEX(A1:A1000,MATCH(MAX(A1:A1000),A1:A1000,0)))

Toggle between MIN and MAX as per requirement

**FDibbins** · 11-14-2012, 06:24 PM

you will have to play with the ranges, but try this...
=CELL("address",INDIRECT("C"&MATCH(MIN(C8:C16),C8:C16,0)+7))

swap max for min as needed

**sandsoppa** · 11-14-2012, 06:47 PM

thank you both!

sandy

**sandsoppa** · 11-15-2012, 04:18 PM

I was wondering how to get the actual address by applying the first solution by FDibbins, becaue if the data does not starts from the first row, the cell address refers to the position within the arrey. Today i find the solution by Ace_XL works exactly what i wished. That is awsome!!! Again, thanks both!