Dynamic Arrays
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 |
All times are GMT +1. The time now is 01:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com