THANKS very much! Worked like a charm.
Also - Thanks for catching that and for transposing the answer.
-ML
Dave R. wrote:
Your row/column labels are switched... I'll switch them back, if that
works,
but the idea is the same even if you have to switch them back.
You can use index/match for this like;
=INDEX(A1:A3,MATCH(MAX(B1:B3),B1:B3,0))
where A1:A3 contains the value you want returned (dates) and B1:B3
contain
the dollar amounts. This will find the matching value (the max value)
and
return the corresponding value from A1:A3.
You could also use MATCH($B$4) if you want to use the MAX you've
already
computed.
wrote in message
oups.com...
I am having trouble performing a lookup function. I've tried a few
suggestions I've found by searching the user groups, and cannot
seem to
get one to work for me.
I have an array consisting of dates (column 1) and prices (columns
2
and 3 below, but many columns in my sheet. Below the array are some
basic descriptor statistics, including max and min. I'd like a
lookup
function that tells me, for each column, what date corresponds to
the
max and min, as in cells F2:G3, below.
Any help would be greatly appreciated. Thanks in advance,
Michael
1 2 3
A Jan-04 $4 $2
B Feb-04 $1 $8
C Mar-O4 $5 $3
D Min $1 $2
E Max $5 $8
F mindate 02/04 01/04
G maxdate 03/04 02/04
|