Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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   Report Post  
Dave R.
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match, Index, Vlookup, Large....Help Please [email protected] Excel Worksheet Functions 3 December 15th 04 02:38 PM
Match, Index, Vlookup, Large....Help Please [email protected] Excel Worksheet Functions 0 December 15th 04 12:16 AM
Match & Index Phyllis B. Excel Worksheet Functions 2 November 27th 04 04:26 PM
Find a match that;s not exact Phyllis Excel Worksheet Functions 0 November 8th 04 09:12 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 07:11 PM


All times are GMT +1. The time now is 01:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"