How to find a result in an array in the same row as a minimum resu

[dbasmb]

I have temperature data for many years and am trying to find the minimum
temperature for a given day. I can find the min temperature on a given day
with this:
{=MIN(IF(TEXT($A$7:$A$2001,"mmm,d")=X127,$D$7:$D$2001))} where X127 has the
day of the month I'm searching for and the low temperatures are in column
"D". But now I want the day it occurred which is in column "A" of the same
row as the min temp. How do I get that? I thought this would work;
{=ADDRESS(ROWS(D72001=Z127),1)} but that gives me an erroneous address.

Another question: If I have the correct result from the 'ADDRESS()'
function above, how do I access the data in that cell that is referenced as a
text value?

Thanks for your help!

Doug

 

[Max]

Use Index/match - set for an exact match
The Index part of it is the col result that you want returned

You could try something like this, array-entered* (untested)
=INDEX($A$7:$A$2001,MATCH(MIN(IF(TEXT($A$7:$A$2001,"mmm,d")=X127,$D$7:$D$2001)),IF(TEXT($A$7:$A$2001,"mmm,d")=X127,$D$7:$D$2001),0))
*press CTRL+SHIFT+ENTER to confirm the formula
Success? wave it, hit YES below

 

[dbasmb]

Max:

Yeah!! That's the ticket!!! Thanks for your help.

Is there any easier way to answer the last part of my question?? Given an
address in text, such as '$A$2000', how do I use that to get the contents of
$A$2000??

Thanks again,

Doug

 

[Gord Dibben]

In B1 you have the text $A$2000

In C1 enter =INDIRECT(B1)


Gord Dibben MS Excel MVP