+ Reply to Thread
Results 1 to 25 of 25

Dynamic Arrays

  1. #1
    Registered User
    Join Date
    07-07-2005
    Posts
    1

    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?

  2. #2
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  3. #3
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  4. #4
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  5. #5
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  6. #6
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  7. #7
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  8. #8
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  9. #9
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  10. #10
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  11. #11
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  12. #12
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  13. #13
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  14. #14
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  15. #15
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  16. #16
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  17. #17
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  18. #18
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  19. #19
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  20. #20
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  21. #21
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  22. #22
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  23. #23
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


  24. #24
    bpeltzer
    Guest

    RE: Dynamic Arrays

    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
    >
    >


  25. #25
    Ashish Mathur
    Guest

    RE: Dynamic Arrays

    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$10,ROW
    ($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1