lookup, index, match, offset, etc.

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

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.




<[email protected]> wrote in message
news:[email protected]...
> 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
>

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.
>
>
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

Hi,

Here is one solution:

B1: =TEXT(SUM((C1=C4:C6)*(B4:B6)),"MMM-YY")&", "&TEXT(SUM((D1=D4:D6)*(B4:B6)),"MMM-YY")
B2: =TEXT(SUM((C2=C4:C6)*(B4:B6)),"MMM-YY")&", "&TEXT(SUM((D2=D4:D6)*(B4:B6)),"MMM-YY")
C1: MIN(C4:C10)
C2: MAX(C4:C10)

Regards,
Ola

A...........B..........................C............D
Min:......Feb-04, Jan-04.......1.............2
Max:.....Mar-04, Feb-04.......5.............8

.............Jan-04....................4............2
.............Feb-04....................1............8
.............Mar-04....................5............3