Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |