Got a head scratcher. I have a list of prices and dates. I'm using a
DMAX function to get the highest price for each month, which works just
fine.

But, I want to lookup the result of the DMAX function and return the
date. That way I have a list of months, the high price and the date
that price came from. Problem is, in the whole table there are multiple
prices that are the same, so I get a #NUM error if I use DGET, or
vlookup just returns the first one.

How can I set the lookup table to check the original date criteria and
only lookup in a part of the overall table?


--
Chiba
------------------------------------------------------------------------
Chiba's Profile: http://www.excelforum.com/member.php...o&userid=24998
View this thread: http://www.excelforum.com/showthread...hreadid=385264

I'd use vlookups to find the first and last row of the appropriate month,
then an offset function to construct the range of cells that would be my
table array for a final vlookup.


"Chiba" wrote:


Got a head scratcher. I have a list of prices and dates. I'm using a
DMAX function to get the highest price for each month, which works just
fine.

But, I want to lookup the result of the DMAX function and return the
date. That way I have a list of months, the high price and the date
that price came from. Problem is, in the whole table there are multiple
prices that are the same, so I get a #NUM error if I use DGET, or
vlookup just returns the first one.

How can I set the lookup table to check the original date criteria and
only lookup in a part of the overall table?


--
Chiba
------------------------------------------------------------------------
Chiba's Profile: http://www.excelforum.com/member.php...o&userid=24998
View this thread: http://www.excelforum.com/showthread...hreadid=385264

Hi,

I presume that you want to return all the dates pertaining to that value. I
have tried to draw a parallel example. Hope this helps.

Col A Col B

Ashish 100
Sanjay 200
Pongal 300
Ashish 400
Rajesh 500
Suresh 600
Ashish 700

Now enter "Ashish" (w/o quotes) in cell A10 and array enter
(Ctrl+Shift+Enter) the following in cell B10. Now copy the formula down to 2
rows.

Ashish IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$1 0,ROW
($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMAL L(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

Regards,

Ashish Mathur


"Chiba" wrote:


Got a head scratcher. I have a list of prices and dates. I'm using a
DMAX function to get the highest price for each month, which works just
fine.

But, I want to lookup the result of the DMAX function and return the
date. That way I have a list of months, the high price and the date
that price came from. Problem is, in the whole table there are multiple
prices that are the same, so I get a #NUM error if I use DGET, or
vlookup just returns the first one.

How can I set the lookup table to check the original date criteria and
only lookup in a part of the overall table?


--
Chiba
------------------------------------------------------------------------
Chiba's Profile: http://www.excelforum.com/member.php...o&userid=24998
View this thread: http://www.excelforum.com/showthread...hreadid=385264